Parameter Sniffing In Action

Question

Parameter Sniffing In Action

Nisarg Upadhyay

SSC-Addicted

Points: 460
More actions
October 23, 2017 at 9:06 pm

#340243

Comments posted to this topic are about the item Parameter Sniffing In Action

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

I like short "spackle" articles like this. Good job.

My only suggestion is that people like to run the code you've included in your article. Instead of using some canned database that a lot of people won't download, provide some code to generate the test data. It's pretty easy to quickly build a million row test table nowadays.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Nisarg Upadhyay SSC-Addicted Points: 460 More actions · Answer 2

Jeff Moden - Monday, October 23, 2017 10:01 PM
I like short "spackle" articles like this. Good job.
My only suggestion is that people like to run the code you've included in your article. Instead of using some canned database that a lot of people won't download, provide some code to generate the test data. It's pretty easy to quickly build a million row test table nowadays.

Thanks for feedback Jeff. I do agree with your suggestion.

steve.powell1 SSC Enthusiast Points: 104 More actions · Answer 3

The problem with Parameter Sniffing occurs where the execution plan is optimized for small data as a result (here SQL can be quite lazy) and you mostly want big data, where a good execution plan is better.

we had an instance of this a few months ago - execution plans were refreshed and the first parameter passed returned a few rows only when normally there are quite a few more rows expected. End result: The SP ran like a three legged dog dragging a Challenger tank when normally it ran like a greyhound. Took a while to track down why and to fix the problem so we wouldn't have this again. However, the recommendation is to let SQL sort out the Execution plan and that includes Parameter Sniffing - only 'fix' the sniffing if it is actually causing a problem. And to know that you need to do regular health checks.

carjara SSC Veteran Points: 200 More actions · Answer 4

Hi, how can I prevent this behavior?
Is it "recompile" option the right way?

Thank you in advance.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

carjara - Tuesday, October 24, 2017 8:48 AM
Hi, how can I prevent this behavior?
Is it "recompile" option the right way?
Thank you in advance.

It depends.

First, and as Nisarg mentioned at the end of the article, parameter sniffing is not always a bad thing. In fact, sometimes lack of parameter sniffing is a bad thing. Parameter sniffing is bad when the optimizer is choosing a terrible execution plan due to it.

I have found it's a good thing to always recompile store procedures that do ETL work and run on a schedule. I would not do it, however, on say - an proc that runs hundreds/thousands of times an hour.

All that said, I've always learned a lot about this topic from Grant Fritchey and Gail Shaw. Here's a couple good articles

https://www.scarydba.com/2016/12/12/optimize-hints-parameter-sniffing-turned-off
https://www.sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/

This article (with perhaps the most uninspiring title ever) is also good: https://www.brentozar.com/archive/2013/06/optimize-for-unknown-sql-server-parameter-sniffing/

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

carjara SSC Veteran Points: 200 More actions · Answer 6

Alan.B - Tuesday, October 24, 2017 11:02 AM
carjara - Tuesday, October 24, 2017 8:48 AM
Hi, how can I prevent this behavior?
Is it "recompile" option the right way?
Thank you in advance.
It depends.
First, and as Nisarg mentioned at the end of the article, parameter sniffing is not always a bad thing. In fact, sometimes lack of parameter sniffing is a bad thing. Parameter sniffing is bad when the optimizer is choosing a terrible execution plan due to it.
I have found it's a good thing to always recompile store procedures that do ETL work and run on a schedule. I would not do it, however, on say - an proc that runs hundreds/thousands of times an hour.
All that said, I've always learned a lot about this topic from Grant Fritchey and Gail Shaw. Here's a couple good articles
https://www.scarydba.com/2016/12/12/optimize-hints-parameter-sniffing-turned-off
https://www.sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
This article (with perhaps the most uninspiring title ever) is also good: https://www.brentozar.com/archive/2013/06/optimize-for-unknown-sql-server-parameter-sniffing/

Alan, thank you a lot!

Grey Cat Mr or Mrs. 500 Points: 524 More actions · Answer 7

"with recompile" is one way to avoid parameter sniffing.
I think another way is using dynamic queries in SPs. This will cause extra plans in plan cache. So make sure "optimize for Ad hoc Workloads" are turned on.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

Grey Cat - Wednesday, October 25, 2017 5:29 PM
"with recompile" is one way to avoid parameter sniffing.
I think another way is using dynamic queries in SPs. This will cause extra plans in plan cache. So make sure "optimize for Ad hoc Workloads" are turned on.

For large batch jobs, RECOMPILEs aren't so bad. Using "optimize for Ad hoc Workloads" actually masks the terrible problem of multiple renditions of the same code being just far enough off to require a separate and sometimes very expensive recompile of code coming from poorly formed embedded SQL, SQL generated from an ORM, and dynamic SQL. It's a real bitch when front end code (regardless of method) "only" takes 100ms to run but takes 2-22 seconds to execute and it's execute thousands of times per hour. Most folks never find that type of problem and just chalk it up to the "server being slow" when it's actually the fault of the code and the recompiles that are taking place.

To wit, "with recompile" should be used very, very sparingly and with great caution and understanding. It is NOT a panacea to avoid parameter sniffing and should only be used with the greatest of care.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grey Cat Mr or Mrs. 500 Points: 524 More actions · Answer 9

steve.powell1 - Tuesday, October 24, 2017 2:02 AM
The problem with Parameter Sniffing occurs where the execution plan is optimized for small data as a result (here SQL can be quite lazy) and you mostly want big data, where a good execution plan is better.
we had an instance of this a few months ago - execution plans were refreshed and the first parameter passed returned a few rows only when normally there are quite a few more rows expected. End result: The SP ran like a three legged dog dragging a Challenger tank when normally it ran like a greyhound. Took a while to track down why and to fix the problem so we wouldn't have this again. However, the recommendation is to let SQL sort out the Execution plan and that includes Parameter Sniffing - only 'fix' the sniffing if it is actually causing a problem. And to know that you need to do regular health checks.

We are talking about extreme situations. First, parameter sniffing is good in most cases (that's why it is implemented). But this makes things worse in some cases. This is why we need to find a way to avoid sniffing. Any way, Kimberly Tripp has very good sessions, articles on this topic.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

Grey Cat - Thursday, October 26, 2017 4:03 PM
steve.powell1 - Tuesday, October 24, 2017 2:02 AM
The problem with Parameter Sniffing occurs where the execution plan is optimized for small data as a result (here SQL can be quite lazy) and you mostly want big data, where a good execution plan is better.
we had an instance of this a few months ago - execution plans were refreshed and the first parameter passed returned a few rows only when normally there are quite a few more rows expected. End result: The SP ran like a three legged dog dragging a Challenger tank when normally it ran like a greyhound. Took a while to track down why and to fix the problem so we wouldn't have this again. However, the recommendation is to let SQL sort out the Execution plan and that includes Parameter Sniffing - only 'fix' the sniffing if it is actually causing a problem. And to know that you need to do regular health checks.
We are talking about extreme situations. First, parameter sniffing is good in most cases (that's why it is implemented). But this makes things worse in some cases. This is why we need to find a way to avoid sniffing. Any way, Kimberly Tripp has very good sessions, articles on this topic.

Got any links to share?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 11

Jeff Moden - Thursday, October 26, 2017 9:51 PM
Grey Cat - Thursday, October 26, 2017 4:03 PM
steve.powell1 - Tuesday, October 24, 2017 2:02 AM
The problem with Parameter Sniffing occurs where the execution plan is optimized for small data as a result (here SQL can be quite lazy) and you mostly want big data, where a good execution plan is better.
we had an instance of this a few months ago - execution plans were refreshed and the first parameter passed returned a few rows only when normally there are quite a few more rows expected. End result: The SP ran like a three legged dog dragging a Challenger tank when normally it ran like a greyhound. Took a while to track down why and to fix the problem so we wouldn't have this again. However, the recommendation is to let SQL sort out the Execution plan and that includes Parameter Sniffing - only 'fix' the sniffing if it is actually causing a problem. And to know that you need to do regular health checks.
We are talking about extreme situations. First, parameter sniffing is good in most cases (that's why it is implemented). But this makes things worse in some cases. This is why we need to find a way to avoid sniffing. Any way, Kimberly Tripp has very good sessions, articles on this topic.
Got any links to share?

Building High Performance Stored Procedures

Stored Procedure Optimization with Kimberly Tripp - Alaska SQL User Group