Finding Queries to Tune

/*****************************************************
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

/*****************************************************
The final result set
******************************************************/SELECT
 [Object] = [DatabaseName] + '.' + [Object],
 [IndividualQuery],
 [TotalRunTime (s)],
 [TotalTimeWaiting (s)],
 [%TimeRunning],
 [%TimeWaiting],
 [ExecutionCount],
 [AverageRunTime],
 [AverageTimeWaiting (s)],
 [QueryPlan]
FROM
 #temp
ORDER BY
 [TotalTimeWaiting (s)] DESC