Untitled

Declare @Index sysname;

Set @Index = N'idx_EntityChangeLog_EntityId';

 

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sqlx)

SELECT DB_NAME(deqp.dbid),

    SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1,

    (CASE deqs.statement_end_offset

        WHEN -1 THEN DATALENGTH(dest.text)

        ELSE deqs.statement_end_offset

    END - deqs.statement_start_offset) / 2 + 1) AS StatementText,

    deqs.statement_end_offset,

    deqs.statement_start_offset,

    deqp.query_plan,

    deqs.execution_count,

    deqs.total_elapsed_time,

    deqs.total_logical_reads,

    deqs.total_logical_writes

FROM    sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

WHERE deqp.dbid = DB_ID()

And deqp.query_plan.exist('//sqlx:RelOp[@PhysicalOp="Index Seek"]') = 1

AND deqp.query_plan.exist('//sqlx:Object[@Index=sql:variable("@Index")]') = 1;