Finding Queries to Tune

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

FindingQueriesToTune

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
 
Advertisements

2 thoughts on “Finding Queries to Tune

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s