I am tackling some performance based issues on my server, the situation is that when the application is running, it runs a loop from a c# application, then calls a stored procedure about 2,000 times, during this period cpu is pegged at 100% all the time.
All the queries are select queries on the database pulling off data to be fed into the application, what I was seeing before was that the stored procedure was denormalising data and puting it into a temp table, then selecting from the temp table out to the
application. I thought at the time that this wasnt effficient, so first task was to remove the use of the temp tables and query tables directly in order to avoid write costs.
I then later noticed that when I call the stored procedure from management studio once i.e 1 thread, performance seems to be 60 - 70% better, but within the application its called with multiple threads and therefore multiple concurrent calls to the database,
i.e sometimes 6 - 10 concurrent calls, then the proc that runs within a second now runs for about a minute. CPU is still pegged at 100%, checked waits, I see a lot of CXPACKET waits and ASYNC_NETWORK_IO waits as well. CXPACKET I dont think i should be worried
too much because the app returns between 3k to 30k rows per call, and parallelism might not be a bad thing with constant reads (please correct if i'm wrong her
View Complete Post