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. ...