Chapter 33. Investigating the plan cache
There lies within SQL Server a treasure trove of information that has the ability to provide insight into many different ways to tune your SQL Server databases. You can use this information to discover opportunities to reduce scans on indexes, identify potentially useful indexes, and find where specific operations are being performed. This information, and more, is contained within the plan cache (formerly known as the procedure cache).
The plan cache is where SQL Server stores compiled query plans. When a request is first made to execute a SQL statement, trigger, or stored procedure, SQL Server compiles a plan for executing that request. This plan is stored in memory in the plan cache. SQL Server can then reuse the plan when an identical or similar request is made in the future.
With the release of SQL Server 2005, a number of things changed with the plan cache, the most significant of which is your ability to access plans in the plan cache in the SHOWPLAN XML format through dynamic management objects (DMOs). By looking into the plan cache, you can examine its contents and make judgments based on frequency of use in order to determine what queries SQL Server is most often executing. By focusing your attention on these areas, you can identify opportunities for performance tuning with minimal effort.