Set Statistics

Any time you’re developing queries in SQL Server, whether they’re single use or intended for inclusion in a stored procedure that’ll be run frequently, you want to know how well your query is running. If it is intended for production, you’ll want to have an efficient query that runs quickly and doesn’t take up more resources than it needs to. If it is a run once, you still want those same qualities, especially since you’ll likely run it a few times to test it. There are plenty of tools with SQL Server that can help you monitor your query’s performance: Activity Monitor, SQL Server Profiler, Estimated and Actual execution plans and more.

I think my favorite though is 2 simple statements that you place before and after your query.
SET STATISTICS IO ON
SET STATISTICS TIME ON

Youre query here

SET STATISTICS TIME OFF
SET STATISTICS IO OFF

SET STATISTICS TIME tells you how long your code takes to run. Sure, the little timer at the bottom of the screen also tells you this, but this is far more accurate. If you click on the Message tab after the query has run, you’ll see how long each statement took to run in milliseconds. Not only that, but it tells you how much CPU time was used and how long the elapsed time was. If you have 2 INSERTS and 3 SELECTS that return results, you’ll get 5 sets of CPU and Elapsed times.

SET STATISTICS IO is more complicated. It details the disk activity for each statement in your code. It tells you the tables referenced, how many index/table scans are performed on each table, how many logical and physical reads there are, how many read-ahead reads are done and more. If you study what each of those stats means, you can get a really good, immediate idea of how well your query is running and what kind of steps you might want to try to get your code running more efficiently.

These are usually my first resort for checking my queries. Write down the numbers most important to you when the results are displayed so you have something to compare later refinements to.

This entry was posted in 2008, SQL Server and tagged , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>