Solved

What is parameter sniffing - SQL Server

Posted on 2010-09-21
12
823 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:milani_lucie
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33731061
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
 
LVL 2

Expert Comment

by:mofoworking
ID: 33731075
0
 
LVL 7

Expert Comment

by:tlovie
ID: 33731097
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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33731120
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33743793
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
 

Author Comment

by:milani_lucie
ID: 33758731
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:milani_lucie
ID: 33758756
Hi All -
Here is the code i am having. Please let me know how can i test the "Parameter Sniffing" !
Thanks
 

SQL.sql
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33758992
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
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33759359
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
 

Author Closing Comment

by:milani_lucie
ID: 33816003
cyberkiwi:

Excellent ! Now i have got the idea. Appreciate your help (For giving sample code).
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33816379
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
 

Author Comment

by:milani_lucie
ID: 33817856
mark_wills:
Thank you very much. I did that and tested, it is taking "idx_CustomerId" index instead of "idx_DateOfSold".
Thanks
 
 
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now