Just a quick post because I’ve written a script I’m quite proud of this afternoon to help find queries that need tuning.

It Fetches the following information using sys.dm_exec_query_stats:

• Object/Database
• IndividualQuery
• TotalRunTime (s)
• TotalTimeWaiting (s)
• %TimeRunning
• %TimeWaiting
• ExecutionCount
• AverageRunTime
• AverageTimeWaiting (s)
• QueryPlan

Hope someone finds it useful.

 /*****************************************************

 Setup a temporary table to fetch the data we need from

 sys.dm_exec_query_stats.

 ******************************************************/

 IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp

 CREATE TABLE #temp

 (

 objectid INT,

 dbid INT,

 [Object] VARCHAR(8000),

 [IndividualQuery] NVARCHAR(MAX),

 [TotalRunTime (s)] DECIMAL(28,2),

 [TotalTimeWaiting (s)] DECIMAL(28,2),

 [%TimeRunning] DECIMAL(28,2),

 [%TimeWaiting] DECIMAL(28,2),

 [ExecutionCount] INT,

 [AverageRunTime] DECIMAL(28,2),

 [AverageTimeWaiting (s)] DECIMAL(28,2),

 [DatabaseName] NVARCHAR(MAX),

 [QueryPlan] XML

 )

 /*****************************************************

 Populate the temporary table with the data we need

 from sys.dm_exec_query_stats.

 ******************************************************/

 INSERT INTO #temp

 SELECT TOP 20

 qt.objectid,

 qt.dbid,

 [Object] = '',

 [IndividualQuery] = SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,

 ((CASE WHEN qs.statement_end_offset = -1

 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

 ELSE qs.statement_end_offset

 END - qs.statement_start_offset)/2) + 1),

 [TotalRunTime (s)] = CAST(qs.total_elapsed_time/ 1000000.0 AS DECIMAL(28,2)),

 [TotalTimeWaiting (s)] = CAST((qs.total_elapsed_time - qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2)),

 [%TimeRunning] = CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(28,2)),

 [%TimeWaiting] = CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 / qs.total_elapsed_time AS DECIMAL(28, 2)),

 [ExecutionCount] = qs.execution_count,

 [AverageRunTime] = CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count AS DECIMAL(28, 2)),

 [AverageTimeWaiting (s)] = CAST((qs.total_elapsed_time - qs.total_worker_time) / 1000000.0 / qs.execution_count AS DECIMAL(28, 2)),

 [DatabaseName] = DB_NAME(qt.dbid),

 [QueryPlan] = qp.query_plan

 FROM

 sys.dm_exec_query_stats qs

 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

 CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

 WHERE

 qs.total_elapsed_time > 0

 AND DB_NAME(qt.dbid) IS NOT NULL

 ORDER BY

 [TotalTimeWaiting (s)] DESC

 /*****************************************************

 This section of code is all about getting the object

 name from the dbid and the object id.

 ******************************************************/
-- Declare a Cursor

 DECLARE FetchObjectName CURSOR FOR

 SELECT

 objectid, dbid

 FROM

 #temp
-- Open the cursor

 OPEN FetchObjectName
-- Declare some vars to hold the data to pass into the cursor

 DECLARE @var1 INT,

 @var2 INT

 DECLARE @sql VARCHAR(MAX)

 DECLARE @object VARCHAR(MAX)
-- Create a temporary table to hold the result of the dynamic SQL

 IF OBJECT_ID('tempdb..#object') IS NOT NULL DROP TABLE #object

 CREATE TABLE #object

 (

 objectname VARCHAR(MAX)

 )
-- Loop through the 20 records from above and fetch the object names

 FETCH NEXT FROM FetchObjectName INTO @var1, @var2

 WHILE ( @@FETCH_STATUS <> -1 )

 BEGIN

 IF ( @@FETCH_STATUS <> -2 )
-- Set the SQL we need to execute

 SET @sql = 'USE [' + DB_NAME(@var2) + '];

 SELECT OBJECT_SCHEMA_NAME(' + CONVERT(VARCHAR(MAX),@var1) + ',' + CONVERT(VARCHAR(MAX),@var2) + ') + ''.'' + ' + 'OBJECT_NAME(' + CONVERT(VARCHAR(MAX),@var1) + ');'
-- Make sure the table is empty!

 TRUNCATE TABLE #object
-- Fetch the name of the object

 INSERT INTO #object

 EXEC(@sql)
-- Set the object name to the local var.

 SELECT @object = objectname FROM #object
-- Update the original results

 UPDATE #temp

 SET

 [Object] = RTRIM(LTRIM(@object))

 WHERE

 objectid = @var1

 and dbid = @var2
-- Go around the loop....

 FETCH NEXT FROM FetchObjectName INTO @var1, @var2

 END

 CLOSE FetchObjectName

 DEALLOCATE FetchObjectName
SELECT

 [Object] = [DatabaseName] + '.' + [Object],

 [IndividualQuery],

 [TotalRunTime (s)],

 [TotalTimeWaiting (s)],

 [%TimeRunning],

 [%TimeWaiting],

 [ExecutionCount],

 [AverageRunTime],

 [AverageTimeWaiting (s)],

 [QueryPlan]

 FROM

 #temp

 ORDER BY

 [TotalTimeWaiting (s)] DESC