What is parameter sniffing - SQL Server

Hi,

Can you please let me know what is parameter sniffing in SQL Server ? Please provide simple examples for this to understand it better !

Thanks
milani_lucieAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
Hmm. This looks like an article.
USE tempdb
GO


/*  


   S C E N A R I O
   ===============

   1) There is a department store.

   2) It has a main table called "Sales".

   3) The department store has two categories of sales.

	A) In-House Sales		
	
	B) Other Customer Sales

   4) 50% sales are to a single customer - "In-House".

   5) All other customers visit the department store once or twice - becuase the department sells very few products.

   6) The sales table is having two indexes on columns "CustomerId" and "DateOfSold".


*/


-- Create a table


CREATE TABLE dbo.Sales
(	
	SalesID int identity primary key,
	CustomerId	uniqueidentifier,	
	ProductName	varchar(128),	
	DateOfSold	datetime,	
	Price		int,	
	SalesCategory	varchar(128)
)
GO

-- Create indexes
CREATE INDEX idx_DateOfSold ON Sales (DateOfSold)
CREATE INDEX idx_Customer ON Sales (CustomerID)
GO


-- Insert some data for "In-House"
Declare @inhouseCustomerID uniqueidentifier set @inhouseCustomerID = '54308F89-3A81-44F4-AF7A-E73C786ADCB3'
DECLARE @i INT
SET @i = 1
WHILE (@i <= 100000)
BEGIN
	INSERT INTO Sales (CustomerId, ProductName, DateOfSold, Price, SalesCategory)
	VALUES (@inhouseCustomerID, 'Vodka', dateadd(hh, -@i, getdate()), 150, 'In-House')
	SET @i = @i  + 1
END
GO

-- Insert some data for "Other Customer"
DECLARE @i INT
SET @i = 1
WHILE (@i <= 100000)
BEGIN
	INSERT INTO Sales (CustomerId, ProductName, DateOfSold, Price, SalesCategory)
	VALUES (newid(), 'Vodka', dateadd(hh, -@i, getdate()), 150, 'Retail')
	SET @i = @i  + 1
END
INSERT INTO Sales (CustomerId, ProductName, DateOfSold, Price, SalesCategory)
VALUES ('FEEDFEED-0000-44F4-AF7A-E73C786ADCB3', 'Vodka', dateadd(hh, -4, getdate()), 150, 'Retail')

-- Create a stored procedure
CREATE PROCEDURE dbo.spGetSalesRecord
@CustomerId uniqueidentifier,
@datestart datetime
AS
BEGIN
	SELECT * FROM Sales
	--WHERE CONVERT(VARCHAR(20), DateOfSold, 110) = '09-24-2010' -- don't use convert on DateOfSold. index won't be used
	WHERE DateOfSold >= @datestart
	AND CustomerId = @CustomerId
END
GO

/* SHOWPLAN_TEXT */
--SET SHOWPLAN_TEXT ON 
-- the SP needs to actually run to be accurate. Set Ctrl-M (include actual execution plan) then run the below

-- Run SP
Declare @inhouseCustomerID uniqueidentifier set @inhouseCustomerID = '54308F89-3A81-44F4-AF7A-E73C786ADCB3'
declare @datestart datetime set @datestart = getdate() - 7 -- up to 7 days ago, i.e. ~168 records
EXEC spGetSalesRecord @inhouseCustomerID, @datestart -- For "In-House"
GO
-- the plan should show usage of index "idx_DateOfSold"
-- idx_DateOfSold, index seek, 335 rows...> filter down to 167

-- Run SP
Declare @otherCustomerID uniqueidentifier select @otherCustomerID = 'FEEDFEED-0000-44F4-AF7A-E73C786ADCB3'
declare @datestart datetime set @datestart = getdate() - 7 -- up to 7 days ago, i.e. ~168 records
EXEC spGetSalesRecord @otherCustomerID, @datestart -- For "Other Customer"
GO
-- the plan should show STILL usage of index "idx_DateOfSold"
-- idx_DateOfSold, index seek, 335 rows...> filter down to 1

-- Run SP
Declare @otherCustomerID uniqueidentifier select @otherCustomerID = 'FEEDFEED-0000-44F4-AF7A-E73C786ADCB3'
declare @datestart datetime set @datestart = dateadd(year, -1, getdate()) -- up to 1 year ago, i.e. ~168 records
EXEC spGetSalesRecord @otherCustomerID, @datestart -- For "Other Customer"
GO
-- the plan should show STILL usage of index "idx_DateOfSold"
-- idx_DateOfSold, index seek, 17519 rows...> filter down to 1  !!! 17,519

-- clear the SNIFFED parameter
dbcc freeproccache

-- Run SP
Declare @otherCustomerID uniqueidentifier select @otherCustomerID = 'FEEDFEED-0000-44F4-AF7A-E73C786ADCB3'
declare @datestart datetime set @datestart = getdate() - 7 -- up to 7 days ago, i.e. ~168 records
EXEC spGetSalesRecord @otherCustomerID, @datestart -- For "Other Customer"
GO
-- the plan should show usage of index "idx_Customer"
-- idx_Customer, index seek, 1 row.

Open in new window

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Go through these in depth articles which can guide you exactly:

http://blogs.msdn.com/b/queryoptteam/archive/2006/03/31/565991.aspx
http://elegantcode.com/2008/05/17/sql-parameter-sniffing-and-what-to-do-about-it/
http://furrukhbaig.wordpress.com/2007/08/22/parameter-sniffing/

If you have any questions, kindly let me know so that I can explain it out.
0
 
mofoworkingCommented:
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
tlovieCommented:
It's summed up here better than I could word it.

http://elegantcode.com/2008/05/17/sql-parameter-sniffing-and-what-to-do-about-it/

The basic idea is that if you have a stored procedure that accepts parameters, the SQL server query optimizer *might* decide to take a look at those parameter values to influence the query plan that it chooses to use to evaluate the query.  Sometimes this could make the query run faster, or perhaps make the evaluation of which plan to use faster, however, it could also have the opposite effect and based on the parameters used, the query could run extremely slow.

I've noticed it on some of my stored procedure runs (especially after upgrading to SQL server 2008) and have generally found it to be a nuisance since my procedures generally run fine 95% of the time, but 5% of the time it takes like 1000% longer to run.
0
 
cyberkiwiCommented:
I could probably do a Google search for "parameter sniffing" and paste you some links, but you are just as well equipped to do that yourself.

I will try to give one simple example.
This is a department store database, and the main table is sales.
The department makes 2 main categories of sales, 50% of sales are to a single customer - "in-house".
On the other extreme, all other customers only visit the department once or twice, ever, because the department sells very few products, the products last forever and never have problems.

There are two indexes on the sales table -
(1) customer
(2) date

If you were to run this query

select * from sales
where date <in this week>
and customer = 123

It is going to be a lot faster to use the "customer" index.  This is because there are only 1 or 2 records for customer 123.

If you run this query

select * from sales
where date <in this week>
and customer = <id of "in-house">

It is going to be a lot faster to use the date index, because the "customer" index in this case gives you 50% the table.

Both queries work fine on their own.
Now, you create a stored procedure that takes the customer as an input.

create proc getSales @customer int as
select * from sales
where date <in this week>
and customer = @customer

Now the first query you run for the day is

exec getSales @customer=123

SQL Server looks at the query and generates a query plan.  The query plan says to collect information from table sales using the index on "Customer".  This query plan is stored in the cache for future re-use.

Some time during the day, someone runs a query

exec getSales @customer=<id of 'in-house'>

SQL Server takes out the cached plan and uses the index on "customer".  BAD IDEA.


There you have it.  Parameter sniffing is when a particular parameter sets a query/proc to a particular plan, in a way that is unsuitable for use for other usages (parameter values) of the same query/proc.  The "sniffed" parameter values from first run is royally destroying performance for another type of usage.

Google a bit and you will find some ways to solve it.
For this particular example, these two ways will do

(1) This one requires you to know the ID of the 'in-house' customer

create proc getSales @customer int as
if @customer = <in-house>
  select * from sales
  where date <in this week>
  and customer = <in-house> and 1=1    -- this just to change the query text and force a different plan
else
  select * from sales
  where date <in this week>
  and customer = @customer

(2) The slight drawback of this one is that the query needs to be compiled each time

create proc getSales @customer int
WITH RECOMPILE     -- this causes it not to use any cached plans. For really complex queries, planning is costly
as
  select * from sales
  where date <in this week>
  and customer = @customer
0
 
Mark WillsTopic AdvisorCommented:
Actually there is also a good introductory article within EE :

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_1258-Speedier-Execution-of-Stored-Procedures-in-SQL-Server.html

Also has a couple of good links down the bottom.

Parameter sniffing is not always a bad thing. It is there to help with performance in so much as a query plan captured at time of creating / compiling the stored procedure is remembered and will try to use that plan. However, if the plan is not optimal, or, the parameters not representational of the underlying database, or, th characteristics of the data has changed, then as cyberkiwi has suggested, it is time to recompile.

Anyway, please take the time to read the Article above - it was written by an "accidental DBA" who stumbled across a problem and does a reasonable job of explainign one aspect of parameter sniffing...



0
 
milani_lucieAuthor Commented:
Hi CyberKivi,
Here is the SQL i have used:

 
USE Sample
GO

-- Create a table

CREATE TABLE dbo.Sales
(
 CustomerId int IDENTITY (1, 1) primary key,
 ProductName varchar(128),
 DateOfSold datetime,
 Price  int,
 SalesCategory varchar(128)
)
GO

-- Create a index

CREATE INDEX idx_DateOfSold ON Sales (DateOfSold)
GO

-- Insert some data for "In-House"

DECLARE @i INT
SET @i = 1
WHILE (@i <= 100000)
BEGIN
 INSERT INTO Sales (ProductName, DateOfSold, Price, SalesCategory) VALUES ('Vodka', getdate(), 150, 'In-House')
 SET @i = @i  + 1
END

-- Insert some data for "Other Customer"

INSERT INTO Sales (ProductName, DateOfSold, Price, SalesCategory) VALUES ('Vodka', getdate(), 160, 'Mike')
GO
INSERT INTO Sales (ProductName, DateOfSold, Price, SalesCategory) VALUES ('Vodka', getdate(), 170, 'Milani')
GO
INSERT INTO Sales (ProductName, DateOfSold, Price, SalesCategory) VALUES ('Vodka', getdate(), 180, 'Sam')
GO
INSERT INTO Sales (ProductName, DateOfSold, Price, SalesCategory) VALUES ('Vodka', getdate(), 190, 'Lucie')
GO

-- Create a stored procedure

CREATE PROCEDURE dbo.spGetSalesPrice
@CustomerId int
AS
BEGIN
 SELECT * FROM Sales WHERE CONVERT(VARCHAR(20), DateOfSold, 110) = '09-24-2010' AND CustomerId = @CustomerId
END
GO

-- Run SP

EXEC spGetSalesPrice 99999  -- For "In-House"
GO

-- Run SP

EXEC spGetSalesPrice 100003  -- For "Other Customer"
GO

/*  
   Parameter Sniffing
*/

CREATE PROCEDURE dbo.spParamSniff
@CustomerId int
AS
BEGIN
 DECLARE @_CustomerId int  
 SET @_CustomerId = @CustomerId  
 SELECT * FROM Sales WHERE CONVERT(VARCHAR(20), DateOfSold, 110) = '09-24-2010' AND CustomerId = @_CustomerId
END
GO

-- Run SP

EXEC spParamSniff 99999  -- For "In-House"
GO

-- Run SP

EXEC spParamSniff 100003  -- For "Other Customer"
GO
 Can you please let me know how i can test "Parameter Sniffing" ?
Thanks
0
 
milani_lucieAuthor Commented:
Hi All -
Here is the code i am having. Please let me know how can i test the "Parameter Sniffing" !
Thanks
 

SQL.sql
0
 
Mark WillsTopic AdvisorCommented:
Hi,

Thats now getting into a different question... Suggest you read an "oldie" but a "goodie" : http://technet.microsoft.com/en-us/library/cc966425.aspx

and : http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html walks through a very similar scenario as yours but with some additional comments about the data and why it is happening.

Also, be aware that there are some new developments with parameter sniffing in the latest editions of SQL : http://support.microsoft.com/kb/980653/
0
 
milani_lucieAuthor Commented:
cyberkiwi:

Excellent ! Now i have got the idea. Appreciate your help (For giving sample code).
0
 
Mark WillsTopic AdvisorCommented:
And then have a look at the same processes, but make this simple change to the stored procedure :


alter PROCEDURE dbo.spGetSalesRecord
@CustomerId uniqueidentifier,
@datestart datetime
AS
BEGIN

declare @c uniqueidentifier
declare @d datetime

set @c = @customerid
set @d = @datestart

      SELECT * FROM Sales
      --WHERE CONVERT(VARCHAR(20), DateOfSold, 110) = '09-24-2010' -- don't use convert on DateOfSold. index won't be used
      WHERE DateOfSold >= @d
      AND CustomerId = @C
END
GO
0
 
milani_lucieAuthor Commented:
mark_wills:
Thank you very much. I did that and tested, it is taking "idx_CustomerId" index instead of "idx_DateOfSold".
Thanks
 
 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.