Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 Feb 28 '12 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 Feb 28 '12 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 Feb 28 '12 at 14:20
    
Yay! Glad it worked! –  Valkyrie Feb 29 '12 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/… –  Dave Markle Feb 29 '12 at 14:58

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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