Question: How to Find Queries Using an Index From SQL Server Plan Cache?
Answer: During the recent Comprehensive Database Performance Health Check, I had an interesting situation, where I suggested that we will drop an index for a query. I had clearly proved with the help of my script that the index is not used at all since the last SQL Server restart (in that case it was over 9 months).
However, the company CEO who was the original DBA for their system was on the call and he really wanted to be sure that they are not using that index as he was the original creator of the index. Even though it was clear from the unused index script, he wanted to get the extra assurance that his index is not used at all by some other way.
At that time, I ran the following script which goes into the plan cache and checks if Index is used by any query in the recent time or not. Please note that plan cache cannot depend on 100% but it for sure provides information about the queries which are currently in the cache.
Let us see the script quickly.
-- Query to identify query using any speicific index -- Pinal Dave (https://blog.sqlauthority.com) SELECT SUBSTRING(sqltext.text, (deqs.statement_start_offset / 2) + 1, (CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(sqltext.text) ELSE deqs.statement_end_offset END - deqs.statement_start_offset) / 2 + 1) AS sqltext, deqs.execution_count, deqs.total_logical_reads/execution_count AS avg_logical_reads, deqs.total_logical_writes/execution_count AS avg_logical_writes, deqs.total_worker_time/execution_count AS avg_cpu_time, deqs.last_elapsed_time/execution_count AS avg_elapsed_time, deqs.total_rows/execution_count AS avg_rows, deqs.creation_time, deqs.last_execution_time, CAST(query_plan AS xml) as plan_xml FROM sys.dm_exec_query_stats as deqs CROSS APPLY sys.dm_exec_text_query_plan (deqs.plan_handle, deqs.statement_start_offset, deqs.statement_end_offset) as detqp CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS sqltext WHERE detqp.query_plan like '%Name of Your Index Here%' ORDER BY deqs.last_execution_time DESC OPTION (MAXDOP 1, RECOMPILE); GO
In this query in the WHERE condition, you will have to insert your index name and it will bring out the elaborative result from the plan cache.
Let me know if you find this query helpful or if you use any other query. I will be happy to share the query on this blog with the due credit to you.
Reference: Pinal Dave (https://blog.SQLAuthority.com)
On a modern system, the plan cache could be >10GB and much more than 100K objects. Parsing the plan cache with an SQL statement could take forever only to miss as plans are evicted. Years ago, I built a C# client program to grab plans from SQL Server, parsing for index usage (www.qdpma.com/), building a cross-reference between plans and index usage. I have captured and parsed several thousand plans. On a large system, the plan cache may be huge, but typically the execution stats having total_worker_time > 10000 micro-sec is not overly huge. Another option is to simply get all the stored procs and functions from one database, then generate the estimated plans, and again, building the proc plan – index cross-reference. SQL is great, but there things better done in C#