Parameterization Part 1: Plan Caching

Many times, when I perform query tuning, the problem that causes the query to perform badly is related, one way or another, to parameterization.

On one hand, parameterization is a fundamental subject in the heart of the query processor. It can affect query performance dramatically. On the other hand, it seems to me that this important subject is not covered enough and not understood well enough.

So I decided to start a series about parameterization. This is the first post in the series, and in this post I would like to write about plan caching. Understanding how execution plans are cached is essential in order to understand parameterization.

So please fasten your seat belt. Here we go…

at-1

SQL Server reserves a certain amount of memory for the plan cache. This is where execution plans (and a few other structures) are cached for future reuse. One thing that sometimes causes confusion in this regard is the distinction between a query (or statement) and a batch. The former is a single command that the query performs as an atomic unit, one at a time. The latter is a unit of execution, which can include any number of statements. SQL Server always works with batches. This is the unit that gets parsed, simplified, optimized, compiled, and finally – executed. This is the unit for which the optimizer generates an execution plan. So the plan cache stores execution plans – each one representing a batch.

You can view the contents of the plan cache by using the sys.dm_exec_cached_plans DMV. If you want to view also the batch text and the plan XML, then you can use the following query:

Transact-SQL

SELECT BatchText = BatchTexts.text , QueryPlan = BatchPlans.query_plan , ExecutionCount = CachedPlans.usecounts , ObjectType = CachedPlans.objtype , Size_KB = CachedPlans.size_in_bytes / 1024 FROM sys.dm_exec_cached_plans AS CachedPlans CROSS APPLY sys.dm_exec_query_plan (plan_handle) AS BatchPlans CROSS APPLY sys.dm_exec_sql_text (plan_handle) AS BatchTexts;

1

2

3

4

5

6

7

8

9

10

11

12

SELECT

BatchText = BatchTexts.text ,

QueryPlan = BatchPlans.query_plan ,

ExecutionCount = CachedPlans.usecounts ,

ObjectType = CachedPlans.objtype ,

Size_KB = CachedPlans.size_in_bytes / 1024

FROM

sys.dm_exec_cached_plans AS CachedPlans

CROSS APPLY

sys.dm_exec_query_plan (plan_handle) AS BatchPlans