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.
SQL Server 2000 provides you with a virtual system table called master.dbo.syscacheobjects, which contains information about cached execution plans. SQL Server 2005 provides you with a compatibility view called sys.syscacheobjects, and a new DMV and two DMFs that replace the legacy system table and compatibility view. The three new objects are: sys.dm_exec_cached_plans, sys.dm_exec_plan_attributes, and sys.dm_exec_sql_text. These give you extremely useful information when analyzing the caching, compilation, and recompilation behavior of execution plans. The