SQL server/ ADO.NET performance
-
I have an application (C#/ADO.NET) that collects alarms from equipment at around 100 sites, through concurrent TCP/IP listeners. Per day I receive about 1 million messages (~10/sec). I used to insert the directly into the destination table, but that would lock up the table so user access (read/reporting) was hardly possible. Now I insert the data in a intermediate table and upload it every 5 min. to the final table with SQl agent. First of all, this doesn't look like an elegant solution. Furthermore, performance is still quite bad. Even though I have just this one DB/table, SQL-server uses up all available memory (1.5Gb out of 2Gb) and all available CPU, 24x7; I'm just wainting for a melt-down... Anybody have any experience with this amount of transactions, and/or any suggestions? Thanks.
-
I have an application (C#/ADO.NET) that collects alarms from equipment at around 100 sites, through concurrent TCP/IP listeners. Per day I receive about 1 million messages (~10/sec). I used to insert the directly into the destination table, but that would lock up the table so user access (read/reporting) was hardly possible. Now I insert the data in a intermediate table and upload it every 5 min. to the final table with SQl agent. First of all, this doesn't look like an elegant solution. Furthermore, performance is still quite bad. Even though I have just this one DB/table, SQL-server uses up all available memory (1.5Gb out of 2Gb) and all available CPU, 24x7; I'm just wainting for a melt-down... Anybody have any experience with this amount of transactions, and/or any suggestions? Thanks.
CPU should not be high on the SQL box for data inserts. Check that your inserts are as efficient as possible: * avoid INSERT ... WHERE ... statements * avoid clustered index (good for selecting, often bad for inserting) * minimize the number of indexes on the tables you insert to Regarding memory usage, SQL tends to do that (grab as much memory as it can), it does not necessarily mean anything. However, it is worthwhile checking that you have appropriate indexes for the user-reporting and other reads are optimized.