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.

syscacheobjects

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

Get Inside Microsoft® SQL Server™ 2005: T-SQL Querying now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.