Database Reference
In-Depth Information
■
always specify the schema when you reference tables and stored procedures. it reduces the size of plan
cache and speeds up the compilation process.
Important
Finally, SQL Server does not cache plans if the batch or object includes string literals greater than 8KB in size.
For example, the plan for the following query is not going to be cached when a constant used in the
where
clause has
more than 8,192 characters:
SELECT * FROM Table1 WHERE Col='<insert more than 8,192 characters here>'
We will dive deeper into the plan cache internals later in this chapter after discussing a few practical questions
related to plan caching.
Parameter Sniffing
Plan caching can significantly reduce CPU load on systems by eliminating unnecessary query compilations. However,
it also introduces a few problems. The most widely known problem is called
parameter sniffing
. SQL Server
sniffs
parameter values at the time of optimization and generates and caches a plan that is optimal for those values. Nothing
is wrong with this behavior. However, in some cases, when data is unevenly distributed, it leads to a situation when
the generated and
cached
plan is optimal only for atypical, rarely used parameter values. Those cached plans could be
suboptimal for further calls that are using more common values as parameters.
Most database professionals have experienced a situation when customers are suddenly complaining about
system response time. Some queries or stored procedures took a much longer time to complete, even though there
were no recent deployments to production. In most cases, these situations happened due to parameter sniffing when
queries were recompiled due to a statistics update.
Let's look at an example and create the table shown in Listing 26-1. We will populate it with data in such a way
that most rows have
Country
value set to 'USA. Finally, we will create a nonclustered index on the
Country
column.
Listing 26-1.
Parameter Sniffing: Table Creation
create table dbo.Employees
(
ID int not null,
Number varchar(32) not null,
Name varchar(100) not null,
Salary money not null,
Country varchar(64) not null,
constraint PK_Employees
primary key clustered(ID)
);
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 cross join N4 as T2 ) -- 65,536 rows
,Nums(Num) as (select row_number() over (order by (select null)) from N5)
insert into dbo.Employees(ID, Number, Name, Salary, Country)
