Inside Microsoft® SQL Server® 2008: T-SQL Querying

Tools for Query Tuning

This section provides an overview of the query-tuning tools that will be used throughout these books, and it will focus on analyzing execution plans.

Cached Query Execution Plans

SQL Server 2008 provides several objects that you can query to analyze the behavior of cached query execution plans:

• The sys.dm_exec_cached_plans DMV contains information about the cached query execution plans, with a row per each cached plan.

• The sys.dm_exec_plan_attributes DMF contains one row per attribute associated with the plan, whose handle is provided as input to the DMF.

• The sys.dm_exec_sql_text DMF returns the text associated with the query, whose handle is provided as input to the DMF.

• The sys.dm_exec_query_plan DMF provides the XML form of ...