Microsoft® SQL Server® 2008 Bible

Chapter 65. Query Plan Reuse

IN THIS CHAPTER

• Compiling SQL

• Working with the query Plan Cache

CSC 101 taught that programming languages are either compiled or interpreted. SQL is neither.

A SQL query is declarative, meaning that the SQL statement describes the question but does not specifically code how to best solve the problem.

The best solution has to consider the available indexes and the mix of the data compared to the query parameters, and the hardware constraints. The indexes, data mix, and parameters are bound to fluctuate, so generating a query execution plan (also called compiling the SQL query) when the query is created would be foolish.

On the other hand, the process of generating a query execution plan can be expensive, sometimes more expensive than performing the query, so it doesn't make sense to generate the query execution plan every time the query is executed.

As a compromise, SQL Server generates a query execution plan the first time the query is executed and then stores that query execution plan in memory. The next time the same query is executed, SQL Server uses the stored query execution plan instead of generating a new plan.

Query Compiling

When SQL Server compiles a query, it's stored in the Plan Cache, a portion of memory reserved for query plans, where it stays for a while, ready to be used again. SQL Server compiles both SQL queries and stored procedures into query execution plans.

The Query Optimizer

The technology that compiles a SQL query is commonly called the ...