When querying your plan cache, you may see a large number of plans with a usecounts value of 1.
An option here may be to set the “optimize for ad hoc workloads” configuration option. When set, this option means only a stub plan is placed into cache the first time a plan is compiled with subsequent executions removing the stub and inserting the full plan. This prevents the plan becoming bloated with plans that are never re-used and leaves room for valuable plans that are used many times.
You can see if the value is set on a server via: –
SELECT value_in_use FROM sys.configurations WHERE name = N'optimize for ad hoc workloads';
A value of 1 means this option is set.
EXECUTE sp_configure 'show advanced options',1; RECONFIGURE; EXECUTE sp_configure 'optimize for ad hoc workloads',1; RECONFIGURE;