Development query statistics

So you want to improve the performance of a query, and need stats. Add this to the top of your query (remove it before deploying to production!)

SET STATISTICS IO ON
SET STATISTICS TIME ON

When you run your query, it’ll output some useful stats such as; execution time, query compilation time, number of scans on each table, number of reads, etc.

Example output:

SQL Server parse and compile time: 
   CPU time = 78 ms, elapsed time = 78 ms.

 SQL Server Execution Times:
   CPU time = 3 ms,  elapsed time = 3 ms.

Table 'tFoo'. Scan count 9, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'tBar'. Scan count 8, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

As a side note on this; I had a query that was taking 5+ seconds to execute, but the server-side stats were only showing it taking ~50ms.

Our DBA showed me that these are server-side stats, so they do not include additional timings for things like network latency or client processing. You can get separate client side stats by enabling Query > Include Client Statistics in SSMS. Pesky VPN.

Popular posts from this blog

Taking a memory dump of a w3wp process

sp_blitzIndex

GitLab Badges