Find Table Scans in Query Plan Cache

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.