Database Reference
In-Depth Information
Starting with SQL Server 2008, you can use the
sys.fn_validate_plan_guide
system function to check if a plan
guide is still valid. The code in Listing 26-28 shows an example of this.
Listing 26-28.
Validating plan guides
select
pg.plan_guide_id, pg.name, pg.scope_type_desc
,pg.is_disabled, vpg.message
from sys.plan_guides pg
cross apply
(
select message
from sys.fn_validate_plan_guide(pg.plan_guide_id)
) vpg
The
sys.fn_validate_plan_guide
function returns a row if the plan guide is incorrect. You can see an example
of its output in Figure
26-16
.
Figure 26-16.
Validating plan guides
As a final note, plan guides are only supported in the Standard, Enterprise, and Developer editions of SQL Server.
You can still create plan guides in the unsupported editions, however Query Optimizer will ignore them.
Plan Cache Internals
SQL Server separates plan cache into four different memory areas called
cache stores
. Each cache store caches
different entities and plans.
The
SQL Plans
cache store (internal name
CACHESTORE_SQLCP
) stores plans for
parameterized and ad-hoc queries and batches, as well as for autoparameterized plans.
The
Object Plans
cache store (
CACHEHSTORE_OBJCP
) stores plans for T-SQL objects, such as
stored procedures, triggers, and user-defined functions.
The
Extended Stored Procedures
cache store (
CACHEHSTORE_XPROC
) stores plans for extended
stored procedures.
The
Bound Trees
cache store (
CACHESTORE_PHDR
) stores bound trees generated during the
query optimization stage.
■
SQL Server uses other cache stores that are not associated with plan cache. You can examine their content by
using the
sys.dm_os_memory_cache_counters
data management view.
Note
You can monitor the size of each cache store with a select statement, as shown in Listing 26-29.