Optimize for ad hoc workloads

Enable the optimize for ad hoc workloads option to improve the efficiency of the plan cache for workloads that contain many single-use, ad hoc batches. When you initially run an ad hoc query, the database engine caches a compiled plan stub instead of the complete execution plan, thus saving space in the plan cache. If you run the ad hoc batch again, the database engine recognizes that the batch has been run before and replaces the compiled plan stub with the full, compiled plan in the plan cache.

To check whether this option is enabled, use the query:

$ sp_configure 'optimize for ad hoc workloads'

For more information about optimizing for ad hoc workloads, see the Microsoft SQL Server documentation.