Compilation

The query plan is the optimal data access path used to execute the procedure. SQL Server determines the optimal data access path and builds the query plan based on:

• The SQL statements stored in the query tree
• The statistics for each table and index referenced in the procedure
• The values of any parameters passed to the procedure when first executed

Because query plans are held only in the procedure cache (and not on disk), they must be rebuilt each time SQL Server starts. The process of building a query plan is called compilation.

When a stored procedure is executed the first time, the query tree for the procedure is read from the sysprocedures table and loaded into the procedure cache. Then a query plan is created and loaded into the procedure cache. For more information about query plans, see Chapter 23, "Understanding SHOWPLAN Output."