1

We have a stored procedure that our application uses to query data (no write operations are performed). I have the complete staging database copied to my local version of Sql Server developer edition. The stored procedure locally runs perfectly fine and gives me results in less than a second. When I run it on our staging system the stored procedure gets suspended and sits there.

I cannot figure out why this is getting deadlocked and I feel stuck.

When I run exec sp_lock I see a LOT of entries for a specific spid with all different object ids, and all the locks are of type TAB with different object ids (probably makes sense as the query returns about 30 fields, at least half of which are subqueries).

When I run exec sp_who2 I see that SPID keeps going between SUSPENDED and RUNNABLE states, with ever increasing CPUTime and DiskIO values.

I'm not sure where to go from here. I can almost guarantee that the tables the stored procedure is using is not being used by anything else, as this system isn't live. Also, other stored procedures querying data over the same tables run flawlessly. How can I identify the source of the deadlock?

5
  • Have you checked to see if any stats on the tables in question are out of date? When I've seen issues like this, it's usually a highly-fragmented index (or missing index) or stats that are woefully out of date; as a result, the server spends way too much time sorting through data and not nearly enough actually running the query.
    – Valkyrie
    Commented Feb 28, 2012 at 14:11
  • How do I do that (I haven't done much admin stuff with databases before)? That does sound like a likely possibility (just moved the database after it erroring out from running out of space on c:)
    – KallDrexx
    Commented Feb 28, 2012 at 14:15
  • Actually I figured it out and that worked! If you write that as an answer I'll mark it as the correct one! Thank you!!
    – KallDrexx
    Commented Feb 28, 2012 at 14:20
  • Yay! Glad it worked!
    – Valkyrie
    Commented Feb 29, 2012 at 14:30
  • FYI, this is not a deadlock condition; it's simply a very long blocking condition. Deadlocks are different, and they end up being resolved very differently. See simple-talk.com/sql/learn-sql-server/… Commented Feb 29, 2012 at 14:58

2 Answers 2

1

Have you checked to see if any stats on the tables in question are out of date? When I've seen issues like this, it's usually a highly-fragmented index (or missing index) or stats that are woefully out of date; as a result, the server spends way too much time sorting through data and not nearly enough actually running the query.

3

I think "deadlocked" is the wrong term in this case. Your query was getting blocked. Deadlocked means that it is blocked by another process that is in turn blocked, waiting for your original process to complete. Since these two processes will never complete, one of them is chosen to the victim of a deadlock, and it is terminated, allowing the other one to continue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.