Identifying and Fixing Parameter Sniffing Issues
Vložit
- čas přidán 4. 01. 2019
- 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.
- Věda a technologie
This session is gold. Absolute pure gold !! Pure class from Brent
Thanks, glad you liked it!
I wish I got teachers like you! Absolute legend! Cheers
Thanks!
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
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).
What a great session! A lot of practical knowledge. Thanks!
amazing presenter, the best i've seen, thank you so much for telling us serious things in this funny and easy way
brent ozar never fails! great videos
You are special!!! Thanks for the great video!!!
What a legend!! Absolutely loved the session..
Amazing Session Brent...A lot of learning today.Mindblowing. looking for more sessions.
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 !!
you are such an amazing presenter!
This is such a good session and really shows the issues succinctly.
Thanks!
Interesting demo about parameter sniffing, and ... funny "anchor-man".
I liked it.
Glad you enjoyed it!
Thank you! Did not know I was wasting my time with the local variable testing.
U r smiply superb Brent, watched 👀 this video 🎥 and liked it very much.Thanks🌹
Learned a lot. thanks Brent
It is a veey clear explaination and your sp is very useful
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.
fandabidozi, superb Brent as always!
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.
impressive presentation...
Not often I find anything I can use in my job on CZcams 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.
Thank you!
Very nicely explained. Very helpful 👍
Glad it was helpful!
Tks Brent. Love this.
Glad you enjoyed it!
Cool :) Thanks for the knowledge share.
I loved this video.
Thanks, glad you liked it.
Much appreciated! Very good stuff. I wanted to ask if and how all this is related to forced parametrization.
Sure, we cover that in detail in our training class, Mastering Server Tuning. (It's beyond what I can do in a CZcams comment though.)
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).
We'd love to watch you do a presentation on CZcams about how you solved that problem in that way.
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?
Ruch - it only sniffs the *first* set of parameters, not all of them.
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
Sure, read this: www.brentozar.com/archive/2020/05/no-you-cant-calculate-the-tipping-point-with-simple-percentages/
@@TheBrentOzar Thanks! So we will leave it as one of those wonderful mysteries of the SQL Black-box?
You should really post the link to Erwins blog.
Hi Brent. so finally what would be a good plan to avoid sniffing?
It's beyond what I can cover here in a CZcams comment, but I continue that discussion in my Mastering Query Tuning class.
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
Sure, that's exactly the kind of question I cover in my Fundamentals of Parameter Sniffing class. You can enroll at my site.
Still have pen in midair waiting for the answer that does not SUCK
liked it
Can parameter sniffing resolve using query store?
To find out, take my Mastering Parameter Sniffing class.
He was not being sarcastic about Erlands blog, omg!
Wooh, now I got answer for my boss, lol