Category: Cached Plan Analysis

Cached Plan Analysis, SQL

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.

Cached Plan Analysis, SQL, Wait types

How to identify parallel plans in SQL Server
Few years back, on one of my engagements, identified lots of performance issues with an application. As part of a detailed analysis, it is observed there are lots of queries under performing with wait type “CXPACKET”. In short, CXPACKET wait types are involved in parallel query execution. These wait types indicate that the SPID is waiting on a parallel process to complete or start. There are many reasons your query may undergo a parallel execution like missing index, non optimized sql script etc. But, CXPACKET is not ALWAYS a bad thing.We will see about CXPACKET in detail with another post.

Now, We will see how to identify the queries with parallel execution. Once you identified the queries, you can further examine to identify the actual cause of the issues and optimize.

Script

select p.*,q.*,cp.plan_handle
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0

Please note that the above query gets information from cached plan in SQL Server. Hence, I will recommend to run a load test with a real time workload and use the script to identify the parallel executing queries. It is also important that the cached plans may get flushed due various factors like memory pressure, recompile of procedures, auto stats updates etc. But, this helped me a lot at times!!!

[Edit – 8th-Sep-202]

How do we know the degree of parallelism of a currently running query?

select session_id, scheduler_id
from sys.dm_os_tasks
where parent_task_address IS NOT NULL
--AND session_id = ? --You can uncomment and give spid as required.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Cached Plan Analysis, SQL

How do we cleanup plan cache in SQL Server?

At times, we may need to cleanup plan cache in SQL Server. I have come across situations where I need to flush the plans from the cache for our testing purposes
to troubleshoot performance issues related to procedure/test environment.However, there is caution for this to use at production boxes as it may hurt the performance badly.

There are three ways to clean up plan cache as per our requirement.

1. DBCC FREEPROCCACHE

– This DBCC command will clear/clean the entire cached plans from the SQL Server for the instance
– I personally used for my test environment to clean up the entire plan before do a load test and followed by a single user test execution(this to generate fresh plan without affecting the actual load test)


--Clean up the entire plans for the instance
DBCC FREEPROCCACHE with NO_INFOMSGS
GO
--Check the count of the plans
Select dbid,db_name(dbid),count(1) From sys.dm_exec_cached_plans dec
CROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS des
where db_name(dbid) in ('Nov_ProdStats','Dec_ProdStats') --Sample Databases for the demonstration
Group by db_name(dbid), dbid


2. DBCC FLUSHPROCINDB

– This DBCC command will flush the plans for a database
– Database id is a mandatory parameter for FLUSHPROCINDB execution
– This way we will make sure the clean up will not have any impact on other databases


--Clean up the plans for a database 
DBCC FLUSHPROCINDB (8) -- 8 is the databaseid of Dec_ProdStats
GO
--Check the count of the plans
Select dbid,db_name(dbid),count(1) From sys.dm_exec_cached_plans dec
CROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS des
where db_name(dbid) in ('Nov_ProdStats','Dec_ProdStats') --Sample Databases for the demonstration
Group by db_name(dbid), dbid


3. DBCC FREEPROCCACHE with plan handle as parameter

– This is to clean up the cache only for the plan handle supplied

	
--Identify the plan handle
SELECT cp.plan_handle, st.[text],*
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE object_name(objectid)='test'--test is a sample procedure name
GO
--Clean up the plan for TEST procedure
DBCC FREEPROCCACHE(0x05000C001B67A4684061C3B9010000000000000000000000)
GO
--Check the count of the plans
Select dbid,db_name(dbid),count(1) From sys.dm_exec_cached_plans dec
CROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS des
where db_name(dbid) in ('Nov_ProdStats','Dec_ProdStats') --Sample Databases for the demonstration
Group by db_name(dbid), dbid

Hope you enjoyed this one, see you soon with another one. Meanwhile you can share any feedback or your thoughts here.

Cached Plan Analysis, SQL

How to find index usage from cached plan in SQL Server

Today, I would like to share a script to identify the usage of index from cached plan in SQL Server. This would help us to identify the places (procs/functions etc) where the index is being used. One interesting use-case is to identify the index usage that has been introduced recently during the performance optimization.


;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
CachedPlansCTE
(
	DatabaseName,SchemaName,TableName,IndexName,
	QueryText,QueryPlan,CacheObjectType,ObjectType
)
AS
(
SELECT	
        RelOp1.op.value(N'@Database', N'varchar(128)') AS DatabaseName,
	RelOp1.op.value(N'@Schema', N'varchar(128)') AS SchemaName,
	RelOp1.op.value(N'@Table', N'varchar(128)') AS TableName,
	RelOp1.op.value(N'@Index', N'varchar(128)') AS IndexName,
	cp.TEXT AS QueryText,cp.query_plan AS QueryPlan,
	cp.cacheobjtype AS CacheObjectType,cp.objtype AS ObjectType
FROM (SELECT usecounts,cacheobjtype,objtype,query.text
 ,executionplan.query_plan
 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' ) cp
CROSS APPLY cp.query_plan.nodes(N'//Object') RelOp1 (op)
)
SELECT	QueryPlan,QueryText,CacheObjectType,	ObjectType,
		DatabaseName,	SchemaName,TableName,	IndexName
FROM CachedPlansCTE
WHERE CacheObjectType = N'Compiled Plan'
/*
*********************************************************
SEARCH SCENARIOS
****************
/* if you need to search for a particular index*/
and (IndexName like '%Indexname%')
/* if you need to search ONLY for SELECT queries*/
and (QueryText not like '%insert%')
and (QueryText not like '%update%')
*********************************************************
*/
OPTION (MAXDOP 1)

See Also:
How to identify Missing Index from Cached Plan in SQL Server
How do you find cached plan for a procedure in SQL Server

Cached Plan Analysis, SQL

How to identify Missing Index from Cached Plan in SQL Server

Here, we are going to find a simple and very efficient way to identify missing indexes in SQL Server. The method is used to query cached plan in SQL Server and identify the missing index information from the Cached plan.

Please have a look and share your thoughts on the same.

Script


;WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT 
	query_plan, n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text,
	n.value('(//MissingIndexGroup/@Impact)[1]', 'FLOAT') AS impact,
	DB_ID(REPLACE(REPLACE(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'),'[',''),']','')) AS database_id,
	OBJECT_ID(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' +
    n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' +
    n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')) AS OBJECT_ID,
	n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' +
    n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' +
    n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)') AS statement,
	(   SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
		FROM n.nodes('//ColumnGroup') AS t(cg)
		CROSS APPLY cg.nodes('Column') AS r(c)
		WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'EQUALITY' FOR  XML PATH('')) AS equality_columns,
	(  SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
		FROM n.nodes('//ColumnGroup') AS t(cg)
		CROSS APPLY cg.nodes('Column') AS r(c)
		WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INEQUALITY' FOR  XML PATH('')) AS inequality_columns,
	(  SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
		FROM n.nodes('//ColumnGroup') AS t(cg)
		CROSS APPLY cg.nodes('Column') AS r(c)
		WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INCLUDE' FOR  XML PATH('')) AS include_columns,
		tab.text,ObjectName
FROM
(
   SELECT query_plan,text,ObjectName   FROM (
			SELECT usecounts,cacheobjtype,objtype,query.text,object_name(query.objectid) ObjectName ,executionplan.query_plan
			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' ) qs
   WHERE qs.query_plan.exist('//MissingIndex')=1
) AS tab (query_plan,text,ObjectName)

CROSS APPLY query_plan.nodes('//StmtSimple') AS q(n)
WHERE n.exist('QueryPlan/MissingIndexes') = 1