Professional Microsoft® SQL Server® 2008 Administration

14.1. Physical Query Processing

SQL Server performs two main steps to produce the desired result when a query is fired. As you would guess, the first step is query compilation, which generates the query plan; and the second step is the execution of the query plan. The compilation phase in SQL Server 2008 goes through three steps: parsing, algebrization, and optimization. In SQL Server 2000, there was a normalization phase, which was replaced with the algebrization piece in SQL Server 2005. The SQL Server team has spent much effort to re-architect and rewrite several parts of SQL Server. Of course, the goal is to redesign logic to serve current and future expansions of SQL Server functionality. Having said that, after the three steps just mentioned are completed, the compiler stores the optimized query plan in the plan cache. The execution engine takes over after that; it copies the plan into its executable form and, of course, executes the steps in the query plan to produce the desired result. If the same query or stored procedure is executed again, the compilation phase is skipped and the execution engine uses the same cached plan to start the execution.

You can see this in action with an example. The goal is to determine whether the stored procedure plan is reused or not. The example also uses SQL Profiler and the dynamic management view (DMV) sys.dm_exec_cached_plans to examine some interesting details. In order to determine whether a compiled plan is reused or not, you have ...