One query many plans – SQL Server

When you see this happening in your system. Here is the script that uses system DMVs to look at the cache for execution plans:

SELECT 
cplan.usecounts,
qryplan.[query_plan],
sqltxt.text,
planobj.pages_in_bytes / 1024 AS [PlanKB],
cplan.plan_handle,
cplan.cacheobjtype,
cplan.objtype
FROM sys.dm_exec_cached_plans cplan
OUTER APPLY sys.dm_exec_sql_test(cplan.[plan_handle] sqltxt
OUTER APPLY sys.dm_exec_query_pla(cplan.[plan_handle] qrypln
INNER JOIN sys.dm_os_memory_ibjects planobj
ON planobj.memory_object_address = cplan.memory_object_address
WHERE cplan.parent_plan_handle is NULL
AND cplan.cacheobjtype IN (N'Compiled Plan',N'Compiled Plan Stub')
ORDER BY cplan.objtype, cplan.plan_handle;

You see the many plans scenario when dynamic SQL is being generated by ORM. It would help if you can convert functions or queries into a stored procedure so that fewer plans get cached into the system and get re-used.

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