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

[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

[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).

[marancibia]

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

[chiragbhatt]

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 !!

[themortu]

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

[bluerays]

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

[RodrigoMatela]

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

[datanalan]

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

[ganeshjraj]

What a legend!! Absolutely loved the session..

[AsailingDIYlifechannel]

Not often I find anything I can use in my job on YouTube 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.

[parnet]

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

[andreabruschetta]

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

[eugenechen]

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.

[ScottLucas]

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

[marcosdiez]

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).

[martinrousev]

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

[koushikdey]

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

[monday]

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.

[rliy]

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?