SHARE
TWEET

Untitled

SQLSoldier Nov 4th, 2013 98 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Declare @Index sysname;
  2. Set @Index = N'idx_EntityChangeLog_EntityId';
  3.  
  4. WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sqlx)
  5. SELECT DB_NAME(deqp.dbid),
  6.     SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1,
  7.     (CASE deqs.statement_end_offset
  8.         WHEN -1 THEN DATALENGTH(dest.text)
  9.         ELSE deqs.statement_end_offset
  10.     END - deqs.statement_start_offset) / 2 + 1) AS StatementText,
  11.     deqs.statement_end_offset,
  12.     deqs.statement_start_offset,
  13.     deqp.query_plan,
  14.     deqs.execution_count,
  15.     deqs.total_elapsed_time,
  16.     deqs.total_logical_reads,
  17.     deqs.total_logical_writes
  18. FROM    sys.dm_exec_query_stats AS deqs
  19. CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
  20. CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
  21. WHERE deqp.dbid = DB_ID()
  22. And deqp.query_plan.exist('//sqlx:RelOp[@PhysicalOp="Index Seek"]') = 1
  23. AND deqp.query_plan.exist('//sqlx:Object[@Index=sql:variable("@Index")]') = 1;
RAW Paste Data
Pastebin PRO Summer Special!
Get 40% OFF on Pastebin PRO accounts!
Top