Microsoft® SQL Server® 2012 Bible

1041

CHAPTER

46

Maximizing Query Plan Reuse

IN THIS CHAPTER

Compiling SQL

Using the Query Plan Cache

T

he SQL language is a declarative language, meaning that the SQL statement describes the

wanted result but does not specifi cally address how to best solve the query.

Often the best solution is to consider the available indexes and statistics on the objects involved

in the query. The indexes, data distribution, and parameter values are likely to fl uctuate, so gen-

erating a query execution plan (plan compilation) when you declare the statement doesn’t make

sense.

Also, the process to generate an execution plan can be expensive, sometimes more so than execut-

ing the statement, so it doesn’t often make sense to generate the execution plan every time you

execute the statement.

As a compromise, SQL Server generates an execution plan the fi rst time the statement executes and

then stores that execution plan in a portion of memory known as the Plan Cache. The next time the

same statement needs to execute, SQL Server attempts to use the cached execution plan instead of

generating a new plan.

Query Compiling

As previously noted, when SQL Server compiles a statement, it stores the execution plan in the

Plan Cache, which stays there, ready to be used again, as long as the plan is useful and there is no

memory pressure.

The Query Optimizer

The Query Optimizer is a component of the relational engine that creates execution plans. Think

of the Query Optimizer as the brain of the engine because it is responsible for making your queries

run as quickly and effi ciently as possible. There are several components and stages to compiling

a query.

c46.indd 1041c46.indd 1041 7/31/2012 10:22:56 AM7/31/2012 10:22:56 AM

http://www.it-ebooks.info