One of the most popular questions I received during my Comprehensive Database Performance Health Check is Finding Queries Using Particular Index. While this looks like a very simple question, it is actually not that simple. Here is the script which can help you to find queries using a particular index. I have blogged about this earlier as well here.
-- 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. Please note that plan cache cannot depend on 100%, but it provides information about the queries currently in the cache.
If you liked this blog, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.
Here are my few recent videos and I would like to know what is your feedback about them.
- Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
- One Scan for 3 Count Sum – SQL in Sixty Seconds #178
- SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
- COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
- COUNT(*) and Index – SQL in Sixty Seconds #175
- Index Scans – Good or Bad? – SQL in Sixty Seconds #174
- Optimize for Ad Hoc Workloads – SQL in Sixty Seconds #173
- Avoid Join Hints – SQL in Sixty Seconds #172
- One Query Many Plans – SQL in Sixty Seconds #171
Reference: Pinal Dave (http://blog.SQLAuthority.com)
I have opposite question. If index is not used according to your script for finding non-used indexes and if it is filtered is there anything in background done by SQL Server to check this index when inserting or updating data? Or nothing happens. Index could have for example 30.000 and table has 300.000 rows.