Compilation, Recompilation, and Plan Caching in SQL Server 2005

Author → Asif

SQL Server will manage the objects in its cache in a few main ways: freeing up buffers or aging execution plans. Before a query, batch, stored procedure, trigger, prepared statement, or dynamic SQL statement begins execution on a SQL Server, the batch gets compiled into a plan. The plan is then executed for its effects or to produce results. Plans are cached for possible reuse opportunities. If a query plan is not even cached, its reuse opportunity is zero. Such a plan will be compiled every time it is executed, resulting in poor performance. In rare cases, non-caching is a desirable, and this paper will point out such cases later on.

Read more Comments (0)

Compilation, Recompilation, and Plan Caching in SQL Server 2005

Author → Asif

SQL Server will manage the objects in its cache in a few main ways: freeing up buffers or aging execution plans. Before a query, batch, stored procedure, trigger, prepared statement, or dynamic SQL statement begins execution on a SQL Server, the batch gets compiled into a plan. The plan is then executed for its effects or to produce results. Plans are cached for possible reuse opportunities. If a query plan is not even cached, its reuse opportunity is zero. Such a plan will be compiled every time it is executed, resulting in poor performance. In rare cases, non-caching is a desirable, and this paper will point out such cases later on.

Read more Comments (0)