How to Stop Parameter Sniffing in SQL Server
ฝัง
- เผยแพร่เมื่อ 7 ส.ค. 2017
- Are your query parameters being sniffed by SQL Server? Learn how to recognize and solve parameter sniffing performance problems in your SQL queries. Links below.
Blog post: blog.bertwagner.com/why-param...
Follow me on Twitter:
/ bertwagner
Want to receive the latest weekly blog posts and videos in your inbox? Sign up for the newsletter here: upscri.be/c77fc8/ - วิทยาศาสตร์และเทคโนโลยี
Watch Brent Ozar on yt about it.
It is quite normal that your data can change. This is why you are tuning your queries over time. This is nothing wrong cause your data is growing constantly and this is why you have your job :)
I like the 3rd option, but hate it too and not just for maintenance. The idea of having to put that through peer review and production approval would be lovely to explain to others as well as documenting everything so people know why you have duplicate code.
Fantastic explanation Bert, this really helped me.
I have resolved many issues in my environment(ETL) with this energy.
Thank you for investing your time in such a great knowledge sharing session.
Great explanation, Bert. It's a problem that has plagued many for years (even decades). There are still other solutions one could consider, and more have been added to later and later versions of SQL Server. (And actually, the problem can plague more than just SQL Server.)
But this is a really well-done demonstration of the problem and some basic solutions which may be all many need. Thanks for the effort!
Most excellent nit-picky explanation on how to force the optimizer to work for you. Especially useful for, for example, SSRS.
Clear and good explanations.in very short period of time
Hey Nicely Explained, Thanks from Afghanistan
First 2 options are great and often used. The 3rd option as you explained, can be a nightmare especially if for example, new countries are added to your table that aren't specifically optimised for in the procedure.
Another option surprisingly not mentioned, is declaring local variables in your SP that pick up the variables past through say an application like SSRS. This avoids parameter sniffing altogether... A blessing but occasionally a curse too! Good video!!
Good analysis, but Bert already told about the maintenance which required for option 3 :-)
Good video. Thanks for making it! That espresso shot extraction at the end was terrible though! 10.5 seconds is way too fast! You need to grind it finer to get it up to ~28 seconds (assuming the volume of coffee is good as well as the tamping pressure being adequate). :)
Saludos desde México muy buena explicación y sobre todo explicas la solución contundente.
It's really hot in there (thanks for the explanation) ^^
Thx for good explanation!
Good video. Do you know how can I improve the performance into a stored procedure that has multiple table type parameters? I added the option recompile but that didn't help too much.
Easy do not use table parameters. Instead use temp tables
All options have their trade offs
I get this error, "mysql.connector.errors.ProgrammingError: Not enough parameters for the SQL statement" in mysql, please how do I resolve it?
what happened with your right ear???
Clap clap