How to Stop Parameter Sniffing in SQL Server

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.

Follow me on Twitter:

[CharlieArehart]

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!

[gopiadabala]

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.

[randeepsinghmatharu]

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

[AlejandroBelloRD]

Most excellent nit-picky explanation on how to force the optimizer to work for you. Especially useful for, for example, SSRS.

[hovardlee]

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

[grandetaco]

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.

[shaikzuhair]

Clear and good explanations.in very short period of time

[ssrakeshsharma]

Hey Nicely Explained, Thanks from Afghanistan

[notcranium]

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

[FlashDark]

It's really hot in there (thanks for the explanation) ^^

[natanaelmontes]

Saludos desde México muy buena explicación y sobre todo explicas la solución contundente.

[aurelianpaulmuresan]

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.

[ahmettek]

As I read it, SQL server query planner isn't doing a good job and this hasn't been corrected even in the last version. Other databases do not have this problem as far as I know.
And also the explanation doesn't really make sense, why would it generate a nonoptimal plan for that sample or for even the simplest queries with NO PARAMETERS at all. If you think about the sample, it is nonsense to use table scan in the first place. when you could use an index. If I have 100, 000 rows and the query would return 99, 000 of it well yeah table scan is better, really? How much SQL server would gain not using the index there? That would obviously hurt the searches that would return 10 or 1000 rows and SQL server still chooses this plan, great minds.

This is a real query that was causing problem (only field and table names are altered):

select Id, myId, myType, myJSON from myTable where IsProcessed=0 and IsValid = 1 order by processingDate asc offset 0 rows fetch next 1000 rows only;

This query, returning only 20-30 rows, from a table of 2.5 million rows started to take over a minute, despite there were indexes that SQL server could utilize for this. I wasn't the original author and I fixed this, clearing the plan cache + creating a filtered index on processingDate. The issue is, creating and populating that index didn't even take a second. SQL server should and could do this behind the scenes instead of generating an insanely bad plan.

Of course I could claim SQL server query planner is a bug in itself and I could be right, but who am I to question MS - looks like they fail to copy the good parts from open source :)

[ngwustephen]

I get this error, Not enough parameters for the SQL statement" in mysql, please how do I resolve it?