November 1, 2011 at 9:00 am
Hi all,
Here's the situation I'm facing. I have a multi-user win forms application that is connecting to a SQL Server 2008 R2 instance (DB in compat 80). This is a charge entry system, so there are multiple people entering and processing charges through the various validations at any one time. A single person will post those charges to A/R. All is well and good. Due to the size of the application each user can have up to 7-10 connections to the DB, each with MARS enabled, making Activity Monitor show between 16 and 30 connections (7-10 SPIDs) per person. Most of these are used to perform asynchronous operations. What is happening is that occasionally a single SPID (with it's MARS connections as well) from a single person gets into a SUSPENDED state on a SELECT query inside a stored procedure from the unposted charges table. This causes the posting Process to become blocked waiting on the table and effectively freeze EVERYONE out of the system (either litterally freezing the app or figuratively that they can't work).
Questions:
1. How does a simple stored procedure that does a single SELECT (returning no more than 500 rows in about 3000 reads and 100 ms) get into the SUSPENDED State? (The select query is basically a "SELECT * FROM unpostedCharges WHERE [Date] = @Now")
2. How to I diagnosis the root cause?
November 1, 2011 at 9:29 am
venoym (11/1/2011)
1. How does a simple stored procedure that does a single SELECT (returning no more than 500 rows in about 3000 reads and 100 ms) get into the SUSPENDED State? (The select query is basically a "SELECT * FROM unpostedCharges WHERE [Date] = @Now")
It's waiting for a resource of some form.
2. How to I diagnosis the root cause?
Start by investigating what it's waiting for. Sys.dm_exec_requests or sys.dm_os_waiting_tasks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply