SQL Server 2008 and SQL Server
2005 include dynamic management
views (DMVs) and
dynamic management functions (DMFs) that provide
performance-related information. However, the most
useful information is obtained when you combine
results from various DMVs and DMFs. For example,
you can combine their results to identify T-SQL
statements that are using an excessive amount of
server resources.
When you want to quickly identify resourceintensive
T-SQL statements, you don’t want to
have to wade through DMV and DMF syntax
documentation or search for information on how to
perform JOIN operations. To avoid the hassles
associated with writing DMV and DMF queries
on the fly, I wrote a parameter-driven stored
procedure named usp_Worst_TSQL. To download
usp_Worst_TSQL, click the 100201.zip hotlink at the top of this page.
The usp_Worst_TSQL stored procedure executes
three DMVs (sys.dm_exec_query_stats, sys.dm_exec_
cached_plans, and sys.dm_exec_sql_text) and one
DMF (sys.dm_exec_plan_attributes), then joins and
manipulates the DMV and DMF results to provide a
variety of statistics. Table 1, shows some of
the available statistics. (The 100201.zip file contains
the entire list in the file StatisticsReturned.xls.) With
these statistics, you can identify the worst-performing
T-SQL statements.
How to Use usp_Worst_TSQL
The usp_Worst_TSQL stored procedure has three
optional parameters—@DBNAME, @COUNT, and
@ORDERBY—that let you restrict and sort the
stored procedure’s output. The @DBNAME parameter
lets you retrieve T-SQL statements for a single
database. If you don’t include this parameter, the
stored procedure returns the statements for all the databases
on the server.
The @COUNT parameter limits the number
of rows (i.e., T-SQL statements) returned by usp_
Worst_TSQL. If you use this parameter, the stored
procedure returns only the top x rows (where x is
equal to the @COUNT value). If you don’t use this
parameter, the stored procedure returns all rows.
The @ORDERBY parameter identifies the statistic
to sort the rows by. Table 1 shows some of the values
you can use with this parameter. No matter which
statistic you choose, the rows are sorted in descending
order. If you don’t include the @ORDERBY parameter,
the stored procedure sorts by average I/O usage.
With the @DBNAME, @COUNT, and
@ORDERBY parameters, you can quickly generate
customized worst-performance reports. For example,
the call
EXEC Examples.dbo.usp_Worst_TSQL
@DBNAME='AdventureWorks', @COUNT=6,
@ORDERBY='ACPU';
returns the top six T-SQL statements that have the
highest average CPU usage in the AdventureWorks
database. The call
EXEC Examples.dbo.usp_Worst_TSQL
@DBNAME='AdventureWorks', @COUNT=5;
returns the top five T-SQL statements that have the
highest average I/O usage values in the Adventure-
Works database. Figure 3 shows an excerpt
of sample output from this query. (Only the first seven
columns are displayed, and the last T-SQL statement
is truncated.) As you can see, the T-SQL statement in
the first row is by far the worst-performing in terms
of average I/O.
In Figure 3, notice that some of the database
names have an asterisk (*) after them. An asterisk
indicates that the dbid column in the sys.db_exec_
plan_attributes DMV was used to identify the database
name for that statement. When this occurs, the
database name is the database context that was used
when the statement was executed, so it might not reflect the actual database name if three-part naming
conventions are used.
If you run usp_Worst_TSQL with no parameters,
such as
EXEC Examples.dbo.usp_Worst_TSQL
the stored procedure returns all the T-SQL statements
(sorted by average I/O usage) ran against all the databases
on the server. These results can help identify the
databases against which the most resource-intensive
statements are being run.
I use usp_Worst_TSQL frequently and have encountered
quirky results only one time. When I ran
the stored procedure against a SQL Server 2005
instance running on my Windows XP laptop, it returned
negative numbers for some of the columns. I
believe this occurred because the sys.dm_exec_query_
stats DMV’s total_worker_time and total_elapsed_
time statistics contained negative numbers, which is
related to SQL Server not being able to handle different CPU frequencies, as outlined in the Microsoft article
“SQL Server timing values may be incorrect when
you use utilities or technologies that change CPU frequencies”
(support.microsoft.com/kb/931279). This
problem didn’t occur when I ran usp_Worst_TSQL
against a SQL Server 2008 instance on the same laptop
or against any of my SQL Server 2005 instances
running on Windows Server 2003 servers.
A Handy Report
The usp_Worst_TSQL stored procedure lets you
quickly produce customized reports showing the
poorest-performing T-SQL statements—all without
having to remember or search through documentation
on how to perform DMV, DMF, and JOIN operations.
You can then share this information with the
people who wrote the T-SQL statements (e.g., DBAs,
programmers) so that they can fine-tune their T-SQL
statements.
End of Article
