One fine morning, users started complaining my SQL server instance performance…They sent me a screenshot which indirectly says, their queries have timed out. I quickly logged in and checked if there are any blocks.
WHERE blocked <> 0
There were several blocks caused by a naughty spid 80.
When I checked DBCC INPUTBUFFER(80), To my surprise, I got below scary result.
EventType | Parameters | EventInfo
Language Event | 0 | FETCH API_CURSOR0000000000005BE4
I started googling and came across a blog http://www.sqlskills.com/blogs/joe/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch/
Below query in that forum helped me identify the process and query causing the blocks. It was a select query.
FROM sys.Dm_exec_cursors (80) c
CROSS apply sys.Dm_exec_sql_text (c.sql_handle) t
The value 80 was my blocking process spid here. You will have to replace that with your blocking spid. Share the output with the application developers to help them prevent this from happening in future.
session_id – properties – creation_time – is_open text
80 – API | Dynamic | Scroll Locks | Global (0) – 2013-06-18 20:34:19.357 – 1 – (@1 int)SELECT * FROM [xxxxxxx] WHERE [aid]=@1