on January 24, 2017
Sometimes you know a query is out there, but it’s hard to find the exact query.
SQL Server stores query execution plans in cache, but it can be difficult to query the XML it stores. And there’s always a chance that the query plan won’t be there, due to memory pressure, recompile hints, or the plan cache being cleared by setting changes or other administrative actions.
I can’t guarantee that your query can always be in the plan cache. But I can make it a bit easier to find the query you’re looking for.
In this post I give example code to find queries using a specific index, or using an index hint. But you’ll find that it’s pretty easy to adapt these queries for whatever you’re looking for.
How I like to Search the Plan Cache
If I’m looking in SQL Server’s Execution Plan Cache, I like to use the sys.dm_exec_text_query_plan dynamic management view. This stores those XML query plans as text.
I learned about using this DMV from Grant Fritchey in his post, “Querying the Plan Cache, Simplified.” Grant points out that while doing wildcard searches in the text version of a query plan isn’t fast, querying it as XML is often even slower.
Things to remember when searching the plan cache:
- Queries will be missing from the plan cache
- The larger your plan cache and the slower your CPUs, the longer this will take: handle with care
Search Query Store, if You’ve Got It
If you’ve enabled the SQL Server 2016+ Query Store on your databases, you’ve got something better to search than the plan cache. I’m including code to search Query Store as well.
How to Find Queries Using a Specific Index
Search for queries in the execution plan cache
Simply plug the name of the index you’re looking for into this query. If you have multiple databases with the same index name, you’ll need to add additional criteria to get just the database you’re looking for.
In many cases, this will return more queries than you’re looking for, because inserts and deletes will reference all nonclustered indexes on the table. You can either add additional predicates to this query, or just look through everything on the list, depending on what you’re doing.
/* Execution plan cache */
SELECT
querystats.plan_handle,
querystats.query_hash,
SUBSTRING(sqltext.text, (querystats.statement_start_offset / 2) + 1,
(CASE querystats.statement_end_offset
WHEN -1 THEN DATALENGTH(sqltext.text)
ELSE querystats.statement_end_offset
END - querystats.statement_start_offset) / 2 + 1) AS sqltext,
querystats.execution_count,
querystats.total_logical_reads,
querystats.total_logical_writes,
querystats.creation_time,
querystats.last_execution_time,
CAST(query_plan AS xml) as plan_xml
FROM sys.dm_exec_query_stats as querystats
CROSS APPLY sys.dm_exec_text_query_plan
(querystats.plan_handle, querystats.statement_start_offset, querystats.statement_end_offset)
as textplan
CROSS APPLY sys.dm_exec_sql_text(querystats.sql_handle) AS sqltext
WHERE
textplan.query_plan like '%PK_Sales_Invoices%'
ORDER BY querystats.last_execution_time DESC
OPTION (RECOMPILE);
GO
Find queries using the index in Query Store
Here’s a starter query to get you going in Query Store when you’re looking to see who’s using an index.
This query groups by the query_id and query_hash because Query Store records runtime stats for a query over multiple intervals.
Similar to the previous query, just plug in the index name you’re looking for into the query plan text:
/* Query Store */
SELECT
qsq.query_id,
qsq.query_hash,
(SELECT TOP 1 qsqt.query_sql_text FROM sys.query_store_query_text qsqt
WHERE qsqt.query_text_id = MAX(qsq.query_text_id)) AS sqltext,
SUM(qrs.count_executions) AS execution_count,
SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_reads) as est_logical_reads,
SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_writes) as est_writes,
MIN(qrs.last_execution_time AT TIME ZONE 'Pacific Standard Time') as min_execution_time_PST,
MAX(qrs.last_execution_time AT TIME ZONE 'Pacific Standard Time') as last_execution_time_PST,
SUM(qsq.count_compiles) AS sum_compiles,
TRY_CONVERT(XML, (SELECT TOP 1 qsp2.query_plan from sys.query_store_plan qsp2
WHERE qsp2.query_id=qsq.query_id
ORDER BY qsp2.plan_id DESC)) AS query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp on qsq.query_id=qsp.query_id
CROSS APPLY (SELECT TRY_CONVERT(XML, qsp.query_plan) AS query_plan_xml) AS qpx
JOIN sys.query_store_runtime_stats qrs on qsp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qsrsi on qrs.runtime_stats_interval_id=qsrsi.runtime_stats_interval_id
WHERE
qsp.query_plan like N'%PK_Sales_Invoices%'
AND qsp.query_plan not like '%query_store_runtime_stats%' /* Not a query store query */
AND qsp.query_plan not like '%dm_exec_sql_text%' /* Not a query searching the plan cache */
GROUP BY
qsq.query_id, qsq.query_hash
ORDER BY est_logical_reads DESC
OPTION (RECOMPILE);
GO
How to Find Queries Using an Index Hint (*any* index hint)
Sometimes you just want to know if index hints are in play. If code is around hinting specific indexes, that means you need to be careful dropping or renaming those indexes– or queries may fail.
Search the execution plan cache for index hints
To find forced indexes in the plan cache, look for plans that contain ‘%ForcedIndex=“1”%’, like this:
/* Execution plan cache */
SELECT
querystats.plan_handle,
querystats.query_hash,
SUBSTRING(sqltext.text, (querystats.statement_start_offset / 2) + 1,
(CASE querystats.statement_end_offset
WHEN -1 THEN DATALENGTH(sqltext.text)
ELSE querystats.statement_end_offset
END - querystats.statement_start_offset) / 2 + 1) AS sqltext,
querystats.execution_count,
querystats.total_logical_reads,
querystats.total_logical_writes,
querystats.creation_time,
querystats.last_execution_time,
CAST(query_plan AS xml) as plan_xml
FROM sys.dm_exec_query_stats as querystats
CROSS APPLY sys.dm_exec_text_query_plan
(querystats.plan_handle, querystats.statement_start_offset, querystats.statement_end_offset)
as textplan
CROSS APPLY sys.dm_exec_sql_text(querystats.sql_handle) AS sqltext
WHERE
textplan.query_plan like N'%ForcedIndex="1"%'
and UPPER(sqltext.text) like N'%INDEX%'
OPTION (RECOMPILE);
GO
I also specify that the text of the query needs to have the word ‘INDEX’ in it (which is part of an index hint), to rule out false positives in the plan cache of queries running against system tables.
Find index hints in Query Store
To find forced indexes in Query Store, you can similarly look for plans with'%ForcedIndex=“1”%', like this:
/* Query Store */
SELECT
qsq.query_id,
qsq.query_hash,
(SELECT TOP 1 qsqt.query_sql_text FROM sys.query_store_query_text qsqt
WHERE qsqt.query_text_id = MAX(qsq.query_text_id)) AS sqltext,
SUM(qrs.count_executions) AS execution_count,
SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_reads) as est_logical_reads,
SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_writes) as est_writes,
MIN(qrs.last_execution_time AT TIME ZONE 'Pacific Standard Time') as min_execution_time_PST,
MAX(qrs.last_execution_time AT TIME ZONE 'Pacific Standard Time') as last_execution_time_PST,
SUM(qsq.count_compiles) AS sum_compiles,
TRY_CONVERT(XML, (SELECT TOP 1 qsp2.query_plan from sys.query_store_plan qsp2
WHERE qsp2.query_id=qsq.query_id
ORDER BY qsp2.plan_id DESC)) AS query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp on qsq.query_id=qsp.query_id
CROSS APPLY (SELECT TRY_CONVERT(XML, qsp.query_plan) AS query_plan_xml) AS qpx
JOIN sys.query_store_runtime_stats qrs on qsp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qsrsi on qrs.runtime_stats_interval_id=qsrsi.runtime_stats_interval_id
WHERE
qsp.query_plan like N'%ForcedIndex="1"%'
GROUP BY
qsq.query_id, qsq.query_hash
ORDER BY est_logical_reads DESC
OPTION (RECOMPILE);
GO
REGISTER