Microsoft SQL Server Performance: optimize for ad-hoc workloads

A SQL statement always requires an execution plan to run it over your data. That plan is used to return the data quickly and efficiently. SQL statements might need to be used over and over in quick succession, so to improve performance the server uses a Plan Cache to save the execution plans for later. When the same SQL comes in again, the database can use the already cached plan to execute the statement which saves a lot of time. If a statement is only ever used once, then the execution plan in the cache will age out and disappear.

What if you have a lot of SQL statements coming to your database that are only used once? As you can probably see, your plan cache will be filled with execution plans that will never be re-used. This can prevent statements that actually repeat themselves from getting in the cache and slow your system down. Third-party reporting and even JDE apps and UBEs can and do send these on-time use SQL statements quite frequently. These are called “ad-hoc” statements.

The configuration setting “optimize for ad-hoc workloads” can help you speed up your system by changing how plans are cached. With this setting enabled, the first time a SQL statement runs it still creates an execution plan, but it only stores a “plan stub” in the cache rather than the entire execution plan. If the statement is run a second time, SQL Server will store the full plan in the plan cache. If it’s never run again then it never has to create a full execution plan, and that frees up space for SQL that actually repeats itself

Enabling optimize for ad-hoc workloads is highly recommended for JD Edwards running on SQL Server, especially when using third-party applications that interface with the database. Before enabling it, be sure to take a performance baseline so that you can compare before and after data. It’s unlikely you will find anything negative, but it will be nice to have the data to back up your choice.