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.