IN THIS CHAPTER
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.
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 technology that compiles a SQL query is commonly called the ...