Saturday, January 20, 2018
Term: Compiled Plan
1 post(s)

SQL Server - Optimize for Ad Hoc Workload Server Setting (Internals)

By Saleem Hakani on 9/23/2014

By default, when a query is executed in SQL Server, it compiles and generates an execution plan for that query and stores that plan in the plan cache. Now if this happens to be a one-off/ad-hoc query that will never be run again, then the newly generated plan is wasted and its space that is being used in the plan cache is also wasted. This means, the buffer pool now contains compiled plan of queries that will never be executed.

Now imagine if you have hundreds of ad-hoc queries like this?

SQL Server introduced a new server level setting called “Optimize for Ad Hoc Workload” which helps address this issue. This setting improves the efficiency of plan cache for one-off/ad hoc queries/batches or workloads. Once you enable this option it changes the behavior of storing the compiled plan in the plan cache. Internally, when the query is executed the first time, the database engine stores query hash in the plan cache which is very small in size compared to the compiled plan. However, the second time the same query is executed, the database engine checks and recognizes that the query hash exists for the ad-hoc workload and it goes ahead with creating a full compiled plan for that query and stores is in the plan cache by removing the query hash. All subsequent execution of this query will use the plan from the plan cache.

Best Practices
Buffer Pool Memory
Compiled Plan
Improve Query Performance
Optimize for Ad Hoc Workload
Plan Cache
Plan Cache Bloat
Query Hash
Server Level Setting
SP_Configure Setting
SQL Internals
SQL Article Tags
Copyright [2014] by SQLCOMMUNITY.COM