Identifying and Fixing Parameter Sniffing Issues

Brent's live session at SQLDay Poland 2017. You'll learn 4 things: what parameter sniffing is, how to react to parameter sniffing, how NOT to test your code, and options to fix the problem long term.

[@DivakarRaj]

This session is gold. Absolute pure gold !! Pure class from Brent

[@BrentOzarUnlimited]

Thanks, glad you liked it!

[@mtsurov]

I watched this several times and endlessly thankful that this material exists. Thank you Brent ! (p.s. Your humor is not lost on me either).

[@kenigiri]

I think this is the first time I understood this Parameter Sniffing concept to this level. Very clear explanation and very powerful tool sp_Blitz

[@marancibia1971]

What a great session! A lot of practical knowledge. Thanks!

[@themortu]

amazing presenter, the best i've seen, thank you so much for telling us serious things in this funny and easy way

[@krneki6954]

brent ozar never fails! great videos

[@chiragbhatt2244]

Very deep, clear session. Great presentation. Would definitely like to polish my skills in sql and may be opt for paid training at some point. What I liked most was the practical scenarios you explained. Awesome work. Thanks !!

[@ganeshjraj]

What a legend!! Absolutely loved the session..

[@datanalan5215]

You are special!!! Thanks for the great video!!!

[@rvijayteja]

How the heck did I never know temp stored procedures even existed. Great session

[@parnet]

This is such a good session and really shows the issues succinctly.

[@BrentOzarUnlimited]

Thanks!

[@bluerays5384]

Amazing Session Brent...A lot of learning today.Mindblowing. looking for more sessions.

[@andreabruschetta8791]

Interesting demo about parameter sniffing, and ... funny "anchor-man". I liked it.

[@BrentOzarUnlimited]

Glad you enjoyed it!

[@neoritch]

you are such an amazing presenter!

[@RodrigoMatela]

I wish I got teachers like you! Absolute legend! Cheers

[@BrentOzarUnlimited]

Thanks!

[@monday6740]

Brent is such a good presenter ! 47:10 The compiler could obviously ignore ALL comment when parsing the statement for use of query parsing, so that you can actually put comments into a statement. Remember: developers are instructed to put "useful" info in there. Same with upper and lower reserved words, they should be treated the same. Other things have similar issues, but this is part of the compiler's job; it should rebuild the statement to a uniform format - similar to like when you read in into the document, where it states the default method of using reserved words and such.

[@AsailingDIYlifechannel]

Not often I find anything I can use in my job on KZbin as an ERP developer. But wow this was extremely helpful. I spent all night last night figuring out a way to get around this exact problem and have now put a "patch" on it using a Plan Guide adding a index hint. Unfortunately the stats is weird and makes sometimes a very strange plan on a table with 200.000.000+ records. It will make it run for almost ever. When I add the hint it's done in 0 seconds. Any thoughts on Plan Guides? We are currently on a SQL Server 2012.

[@eugenechen8240]

HI Brent, if you do a select into temp table rather than get all the result into display in SSMS, then the execution time will be better distinguished between bad and good plans.

[@ScottLucas58]

Thank you! Did not know I was wasting my time with the local variable testing.

[@alllle81]

impressive presentation...

[@alrivera3358]

Learned a lot. thanks Brent

[@poojajoshi8551]

Very nicely explained. Very helpful 👍

[@BrentOzarUnlimited]

Glad it was helpful!

[@chihanglui222]

It is a veey clear explaination and your sp is very useful

[@marcosdiez7263]

Great explanation. I wonder whether another alternative would be possible to the OPTION FOR VALUE, to minimize the implied future maintenance overload of the procedure, which is to have a procedure to be called at server startup or when updating stats that in turn makes a call to the problematic store procedures with the desired VALUE. The drawback would be that at those times there would be a redundant and functionally unneeded load to the server, but then each bussiness logic store procedure would be devoted solely to the bussiness logic aspect as supposed to, and this startup called procedure would gather all the logic devoted to the parameter sniffing problem as a technical database proprietary aspect (or even better, if there exist a chance, to cache the SP plan without executing the actual queries, but I don't know how). Then, in the worst case in which data distribution changes, you would have the parameter sniffing issue triggered again but once detected, you need a single modifictation to the startup procedure to get the issue solved (moreover, DBA would be able to modify the startup procedure without altering bussiness logic procedures that may be critical for the customer which could be prone to introduce undesired side effects, not to mention a concurrent modification in which the programmer dealing with the bussiness logic could reintroduce the value you just corrected).

[@BrentOzarUnlimited]

We'd love to watch you do a presentation on KZbin about how you solved that problem in that way.

[@koushikdey100]

U r smiply superb Brent, watched 👀 this video 🎥 and liked it very much.Thanks🌹

[@dinhba6859]

Tks Brent. Love this.

[@BrentOzarUnlimited]

Glad you enjoyed it!

[@rliy001]

Hi, Question. You mentioned in the presentation that SQL remembers how much memory it needs to execute the proc when its first compiled; but I thought you also mentioned that sql determines there's "lots of work" or "small amount of work" involved when it first executes [by sniffing the params that comes in]... So which is correct?

[@BrentOzarUnlimited]

Ruch - it only sniffs the *first* set of parameters, not all of them.

[@rliy001]

Hi Brent. You mentioned SQL tipping point for "screw-you, I'm going to to scan the entire table" was when having to read ~5% of total pages. However, my copy of stackoverflow (50GB) version, has Users table that is using ~50K pages. Reputation=2 query brings 1800 rows (5700 logical reads). That is ~11% of the pages. However, it is still happily doing the index seek + key lookup. Can you explain why? PS: running SQL2016 DEV Total rows in users table 2465713; consuming 50K pages

[@TheBrentOzar]

Sure, read this: www.brentozar.com/archive/2020/05/no-you-cant-calculate-the-tipping-point-with-simple-percentages/

[@rliy001]

@@TheBrentOzar Thanks! So we will leave it as one of those wonderful mysteries of the SQL Black-box?

[@martinrousev]

Much appreciated! Very good stuff. I wanted to ask if and how all this is related to forced parametrization.

[@TheBrentOzar]

Sure, we cover that in detail in our training class, Mastering Server Tuning. (It's beyond what I can do in a KZbin comment though.)

[@rliy001]

Hi Brent, Why am I not getting the same number that sql cardinal estimator uses when local params are used. Your example, you were able to work out the exact number 314 Using density x total rows I don't get the same number sql ce uses

[@TheBrentOzar]

Sure, that's exactly the kind of question I cover in my Fundamentals of Parameter Sniffing class. You can enroll at my site.

[@aroratripti_]

I loved this video.

[@BrentOzarUnlimited]

Thanks, glad you liked it.

[@RC-nn1ld]

fandabidozi, superb Brent as always!

[@r.s.4174]

You should really post the link to Erwins blog.

[@sandeepmaryala2035]

Hi Brent. so finally what would be a good plan to avoid sniffing?

[@TheBrentOzar]

It's beyond what I can cover here in a KZbin comment, but I continue that discussion in my Mastering Query Tuning class.

[@PD-df8rc]

Can parameter sniffing resolve using query store?

[@BrentOzarUnlimited]

To find out, take my Mastering Parameter Sniffing class.

[@Alexu72u]

Thank you!

[@sudarshana2827]

Note Whenever you see parameter sniffing, identify and apply covering index.

[@BrentOzarUnlimited]

Bad news: you are not correct. Time to attend my Fundamentals and Mastering Parameter Sniffing classes. Cheers!

[@dtovee]

Cool :) Thanks for the knowledge share.

[@rliy001]

Still have pen in midair waiting for the answer that does not SUCK

[@mgs3866]

liked it

[@damarh]

He was not being sarcastic about Erlands blog, omg!

[@shaikhussain474]

Wooh, now I got answer for my boss, lol