Find Table Scans in Query Plan Cache

01 August,2015 by Jack Vamvas

Question: I’m managing a SQL Server with queries running much slower than normal. I’ve analysed the sys.dm_db_index_usage_stats  for heap  scans. To identify the heap statistics I identified rows where the column index_id returned a value of zero.

I’ve identified some heaps with a very high level of usage . How can I find the queries which are creating these large amounts of user scans?

Answer: Using the  sys.dm_db_index_usage_stats  is a good method  to identify heaps. Checking the user scans is a good way of finding clues for inefficient queries. It does not guarantee these queries are the root cause, but it gives you an insight to which queries are consuming high amounts of resources.

Once you’ve found your table – use this query to find queries, in the plan cache. It is possible the query plan cache has been cleared via space management, a SQL Server restart or a DBCC FREESYSTEMCACHE . In that case it won’t be possible to find the query.

This query uses the sys.dm_exec_cached_plans and sys.dm_exec_query_plan SQL Server DMV. Read more on Top 5 SQL Server DMV for Index Analysis - SQL Server DBA

 

 

DECLARE @Table_Name sysname = 'my_table_name';
;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
cp.plan_handle
,operators.value('(TableScan/Object/@Schema)[1]','sysname') AS Schema_Name
,operators.value('(TableScan/Object/@Table)[1]','sysname') AS Table_Name
,operators.value('(TableScan/Object/@Index)[1]','sysname') AS Index_Name
,operators.value('@PhysicalOp','nvarchar(50)') AS Physical_Operator
,cp.usecounts
,qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY query_plan.nodes('//RelOp') rel(operators)
WHERE operators.value('@PhysicalOp','nvarchar(60)') IN ('Table Scan')
AND operators.value('(TableScan/Object/@Table)[1]','sysname') = QUOTENAME(@Table_Name,'[');

Read More on Page Life Expectancy, query timeouts, root cause analysis

SQL Server page life expectancy and memory bottleneck

SQL Server – How to troubleshoot query timeouts

Find the Root cause of a SQL Server Performance downgrade.

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Find Table Scans in Query Plan Cache


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer