Quickly Find Your Worst-Performing T-SQL Statements

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.