Parameter Caching: Parameter Sniffing with Adhoc Select Query

Read Previous Post: Plan Caching VI - Forced Parameterization in Adhoc Queries

Here we will see, how a query makes a difference in Execution plan when it has Parameter passed in where predicate. Whenever you are firing any query the plan is generated and Cached in Memory, but it doest not store the context of the execution i.e (plan based on Parameter passed).

Sometime when we use parameter to pass in where condition, the actual value of parameter is not known at compile time due to which the optimal Execution plan is not selected by SQL Optimizer, this is because query is compiled before actual value of parameter is known.

Lets check out with a small practical

create table ParameterSniffingDemoTB

(id int identity(1,1) primary key,

col2 datetime,

col3 numeric(28,10) not null)  

Command(s) completed successfully.

Lets insert some rows in the table

declare @i int = 0

begin tran

while @i < 50000

begin  insert into ParameterSniffingDemo_TB(col2,col3)

     select '20140616', 1000*rand()

     set @i=@i+1

end  

set @i=0

while @i < 5

begin

     insert into ParameterSniffingDemo_TB(col2,col3)

     select '20140617', 1000*rand()

     set @i=@i+1

end

commit tran 

Now since our table is populated with data, Lets create a non clustered index on col2

create nonclustered index [ix_col2]

on [ParameterSniffingDemo_TB] ([col2] asc)

Command(s) completed successfully.

Non Clustered Index is on place now. Lets query on the basis of col2 and see whether Non Clustered index participate in Execution plan.

select SUM(col3) FROM ParameterSniffingDemo_TB WHERE col2='20140617'

Here we can see Non cluster Index participated in the execution plan.

Now lets declare a parameter variable and check whether it will use the existing Non Clustered index.

declare @mydate datetime = '20140617'

select sum(col3) FROM ParameterSniffingDemo_TB WHERE col2=@mydate

Here we can see the Non clustered index is not taken up by Optimizer while creating Plan for the Query.

Lets check the comparative cost of the query, for this we are running both query in a batch

--First Query with out Parameter Variable

select sum(col3) FROM ParameterSniffingDemo_TB WHERE col2='20140617'

--Second Query with local Parameter Variable

declare @mydate datetime = '20140617'

select sum(col3) FROM ParameterSniffingDemo_TB WHERE col2=@mydate  --option(recompile)

Here we can see the Cost of query with value as parameter choose optimal plan with just 6% of total to execute whereas Query with Parameter variable choose non optimal plan.  

This problem is called Parameter Sniffing.

Workaround Solution :- Option (Recompile) Hint

declare @mydate datetime = '20140617'

select sum(col3) FROM ParameterSniffingDemo_TB WHERE col2=@mydate  option(recompile)

It’s your smile which makes me smile, let me smile and do my duty towards my code, Keep smiling my dear! J

 

Post Reference: Vikram Aristocratic Elfin Share

NONCLUSTERED Index on Table Variable in SQL Server 2014

There are always advantages of using table variable when working with small dataset. But the only flaw with Table Variable was its lack of ability to create NON Clustered index on it but  SQL Server 2014  has given support to create NONCLUSTERED index on table variable.

In SQL Server 2008 R2

Lets try to create NC on @table variable column.

declare @tab_variable table

     ( id int identity(1,1) primary key,

     f_name varchar(10) index idx_f_name,

     l_name varchar(10))

Msg 1018, Level 15, State 1, Line 4

Incorrect syntax near 'index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

We saw SQL Server 2008 R2 has no support for NC on table variable.

In SQL Server 2014

Now let’s see how we can create non clustered index on table variable on SQL Server 2014

declare @tab_variable table

       ( id int identity(1,1) primary key,

       f_name varchar(10) index idx_f_name,

       l_name varchar(10))

insert into @tab_variable(f_name,l_name)

select top 1000000 SUBSTRING(name,0,9), SUBSTRING(name,0,9) from  sys.columns

select f_name, l_name from @tab_variable

where f_name like 'TSQL'

f_name     l_name

---------- ----------

TSQL       TSQL

(1 row(s) affected)

So here we say that Non Clustered index, we can create on @table variable column, it is an awesome feature added to SQL Server 2014, the gap of temp variable and table variable reduced to just scope difference J

Your code has potential to bow every head.  

 

Post Reference: Vikram Aristocratic Elfin Share

Plan Caching VI - Forced Parameterization in Adhoc Queries

Read Previous Post:

• Plan Caching I - Adhoc Query Caching (SQL Server 2008)
• Plan Caching II - Careful consideration needed, if you want to use cached Adhoc Query Plan
• Plan Caching III - Compiled Plan Stub in Adhoc Query caching with _optimize for ad hoc workloads_ set to 1
• Plan Caching IV - Simple Parameterization in Adhoc Query Processing
• Plan Caching V - Disadvantage of Simple Parameterization

In the last article we saw that the datatype of constant is selected on fly depending upon the constant value by SQL Server, due to which the cached Prepared Parameterized plan is not been used by a similar query with constant little different.

Just check this, below example.  

dbcc FREEPROCCACHE

DBCC DROPCLEANBUFFERS

Go

select * from emp where emp_id = 1

go

Then executing this sys.dm_exec_cached_plans query

select usecounts, cacheobjtype, objtype, [text]

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

where usecounts > 0

AND

[text] not like '%dm_exec_cached_plans%' and [text] not like  '%dm_exec_sql_text%'

order by usecounts desc

go

usecounts cacheobjtype  objtype  text

--------  ------------- -------- --------------------------------------------

1         Compiled Plan Prepared (@1 tinyint)SELECT * FROM emp WHERE emp_id=@1

1         Compiled Plan Adhoc    select * from emp where emp_id = 1

From the output we can see that the constant used in query where predict is being treated as tinyint. So the datatype is of the constant is being selected on fly by SQL Server while making a prepared Parameterized cache plan.

Now let’s query the same select with contact value ranging out of tiny data type.

select * from emp where emp_id = 700

go

 

Then executing this sys.dm_exec_cached_plans query

select usecounts, cacheobjtype, objtype, [text]

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

where usecounts > 0

AND

[text] not like '%dm_exec_cached_plans%' and [text] not like  '%dm_exec_sql_text%'

order by usecounts desc

go

usecounts cacheobjtype  objtype  text

--------  ------------- -------- --------------------------------------------

1         Compiled Plan Prepared (@1 tinyint)SELECT * FROM emp WHERE emp_id=@1

1         Compiled Plan Adhoc    select * from emp where emp_id = 1

1         Compiled Plan Prepared (@1 smallint)SELECT * FROM emp WHERE emp_id=@1

1         Compiled Plan Adhoc    select * from emp where emp_id = 700

Here we can see from the output that it has not used the Prepared plan which gets generated when we executed query with predicate constant value as 1 and generate a new prepared plan and cached in memory; this time the constant is replaced by parameter and the type for the parameter is choosed as smallInt unlike tinyint in previous case.

So we get two prepared queries plan with two different parameter data type. So SQL Server is not making use of existing Parameterized cached plan.

The only way to force SQL Server to use the same data type  for both the queries is to enable PARAMETERIZED FORCED for the database.

dbcc FREEPROCCACHE

DBCC DROPCLEANBUFFERS

Go

alter database sparsh set parameterization forced;

go

Here we have set PARAMETERIZATION FORCED enabled. Once this is done SQL Server treats every kind constants as just a set of parameters.

Now since we have enabled PARAMETERIZATION FORCED. Lets run both the select query and query the dm_exec_cached_plans to check the output.

select * from emp where emp_id = 1

go

select * from emp where emp_id = 700

go

select usecounts, cacheobjtype, objtype, [text]

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

where usecounts > 0

AND

[text] not like '%dm_exec_cached_plans%' and [text] not like  '%dm_exec_sql_text%'

order by usecounts desc

go   

usecounts cacheobjtype  objtype  text

--------  ------------- -------- --------------------------------------------

2         Compiled Plan Prepared (@0 int)SELECT * FROM emp WHERE emp_id=@0

1         Compiled Plan Adhoc    select * from emp where emp_id = 1

1         Compiled Plan Adhoc    select * from emp where emp_id = 700

Here we can see that no new Prepared plan gets generated, instead the same prepared plan is referred by the second query with emp_id = 700. And the data type chosen by SQL Server for constant is INT which satisfy both the constant.

Though you need to be careful while setting this “parameterization forced” since setting this option “on” for the database, you are letting SQL Server assume and treat all constant as a same parameter, which may give poor performance. Alternatives are available, which I will soon post as an article.

If my girl friend judge my love to code is like putting them in a strait jacket and kicking them down a flight, then yes.. I have deep love to my code K   

Post Reference: Vikram Aristocratic Elfin Share