20 07 2014
Curse those cursors
Even the best written query can run away with itself sometimes; especially if you have some form of recursion in the script. Be is a while loop or a cursor; the data does not always fit the expectations of the developer. In that case the query can run and run until you run out of resources and your server starts to stutter.
I have previously mentioned Redgate’s SQL Monitor; one of its many talents is identifying long running queries as they are executing. It provides the SPID allowing you to check on the query, and kill it if you feel the need.
However sometimes a query does not register as long running; often because it spawns other statements (which in turn spawn even more). One trick I have picked up when investigating slowness, or odd-looking “expensive queries” is to check for long running cursors; when the requirements of a cursor are not met it will just run and run until it is terminated.
SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text
FROM sys.dm_exec_cursors (0) c
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t
The above query will return a list of all the current cursors running on your database; I recommend that you run it two or three times in a minute to find which cursor(s) is to blame (if in fact it is a cursor to blame) and which are just doing their jobs. You can then kill it’s SPID and end the execution of the cursor.
It is important to remember that killing the query does not solve the problem, only the symptom. Once you identify the query that is causing the problem ensure that you look at its code and prevent future recursive problems.