SqlStopwatch - Tick Tock

Beyond Management Studio

I've been spending a lot of time recently tuning SQL Server queries in my application development work.

For me, that means crafting SQL statements by hand in SQL Management Studio and reviewing IO and time stats to measure my tuning progress. Once the query gets to a reasonably performant point, then I'll fold it into my application code and move on to the next task. This simple process works for me in most cases; however, sometimes it helps to have access to key SQL perf information programmatically.

A Simple Utility

Enter SqlStopwatch. This utility is modeled after the .NET Stopwach class and is used to reveal how hard the SQL Server engine is working to service individual requests.

The constructor for SqlStopwatch expects an open SqlConnection instance. In between the subsequent calls to Start and Stop/Elapsed, the stopwatch will calculate elapsed time and total logial reads for all work performed on that SQL session.

If you're wanting to measure the performance of individual SQL calls then you should call Elapsed after each call to gather up the impact of each step. Alternately, you can Clear or call Start again on the SqlStopwatch to refresh the 'before' snapshot.

Here's a simple example using the Adventureworks database:

var sw = new SqlStopwatch(con);

// Start will capture a 'before' snapshot of the connection's
// session information. This is needed since some of the 
// counters in the DMV tables accumulate over the life
// of the session
sw.Start(); 

var emps = con.Query("SELECT * FROM HumanResources.Employee")  
              .ToList();

// Calling Elapsed will calculate teh elapsed time and 
// number of logical reads since the last time that Start 
// was called. To reset the timer, call Start again or Clear
var elapsed = sw.Elapsed;

// Output example: 
// [SPID 53] Elapsed Time: 28 ms, Logical Reads: 9
Console.WriteLine(elapsed);  

The SqlStopwatch class is available on GitHub here.

If you find this helpful, or have change recommendations, please let me know.

Happy coding!