Appendix B: Stored Procedure Compilation, Storage, and Reuse

Transact-SQL is not a standard programming language, nor is Microsoft SQL Server a standard environment for program execution. However, the process of compiling the source code for a stored procedure and its execution bear some resemblance to the compilation and execution of programs in standard programming languages.

The Compilation and Execution Process

When a developer executes any batch of T-SQL statements, SQL Server performs the following four steps:

1. Parse the batch.

2. Get statistics.

3. Compile the batch.

4. Execute the batch.

Parsing

Parsing is a process during which the Microsoft SQL Server's Command Parser module first verifies the syntax of a batch. If no errors are found, the Command Parser breaks the source code into logical units such as keywords, identifiers, and operators. The parser then builds an internal structure that describes the series of steps needed to perform the requested operation or to extract the requested result set from the source data. If the batch contains a query, this internal structure is called a query tree, and if the batch contains a procedure, it is called a sequence tree.

Get Statistics

Statistics are database objects that describe distribution of data in indexes and columns. Based on them, the engine knows how useful the index for processing of a statement is. By default, databases are set in Auto Create Statistics and Auto Update Statistics modes, and SQL Server automatically maintains them as records that are added, deleted, or modified in tables.

At this point in the process, SQL Server will load all relevant statistics into memory. If some of the statistics are not up-to-date, SQL Server will initiate a process to updating them. One major difference between SQL Server 2005 and the earlier versions is that statistics can now be updated asynchronously. The compilation will not be blocked by statistics updates. SQL Server can decide to continue the process using "stale" statistics.

Compilation

In this step, a sequence tree is used to generate an execution plan. The optimizer module analyzes the ways that information can be retrieved from the source tables. It attempts to find the fastest way that uses the smallest amount of resources (that is, processing time, IDs and memory). It also complements the list of tasks that need to be performed (for instance, it checks security, it verifies that constraints are enforced, and it includes triggers if they need to be incorporated in processing). The result is an internal structure called an execution plan (or query plan).

Execution

The execution plan is then stored in the procedure cache or plan cache, from which it is executed, and then associated with execution context—a set of parameters used in execution plans. Different steps in the execution plan will be posted to different modules of the relational engine to be executed: DML manager, DDL manager, stored procedure manager, transaction manager, or utility manager. Results are collected in the form of a result set and sent to the caller.