Monitoring Cursors, what are some good queries/scripts to do this?

Question

I need to provide management with proof that a group of existing stored procedures that use cursors are the cause of much of our performance issues. Can someone point me in the right direction to find scripts and queries to accomplish this, please? Such as, how to monitor and measure cursors, etc. Using SQL Server 2005.

Thanks.

========UPDATE============

Management needs ammunition to take back to 3rd party vendor to tell them to change their procs at little or no cost to us. Since these are 3rd party procs hitting our accounting system, I don't have any way of rewriting them first.

Besides traces (already doing), are there any other things I can do? I've found that using sys.dm_exec_cursors(0) lets me get a quick list of exisitng cursors. Are there any other things like this?

Answer 1

The best thing (time permitting) would be to rewrite some of the procs as set-based statements and then compare the two with waits analysis (http://technet.microsoft.com/en-us/library/cc966413.aspx has a good paper about how to do this type of thing). Without a before-and-after, your adversaries might just say "set-based won't be any better :-)"

Answer 2

You can run SQL Profiler and capture a trace with the offending sprocs (this will give you important measures like Reads, CPU, Duration).

A good idea would be to e.g. take one of them as an example that's quite easy to rewrite as a set-based approach, run it and capture the profiler trace for that. This way, you can show realworld differences in performance.

If possible, (i.e. not on production), you should clear down the execution plan and data cache before running each version of the sproc to allow a fair comparison.

Also, you could get the execution plans for the cursor version, and the set-based version.

At the end of the day, bottom-line stats speak for themselves so having a comparison "before" and "after" will be beneficial.

Answer 3

Performance monitor (perfmon.exe) is an excellent tool for real time analysis of SQL Server performance.

Answer 4

So you did hard measurements and collected execution times and statistics showing that the problem procedures are the ones using the cursors, right? Then the collected information is an excelent argument to prove your case. If you did not... then how do you know is the cursors?

Start by looking at sys.dm_exec_query_stats and collect the most expensive querries by worker time (CPU), elapsed time (duration) and by I/O. These should be enough to point to the culprit and find out if indeed, the problem is because of the cursors or not.

If the cursors turn out to be indeed an issue, there are dedicated DMVs for them too, sys.dm_exec_cursors

For example, the top most expensive CPU frequently executed statements:

select top(10) substring(Text,
  statement_start_offset/2, 
  (statement_end_offset-statement_start_offset)/2) as Statement
  , *
from sys.dm_exec_query_stats q
cross apply sys.dm_exec_sql_text(sql_handle)
where execution_count > 100
order by total_worker_time/execution_count desc