Can you please help me in querying the plan cache in order to see which queries perform a scan and which queries perform a seek operation for a particular (nonclustered) index? I'm on SQL Server 2008 R2?


You will need to dig into query plans to identify if a particular query is performing a scan instead a seek, but you can (relatively) quickly identify what queries are using a particular index by running the following:

USE DatabaseName

        s.total_logical_reads / s.execution_count
    , SUBSTRING(t.TEXT, (s.statement_start_offset / 2) + 1, (
                CASE s.statement_end_offset
                    WHEN - 1
                        THEN DATALENGTH(t.TEXT)
                    ELSE s.statement_end_offset
                    END - s.statement_start_offset
                ) / 2
            ) + 1) AS statement_text
    , s.execution_count AS ExecutionCount
    , s.max_elapsed_time AS MaxElapsedTime
    , ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime
    , s.creation_time AS LogCreatedOn
    , *
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
WHERE TEXT LIKE '%Table_or_View_Index_Is_Defined_On%'
    AND TEXT NOT LIKE '%sys.dm_exec_query_stats s%'
ORDER BY s.total_logical_reads / s.execution_count DESC

Depending on what your end-goal is, you can adjust the ordering on this to your liking.

Once you identify a query that you want to dig into further, then pull up it's execution plan via:

SELECT query_plan 
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.