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 !!
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.
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.
Not often I find anything I can use in my job on UA-cam 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.
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).
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?
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
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
This session is gold. Absolute pure gold !! Pure class from Brent
Thanks, glad you liked it!
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).
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
What a great session! A lot of practical knowledge. Thanks!
I wish I got teachers like you! Absolute legend! Cheers
Thanks!
You are special!!! Thanks for the great video!!!
What a legend!! Absolutely loved the session..
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 !!
Interesting demo about parameter sniffing, and ... funny "anchor-man".
I liked it.
Glad you enjoyed it!
you are such an amazing presenter!
Amazing Session Brent...A lot of learning today.Mindblowing. looking for more sessions.
This is such a good session and really shows the issues succinctly.
Thanks!
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.
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🌹
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.
Learned a lot. thanks Brent
Not often I find anything I can use in my job on UA-cam 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.
It is a veey clear explaination and your sp is very useful
Very nicely explained. Very helpful 👍
Glad it was helpful!
Tks Brent. Love this.
Glad you enjoyed it!
impressive presentation...
fandabidozi, superb Brent as always!
Note
Whenever you see parameter sniffing, identify and apply covering index.
Bad news: you are not correct. Time to attend my Fundamentals and Mastering Parameter Sniffing classes. Cheers!
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 UA-cam about how you solved that problem in that way.
I loved this video.
Thanks, glad you liked it.
You should really post the link to Erwins blog.
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 UA-cam comment though.)
Cool :) Thanks for the knowledge share.
Still have pen in midair waiting for the answer that does not SUCK
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.
Can parameter sniffing resolve using query store?
To find out, take my Mastering Parameter Sniffing class.
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?
He was not being sarcastic about Erlands blog, omg!
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.
liked it
Wooh, now I got answer for my boss, lol
Hi Brent. so finally what would be a good plan to avoid sniffing?
It's beyond what I can cover here in a UA-cam comment, but I continue that discussion in my Mastering Query Tuning class.
Thank you!