Microsoft® SQL Server® 2012 Bible

1043

Chapter 46: Maximizing Query Plan Reuse

46

Viewing the Plan Cache

Dynamic Management Views (DMVs) expose the contents of the Plan Cache. You can use

these DMVs to query the plan cache to verify that the query plan actually caches. The plan

cache can be large, and although it’s not recommended in a production environment, clear-

ing the cache can make checking for a specifi c query easier:

DBCC FREEPROCCACHE

You can also pass in a sql_handle or plan_handle into DBCC FREEPROCCACHE to remove

specifi c plans. To remove all cached plans for a specifi c database, you can use the DBCC

FLUSHPROCINDB(database_id) command.

To examine the procedure cache, use the sys.dm_exec_cached_plans DMV, which returns

the plan handle. The plan handle is a hashed value which can be used to look up an execu-

tion plan that currently resides in memory. This handle can be passed to sys.dm_exec_

sql_text(plan_handle) and sys.dm_exec_query_plan(plan_handle) to view the original SQL

code and the Query Execution Plan, as shown in Figure 46-1.

FIGURE 46-1

Viewing the Plan Cache is easy with a few DMVs. You can view the query execution plan by

clicking the XML in the query_plan column.

c46.indd 1043c46.indd 1043 7/31/2012 10:22:58 AM7/31/2012 10:22:58 AM

http://www.it-ebooks.info