Method/Tool For Viewing SQL Statistics I/O When Analyzing SQL Query Performance

Today a coworker pointed to a nice, light weight tool that can be used to evaluate a MSSQL query’s performance and more notably, what pieces are taking the longest.  Yes the execution plan is helpful but for me, reading an execution play for a large query can be daunting, scrolling all over the place and searching for high %’s.  However, using SQL’s built in STATISTICS IO, you can print out a summary of scan counts, logical reads, etc.  To utilize this, just put the following at the top of your query:

SET STATISTICS IO ON;

When the query is complete, the summary (which still isn’t exactly plain English) is on the Messages tab.  My coworker pointed me HERE where Vicky Harp has created a simple Excel document with some formulas to parse out the response statistics and break down what operations are taking the longest.  You can download the spreadsheet from the site that the hyperlink above points to.  Just copy & paste your results into Excel (which will work as designed provided you do not have the comma as a delimiter in your text-to-columns dialog [on the data tab]).

Thank you Vicky Harp!

The spreadsheet puts the information in a format that makes it easy to target your efforts on the poorest performing/longest running pieces of your query.  I know that this puts the information in a format that I can appreciate and I hope that this helps somebody else!

Advertisements

One thought on “Method/Tool For Viewing SQL Statistics I/O When Analyzing SQL Query Performance

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s