MSSQL: Improving the statistic plan for dynamic queries and procedures.

Dynamic queries are those that having similarity yet not exactly the same syntax and causing them to be cached for reuse by the planner. This can cause bloating to memory usage. In order to tackle the shortcomings, the user can set the optimizer for ad hoc only. Memory utilization can grow out of a desirable level.

Figure out what is the current plan size.

SELECT objtype, cacheobjtype,  AVG(usecounts) AS Avg_UseCount,  
SUM(refcounts) AS AllRefObjects, 
 SUM(CAST(size_in_bytes AS bigint))/1024/1024 AS Size_MB
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc' AND usecounts = 1
GROUP BY objtype, cacheobjtype;

Run sp_configure to find out what the optimize for ad hoc set to.

Example of changing the optimize for ad hoc workloads and reduce procedure cache usage from these statements without creating any additional indexes.

USE VCDB
EXEC sp_configure 'show advanced options', 1
RECONFIGURE 
GO
EXEC sp_configure 'optimize for ad hoc workloads', 1
RECONFIGURE
GO 

After reconfiguring the optimizer for ad hoc workloads, the config_value would change to 1.

Example of clearing procedure cache. 

alter database scoped configuration clear procedure cache;

This is relatively similar to Oracle's shared pool cache clearing in term of mechanism.

alter system flush shared_pool;