Database Reference
In-Depth Information
Figure 26-9.
Plan cache content after query execution
SQL Server 2008 introduces a server-side configuration setting called
Optimize for ad-hoc workloads
. When this
setting is enabled, SQL Server caches small, less than 300-byte structures, called
Compiled Plan Stubs
, instead of
actual compiled plans. A Compiled Plan Stub is the placeholder that is used to keep track of which ad-hoc queries
were executed. When the same query runs a second time, SQL Server replaces the Compiled Plan Stub with the actual
compiled plan and reuses it going forward.
The
Optimize for ad-hoc workloads
setting is disabled by default. However, it should be enabled in most systems.
Even though it introduces slight CPU overhead on the second ad-hoc query recompilation, it could significantly
decrease plan cache memory usage on systems with heavy ad-hoc activity. That memory would be available for buffer
pool, which could reduce the number of physical I/O operations and improve system performance.
You can enable that setting with the code shown in Listing 26-18. In addition, it can be enabled in the
Advanced
tab of the
Server Properties
window in Management Studio.
Listing 26-18.
Enabling Optimize for ad-hoc activity setting
exec sys.sp_configure N'optimize for ad hoc workloads', N'1'
go
reconfigure with override
go
If you ran the code from Listing 26-19 with the
Optimize for ad-hoc workloads
setting enabled, you would see the
plan cache content shown in Figure
26-10
. As you can see, now it uses just 272KB of memory rather than the 32MB it
used before.
