How to identify Scans (Table/index) from cached plan in SQL Server

Today, we will quickly see how to identify the scans happening on SQL Server. I had to analyse a test SQL Server environment to identify performance bottlenecks. So, the team was looking for a way to get the scans happening on their server to further optimize and confirm the performance.

Here is a small script I created to understand the Scans on the server. Please note that I divided the script into two parts,
1. To generate a snapshot of the cached plans into a table called – Temp_CacheDump_Analyser
2. To query the Temp_CacheDump_Analyser table for scan operators

This way, even if you want to query it multiple times for different reasons, you can query the cache dump table instead of cached plan tables.

–Generate the snapshot with the required fields


SELECT usecounts,cacheobjtype,objtype,query.text
 ,executionplan.query_plan into Temp_CacheDump_Analyser
 FROM sys.dm_exec_cached_plans
 OUTER APPLY sys.dm_exec_sql_text(plan_handle) as query
 OUTER APPLY sys.dm_exec_query_plan(plan_handle) as executionplan
 WHERE [text] NOT LIKE '%sys%'
 AND cacheobjtype ='compiled plan' 

–Query the row data to identify scans on your SQL Server

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
CachedPlans
(
	ParentOperationID, OperationID, PhysicalOperator, LogicalOperator, EstimatedCost, EstimatedIO,
	EstimatedCPU, EstimatedRows, QueryText, QueryPlan, CacheObjectType, ObjectType
)
AS
(
SELECT	RelOp.op.value(N'../../@NodeId', N'int') AS ParentOperationID,
		RelOp.op.value(N'@NodeId', N'int') AS OperationID,
		RelOp.op.value(N'@PhysicalOp', N'varchar(50)') AS PhysicalOperator,
		RelOp.op.value(N'@LogicalOp', N'varchar(50)') AS LogicalOperator,
		RelOp.op.value(N'@EstimatedTotalSubtreeCost ', N'float') AS EstimatedCost,
		RelOp.op.value(N'@EstimateIO', N'float') AS EstimatedIO,
		RelOp.op.value(N'@EstimateCPU', N'float') AS EstimatedCPU,
		RelOp.op.value(N'@EstimateRows', N'float') AS EstimatedRows,
		qp.TEXT AS QueryText, qp.query_plan AS QueryPlan,
		qp.cacheobjtype AS CacheObjectType, qp.objtype AS ObjectType
	FROM Temp_CacheDump_Analyser qp
	CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp (op)
)
SELECT	QueryPlan, ParentOperationID, OperationID, PhysicalOperator, LogicalOperator, QueryText,
		CacheObjectType, ObjectType, EstimatedCost, EstimatedIO, EstimatedCPU, EstimatedRows
FROM CachedPlans
	WHERE CacheObjectType = N'Compiled Plan'
		AND	(PhysicalOperator = 'Clustered Index Scan' OR PhysicalOperator = 'Table Scan'
		OR PhysicalOperator = 'Index Scan' OR PhysicalOperator = 'Lookup')

The caveat is the above results are based on the data available at the point in time in the cache. There may be sceanrios these data gets flushed, so the data should be collected and analysed in a regular way that means, its not a one time activity.

Hope, you enjoyed this post, please share your thoughts and feedback.

One thought on “How to identify Scans (Table/index) from cached plan in SQL Server”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s