Chapter 8. SQL Server Cache and Stored Procedure Recompilations
In this chapter we will cover:
- Monitoring compilations and recompilations at instance level, using Reliability and Performance Monitor
- Monitoring recompilations using SQL Server Profiler
Introduction
When a query, a batch, or a stored procedure is submitted to SQL Server for execution for the first time, the query gets parsed and then compiled. The result of a compiled query is a query plan that is cached in the procedure cache. The procedure cache is a portion of memory that SQL Server allocates to cache its query plans.
When the query is executed, an execution plan with its execution context is derived from the cached query plan, in order to save time during query execution, because ...
Get Microsoft SQL Server 2012 Performance Tuning Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
