Measure TSQL Statement Performance

I/O Usage

Like the other performance measures there are a number of ways to display the amount of I/O used by a TSQL statement.  There are 2 different kinds of I/Os that are tracked within SQL Server: Logical and Physical IOs.  Logical I/O account for data that is processed from the buffer pool which resides in memory, hence the phrase Logical I/O.  Physical I/Os are I/Os that are associated with accessing data directly from the physical disks that SQL Server uses to store databases.   Physical I/O’s are more expensive I/O’s, meaning they take longer to process.  I/O is general the single most expensive operation that impacts the overall performance of a TSQL statement.  So when you are tuning your queries you want to minimize the number of logical and physical I/O’s operation performed to produce a result set.

One method to show the amount of I/O associated with a TSQL statement or batch of statements is to turn on the I/O statistics gathering process by using the “SET STATISTICS IO ON” statement.  When you have issued this statement during your connection SQL Server will output the number of I/O used to resolve your TSQL statement.  Below is an example where I used the “SET STASTISTICS IO ON” statement to display the amount of I/O needed to resolve a simple query against the AdventureWorks database:

Here you can see my SELECT statement performed 2 “logical reads”, and 2 “physical reads”.  Now when I run this batch of statements a second time I get this output:

Here you will note that the second time I issue my TSQL SELECT statement it only required 2 “logical reads and 0 “physical reads”, this because the AdventureWorks pages holding the data for this query are already in the buffer pool.   If you are doing repetitive testing trying to improve the performance of your query you need to make sure you eliminate this I/O counting discrepancy that can arise when pages required by your query are already in the buffer cache.  To eliminate this counting issue, you can issue the “DBCC DROPCLEANBUFFER” command prior to running each test.  This will allow your queries to run with a clean buffer pool without stopping and restarting SQL server.   

Another method to track I/O’s for queries is to use SQL Server Profiler.  To do this just make sure you include the I/O related columns when identify the events and columns you want your trace to monitor.

Conclusion

Being able to track the resource consumptions of your different queries is a critical piece to monitor the performance of your application.   Knowing what TSQL statements are using the most resources helps you determine where to focus your attention when trying to optimize your application.  The “sys.dm_exec_query_stats” DMV helps DBA’s quickly identify those TSQL statements using the most resources.   Using various “SET” statements, system_variables and/or SQL Server Profiler events/columns can help measure CPU, I/O and the elapsed time for your problem TSQL statements. Identifying, measuring and improving performance of your application queries will help you optimize the code behind your application.