Optimise for Ad-Hoc workload

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.

You update this setting using sp_configure and the reconfigure option via

EXECUTE sp_configure 'show advanced options',1;
EXECUTE sp_configure 'optimize for ad hoc workloads',1;


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