SQL Server Management Studio Standard Reports - Activity - All Cursors and Activity - Top Cursors

I'm continuing my series on the Standard Reports in SQL Server Management Studio, and today I'm covering two of them: Activity - All Cursors and Activity - Top Cursors. These are part of the activity series of reports that provide more detail than the "Activity Monitor" panel.

One thing that I haven't mentioned yet is that you can export your reports, either to Acrobat (PDF) or Excel Spreadsheet format. Just right-click anywhere in your report and select "Export" from the menu. I use the Excel versions more often than PDF, because I usually want to do something with the reports once I create them.

In these two reports we're talking about Cursors, which is a looping construct used in the SQL language. Many people see cursors as especially bad, but I think they have their place, if used correctly. The problem is that they aren't always used correctly, so one of things both DBA's and developers spend time on is finding long-running cursors, hence these two reports. Although the columns this report generates are pretty self-explanatory, these reports are very useful - funny how that works out. Simple is good.

Activity - All Cursors

This report shows the activity of all active cursors. You'll get information on which cursors are running, and a list of the CPU time and IO reads and writes. You can use these to track down your longest cursors as the system runs, and then re-factor them to be more efficient. This is another "stacked" report, with the Session ID and user name as the outer “band” in the table which contains the two others. This lets you view the cursors by transaction, by user. You also get part (but not all) of the SQL Statement in the cursor. More importantly, you get the name of each cursor the ID/user is running. Here are the columns:

Session ID The session ID is the number that uniquely identifies the transaction the cursor is running in. You’ll see this number used in a lot of tracking stored procedures (like sp_who) and other reports, so it’s easy to coordinate the information with those outputs.

User Name	The name of the login that is running the cursor.
Name	Each cursor is created using the DECLARE statement followed by a name. This is the name that shows here.
Properties	The values in the Properties field describe the cursor from a system perspective. The values are concatenated with lines between them, and the values themselves represent the following: Declaration Interface | Cursor Type | Cursor Concurrency | Cursor scope | Cursor nesting level | Creation Time For the actual values themselves, check about midway down on this page.
Status	I’ve been asked whether this column showed the FETCH STATUS of the cursor, but alas, it’s only whether the cursor is currently open or not.
CPU Time (ms)	Time spent, in microseconds, by the workers executing this cursor.
# Reads	The number of logical IO reads the cursor has taken so far.
# Writes	The number of logical IO writes the cursor has taken so far.
Dormant Duration (ms)	Session has been reset because of connection pooling and is now in pre-login state.
SQL Statement	Shows the first few lines of the cursor’s SQL statement.

Activity - Top Cursors

This report has the same columns and information as the "All Cursors" report, with the exception that the Session ID's and user information are on the same line, with the other information below that.

The only issue is that if you have only one cursor, it fits all these categories!

You get the following reports:

Top 10 Oldest Cursors - The oldest cursor the system has recently run.

Top 10 Dormant Cursors - The cursors that are currently not responding or doing any work.

Top 10 IO Intensive Cursors - The cursors that are taking the most IO (storage) resources.

Top 10 CPU Intensive Cursors - The cursors that are taking the most CPU resources.