10

SQL SERVER - Finding Queries Using Particular Index - SQL Authority with Pinal D...

 3 years ago
source link: https://blog.sqlauthority.com/2021/07/15/sql-server-finding-queries-using-particular-index/?utm_campaign=sql-server-finding-queries-using-particular-index
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

SQL SERVER – Finding Queries Using Particular Index

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.

SQL SERVER - Finding Queries Using Particular Index ParticularIndex-800x533

-- Query to identify query using any speicific index
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.

Reference: Pinal Dave (http://blog.SQLAuthority.com)


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK