Poorly performing TSQL code is never good. If you manage an SQL Server database, or are a DBA that manages lots of databases, you should be on the lookout for inefficient TSQL code. One way to find TSQL code that performs at a subpar level is to look at the statistics stored in the procedure cache.
When a TSQL statement runs, it gets compiled and, in most cases, the plan is stored in the procedure cache. In addition, when the plan is executed, the database engine also gathers processing statistics related to the execution and stores those stats in the procedure cache as well. By reviewing the stored stats within the execution plan, you can see how many resources are used to run the TSQL code.
The code below displays a few of these processing statistics for the commands stored in procedure cache:
-- Display top 10 based on execution count
SELECT TOP 10 QP.query_plan AS [Query Plan],
ST.text AS [Query Text],
QS.creation_time AS [Creation Time],
QS.last_execution_time AS [Last Execution Time],
QS.execution_count AS [Execution Count],
QS.total_worker_time AS [Total CPU Time],
QS.total_logical_reads AS [Logical IO Count]
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_query_plan(QS.plan_handle) AS QP
CROSS APPLY sys.dm_exec_sql_text(QS.plan_handle) ST
ORDER BY [Logical IO Count] DESC;
This code orders those stats by the most Logical I/O used by commands. By reviewing the stats displayed by this TSQL code on your instance, you should be able to determine the commands that perform worse than others, based on I/O. By changing the ORDER BY clause, you can focus in on the worst TSQL code from different performance metrics like CPU, elapsed time, and so forth.
You should consider running code similar to this when any new application or major changes have been implemented into production. If you periodically review the performance of the TSQL code that is running on your server, you can keep on top of poor performing TSQL code. The next time you are trying to find poorly performing TSQL statements on your instance, consider looking at the stats in the procedure cache, using the methods covered in this article.
Read: TSQL Code Templates.
