If your boss asked you for a list of the worst performing statements in your system, what would you do? Do you keep such a list handy? If you don’t, you should. But how do you keep such a list current? The first thing most DBAs do is open SQL Server Profiler or create a trace. Although SQL Server Profiler is a fantastic tool that can give you all sorts of helpful information about currently running statements, it has some drawbacks: It can have a severely negative effect on a busy system, and it can flash thousands of lines of statements at you, making it nearly impossible to derive anything meaningful for your purposes. Creating a server-side trace has less overhead, but that process has its own set of concerns when it comes to analyzing so much data.
What’s a great alternative for an overworked DBA? With the sys.dm_exec_query_stats dynamic management view (DMV), SQL Server 2005 gives you a clever way to obtain most of the information you would need.
Query Stats to the Rescue
Each time a batch or remote procedure call (RPC) is executed, SQL Server will generate a query plan and attempt to store the plan in the plan cache. Once the plan is in cache, SQL Server will attempt to reuse that plan the next time the batch or RPC is executed and will keep track of the statistics for each statement within that plan. It will even aggregate the values for these statements as they’re run over and over again— assuming the original defined plan stays the same.
The DMV’s captured statistics are fairly self-explanatory (and documented in SQL Server Books Online), so I won’t go into too much detail about each column. Essentially, the statistics are divided into seven categories. The first is execution information that tells how many times the query plan was executed, when it was created, and the most recent time it was executed. The other six categories, which Table 1 shows, all contain the following statistics: Totals, Last, Min, and Max.
Data Ripe for the Picking
To obtain the data and to correlate it with the statements, you can use a query such as the one that Web Listing 1 shows. Because the sys.dm_exec_query_stats DMV doesn’t contain the actual text of the statements, you need to invoke the use of another DMV—sys. dm_exec_sql_text—for this purpose. To do so, you can cross apply the two DMVs and pass the handle of the appropriate SQL Server statement so that it can return the actual text.
It’s important to point out the difference between the first two returned columns. The first is the text of the entire batch or RPC that the plan encompasses. The second is the actual statement within that plan for which the statistics apply.
As you can see, a relatively simple query can yield a great deal of information. You can pick and choose the columns that make sense for your goals. As an example, I’ve included several ways to order the query depending on what you might be looking for. (See the commentedout ORDER BY clauses in Web Listing 1.)
Heed These Warnings
Keep a few important characteristics of this DMV in mind. The data is memory resident and starts completely from scratch with each restart. If the plan associated with these statements gets flushed from cache, the stats will get flushed, too. If the plan is recompiled, the stats start all over again. Queries that never get cached won’t show up in the DMV. In a nutshell, the data is somewhat transitory in nature. However, after the server has been running for a while, there’s usually ample data to give you a good look at how well your statements are performing.
