Batch compilation, recompile, and schedule caching issues in SQL Server 2005 (1)

This article describes how batches are cached and reused in SQL Server 2005, and provides the best practice for maximizing the reuse of cached plans. In addition, some scenarios for recompiling batches are described, and best practices are given for reducing or eliminating unnecessary recompilation.

I. Purpose of this white paper

There are several purposes for this white paper. Describes how batches are cached and reused in SQL Server 2005, and provides the best practice for maximizing the reuse of cached plans. In addition, some scenarios for recompiling batches are described, and best practices are given for reducing or eliminating unnecessary recompilation. This white paper describes the statement-level recompilation feature of SQL Server 2005. In addition, there are a number of tools and utilities that can serve as useful observational tools in query compilation, query recompilation, plan caching, and planning reuse. We compared the different manifestations of SQL Server 2000 and SQL Server 2005 throughout this article so that readers can better understand what is in the text. The statements listed in this document apply to SQL Server 2000 and SQL Server 2005. At the same time, the behavioral differences between these two versions of SQL Server are clearly identified.

This paper is aimed at three types of readers:

User: A person who uses, maintains, and develops applications for SQL Server. Users who first read SQL Server 2005 and those who are migrating from SQL Server 2000 will find useful information here.

Developer: SQL Server developers will find useful background information here.

Testers and Project managers: This document provides a description of the compile and recompile features in SQL Server 2005.

Second, recompile: definition

Batches are compiled into a schedule before queries, batches, stored procedures, triggers, prepared statements, or dynamic SQL statements (hereinafter referred to as "batch") begin execution on SQL Server. The plan is then executed to perform its effect or generate results.