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
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?