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.
 
 
Search WWH ::




Custom Search