Using Reads, CPU, Duration or Writes for query tuning with SQL Trace
Whilst high level tools such as Windows Perfmon can give you an overview of server performance and Waitstats can provide you with an insight into where bottlenecks are occuring within the database engine, seeking out the source of most SQL Server performance problems really requires use of SQL Server Trace / Profiler to identify which specific queries are running inefficiently, either due to insufficient indexing structures, statistics, design or coding issues.
In other words, the higher level measurements provide insight into symptoms, whilst SQL Trace / SQL Profiler can point out the causes of poor SQL Server performance.
SQL Trace can be accessed either via its "SQL Profiler" GUI or by configuring server-side traces via the SQL Trace API. Whichever method you chose, you need to make some decisions about which events & event columns you wish to receive in your output and also what filter criteria you wish to apply (if any) to quickly zero in on the problem queries which might be affecting your server.
Usually, the two events you'll most often want to collect when embarking on a new performance tuning adventure are (Stored Procedures)RPC:Completed and (TSQL)SQL:Batch Completed. These two events combined should identify all external calls from client apps - the first records Remote Procedure Calls (eg, ADO Stored Procedure commands) and the second records all SQL batches submitted (eg ADO text batches).
Once you've chosen which SQL Trace events you wish to collect, you also need to decide on the filters you will apply to your tracing activity. Ideally the filter will show up the statements which are running least efficiently so you can quickly identify where you should focus your tuning efforts, but what is the best measurement to do this filtering?
The RPC:Completed & SQL:Batch Completed events both expose four important data columns (attributes) which are particularly useful in isolating queries which are running inefficiently - Reads, CPU, Duration & Writes. I've listed them in the order in the order I they're useful for query tuning & from here on in, I'll discuss their respective merits.
Firstly, Reads represent the number of 8kb "page reads" performed by SQL Server to execute any given query. SQL Server stores table rows on 8kb pages on disk & these pages are read from disk or memory buffers (if they're already cached in memory) during query execution. The Reads value reported for each SQL Trace event includes the number of these reads performed by each query (whether from disk or memory) & is therefore a key indicator of general query performance. The more Reads performed by any given query, the less efficiently it is performing. Common causes of inefficiencies in this area are lack of suitable indexes on tables, leading to table scans when SQL Server executes its queries. Usually these problems can be resolved by "tuning" the query by adding better indexes to the database (Database Tuning Advisor is useful in this area for less experienced DBAs).
A couple of problems with filtering by the Reads column though - firstly, it includes non data page reads such as Procedure Cache page reads. Don't forget that SQL Server tries to avoid compiling every query it runs by "remembering" query plans by caching them in its Procedure Cache. Later, when the same query runs again, SQL Server first looks up its Procedure Cache to try & find a query plan rather than recompiling it each time it is run. The reads performed when looking up the Procedure Cache (which also uses 8kb memory buffers) are also included in the figure reported by SQL Trace's Reads output. Another problem is that the figure reported in the Reads output provides only a whole query total, giving no table by table breakdown. To obtain a more granular breakdown (at a table by table level), SET STATISTICS IO ON can be used at the connection level when re-running a query. So, whilst Reads does provide a generally useful measurement of query workload, it isn't perfect & can sometimes be mis-leading if the Procedure Cache is abnormally large or transient.
CPU is the next measurement offered by SQL Trace & is useful in that it reports the amount of time that the query spends running on the CPU whilst SQL Server is executing the query. This is a simple & effective measure, but you have to take into account the fact that queries often spend significant amounts of time "waiting" on queues for resources (eg locks) within SQL Server during their execution, during which they are "pre-empted" off the CPU by SQL Server's User Mode Scheduler / SQL Operating System (SQL 2005). The SQL Trace CPU measurement at least gives you a simple indication of how much CPU time the query consumed during its execution but it's also not unusual for a querry's CPU time to be significantly lower than it's Duration (discussed next), especially if it spends significant amounts of time in "wait" or blocked modes.
Duration is another time based measurement offered by SQL Trace & is useful in that it reports the entire query's "round-trip" execution time. This includes the total time from initial invocation, compilation & execution and also the that time taken by the application to consume any resultset (entirely). The obvious problem with this measurement is that it is subject to significant external factors such as network latency, application performance etc. It is also subject to the same blocking / waiting problems associated with the CPU measurement. Whilst it's useful to have this information - as application delays, network delays & blocking / resource waits are also an important part of overall tuning - these aren't particularly useful for QUERY tuning as they only describe the symptoms, not the cause of the problems.
Another minor problem with Duration is that MS tinkered with it's measurement scale in SQL 2005 - Duration is now reported in microseconds rather than milliseconds (as was the case with SQL 2000). To confuse things further, the SQL Server 2005 Profiler performs a transformation & displays Duration in milliseconds so you have to be aware to read Duration values differently from server-side traces than from the SQL Profiler GUI. I really don't understand why MS made this change to report Duration at such a lower granularity, especially when it is generally a much larger number than CPU (because Duration includes the full round trip with the client app). I would have understood this change if it were made to the CPU measurement though, as CPU is often a much smaller number than Duration, yet CPU was left to report in milliseconds! Hmm...
Writes is the last of the major query performance measurements offered by SQL Trace. Whilst it provides some useful information about how many 8kb write a query performed, I rarely find this terribly useful when query tuning as there is little scope for much to go "wrong" with write characteristics. This is a very different picture to Reads because many factors can (& often do) cause query read behaviour to degrade (indexes, statistics etc).
So, in summary, I recommend that you use Reads as your best general purpose filter when working with SQL Trace / SQL Profiler as it represents the best measurement of overall query workload. Generally speaking, a query that is running inefficiently will nearly always reveal itself by performing more reads than necessary. Whilst this isn't universally true (nothing ever is) it's a good starting point for general purpose tracing to find query tuning targets. I think Reads is a better general purpose measurement than CPU or Duration because time based measurements are too easily affected by other significant external factors which might / might not actually be considered problems depending on the circumstances. Excessive Reads, on the other hand, always indicates that a query is doing more work than it should.
How many is too many & how do I use Reads information? That, hopefully will be the subject of another blog post soon