Database Reference
In-Depth Information
Figure 26-10.
Plan cache content when Optimize for ad-hoc workload is enabled
Auto-Parameterization
In some cases, SQL Server may decide to replace some constants in ad-hoc queries with parameters and cache
compiled plans as if the queries were parameterized. When this happens, similar ad-hoc queries that use different
constants can reuse cached plans.
For example, queries in Listing 26-19 could be parameterized and share the compiled plan.
Listing 26-19.
Parameterization
select ID, Number, Name from dbo.Employees where ID = 5
go
select ID, Number, Name from dbo.Employees where ID = 10
go
Internally, SQL Server stores the compiled plan as shown below:
(@1 tinyint)SELECT [ID],[Number],[Name] FROM [dbo].[Employees] WHERE [ID]=@1
By default, SQL Server defines a parameter data type based on a constant value, choosing the smallest data type
where the value fits. For example, the query
SELECT ID, Number, Name FROM dbo.Employees WHERE ID = 10000
would introduce another cached plan, as shown here:
(@1 smallint)SELECT [ID],[Number],[Name] FROM [dbo].[Employees] WHERE [ID]=@1
When parameterization occurs, SQL Server stores another structure in the plan cache, called
Shell Query
, in
addition to the compiled plan of the parameterized query. The shell query uses about 16KB of memory and stores
information about the original query and links it to the compiled plan.
You can see the content of plan cache after we ran the queries from Listing 26-19 in Figure
26-11
. As you can see,
it stores the compiled plan and two shell queries.
