Advertisement
SQLSoldier

Untitled

Nov 4th, 2013
225
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.97 KB | None | 0 0
  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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement