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.