Microsoft SQL Server 2012 Bible

Chapter 46

Maximizing Query Plan Reuse

In This Chapter

Compiling SQL

Using the Query Plan Cache

The SQL language is a declarative language, meaning that the SQL statement describes the wanted result but does not specifically 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 fluctuate, so generating 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 executing 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 first 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.