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 (www.sqlmag.com, InstantDoc ID 97761)
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.
End of Article
