CODEWITHSUNDEEP
sql-server-2005stored-proceduresconcurrency
Dr. J
Dr. J

Reputation: 56

SQL Server 2005 Stored Procedures running concurrently causes delays

I have a stored procedure in my system that runs long already which is somewhat expected since it deals with lots of data and an export process. For arguments sake the procedure will run about 10 seconds by itself. There are times when I see this procedure called back to back with the same parameters.

Call 1 - Start 12:00:10; Duration 30 seconds

Call 2 - Start 12:00:15; Duration 10 seconds

It appears that the first call is waiting for the second call to complete before it can finish. I have blocked process report running and do not get any hits there from profiler; Also when I check sys.dm_exec_requests I do not see the SPID's being blocked by one another and the longest wait_type is async_network_io.

The stored procedure uses both #temp and @temp tables. I'm stuck what else should I check or change to get this under control?

Upvotes: 0

Views: 261

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300559

"the longest wait_type is async_network_io" - that is often a symptom of transferring too much data, e.g. SELECT * on large table(s) or wide table(s).

Update in response to your comment:

"yes this procedure has a dual function of sorts. 1) returns 20 rows from page 1. 2) There is internal logic that turns this into a data dump of sorts; where all of the client data is returned.

It sounds like you are being affected by parameter sniffing and an inappropriate cached query plan. Split your procedure into 2 separate stored procedures. That way each can have their own cached query plan.

Also note: table variables don't get statistics created for them, so sometimes you can see significant performance improvements by converting to use a temp table.

Upvotes: 1

Related Questions