---
title: Finding Queries to Tune
description: Finding Queries to Tune
image: https://blog.coeo.com/hubfs/Imported_Blog_Media/findingqueriestotune-5.png
---

[![](https://www.coeo.com/wp-content/themes/coeo/images/logo.svg)](https://blog.coeo.com/)

+44 (0)20 3051 3595 | [info@coeo.com](mailto:info@coeo.com) | [Client portal login](https://my.coeo.com)

# Finding Queries to Tune

# The Coeo Blog

![Simon Osborne](https://blog.coeo.com/hubfs/SimonOsCircle.jpg)

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](https://blog.coeo.com/hs-fs/hubfs/Imported_Blog_Media/findingqueriestotune-5.png?width=300&height=36&name=findingqueriestotune-5.png)](https://blog.coeo.com/hubfs/Imported_Blog_Media/findingqueriestotune-4.png)

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
```

[![](https://blog.coeo.com/hubfs/Imported_Blog_Media/68-2.png)](http://feeds.wordpress.com/1.0/gocomments/simonosbornesql.wordpress.com/68/) ![](https://blog.coeo.com/hs-fs/hubfs/Imported_Blog_Media/b-85.gif?width=1&height=1&name=b-85.gif)

### Subscribe to Email Updates

## Related posts

---

### [Troubleshooting Ola Hallengren’s Maintenance Solution](https://blog.coeo.com/troubleshooting-ola-hallengrens-maintenance-solution)

### [Domain-Independent Windows Failover Cluster for SQL Server AlwaysOn Availability Group](https://blog.coeo.com/domain-independent-windows-failover-cluster-for-sql-server-alwayson-availability-group)

### [SQLBits 2022 session - Field Testing Ola Hallengren’s Maintenance Solution](https://blog.coeo.com/sqlbits-2022-session-field-testing-ola-hallengrens-maintenance-solution)

### [Windows Active Directory Detached Cluster with SQL Server Basic Availability Group](https://blog.coeo.com/advanced-sql-server-windows-cluster-options)

![](https://www.coeo.com/wp-content/uploads/2016/12/logo-invert.png)

+44 (0)20 3051 3595 | info@coeo.com

## Contact Us

By clicking submit below, you consent to allow Coeo to store and process the personal information submitted above to provide you the content requested.

You may unsubscribe from these communications at any time. For more information on how to unsubscribe and our commitment to your privacy, please review our **[Privacy Policy](https://www.coeo.com/privacy/)**.

## Upcoming Events

[See all events](https://www.coeo.com/events/)

#### NOW Building, Thames Valley Park Drive, Reading, RG6 1RB

[![](https://www.coeo.com/wp-content/themes/coeo/images/social-glass.png)](https://www.glassdoor.co.uk/Overview/Working-at-Coeo-EI_IE959052.11,15.htm)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-in.png)](https://www.linkedin.com/company/coeo-ltd)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-twitter.png)](https://twitter.com/CoeoLtd)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-fb.png)](https://www.facebook.com/coeoltd/)

![](https://www.coeo.com/wp-content/themes/coeo/images//menu-icon.png)

![](https://www.coeo.com/wp-content/themes/coeo/images//menu-close.png)

![](https://www.coeo.com/wp-content/uploads/2016/12/logo-invert.png)

+44 (0)20 3051 3595 | [info@coeo.com](mailto:info@coeo.com)

- [Solutions](https://www.coeo.com/solutions/)
- [Next Steps](https://www.coeo.com/next-steps/)
- [Dedicated Support](https://www.coeo.com/dedicated-support/)
- [Case studies](https://www.coeo.com/case-studies/)
- [Technologies](https://www.coeo.com/solutions/technologies/)

- [Industries](https://www.coeo.com/industries/)
- [Finance](https://www.coeo.com/industries/finance/)
- [Retail](https://www.coeo.com/industries/retail/)
- [Technology](https://www.coeo.com/industries/technology/)

- [The Team](https://www.coeo.com/people/)
- [Join Us](https://www.coeo.com/careers/)
- [Graduate Programme](https://www.coeo.com/graduate-programme/)

- [About Coeo](https://www.coeo.com/about-coeo/)
- [The Coeo Blog](https://www.coeo.com/blog/)
- [Contact us](https://www.coeo.com/contact-us/)
- [Privacy Notice](https://www.coeo.com/privacy/)
- [Cookie Policy](https://www.coeo.com/privacy#Cookie_Policy)

- [Events](https://www.coeo.com/events)

Sign up to our newsletter ![go arrow](https://www.coeo.com/wp-content/themes/coeo/images/newsletter-go.png)

 Back to top