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.