Why parameter sniffing can cause a lot of trouble!

I just read this blog post from Kimberly L. Tripp from SQLSkills.com. It is all about how parameter sniffing can cause some severe performance degradation on an system. It is a good eye opener for the people that will update their statistics when their estimated and actual query plans show some real different returned rows. Normally the first thing i would do in such a case is to update my statistics. And most of the times it’ll work.

However. When you update statistics your plan gets invalidated, so a new plan will be generated the next time. So in case you have suffered from a thing called parameter sniffing, the update statistics will solve nothing, but the side effect of plan invalidation will….

So next time when you see estimated and actual row numbers be quite different and you are suffering performance degradation, look again at this post : http://www.sqlskills.com/blogs/kimberly/what-caused-that-plan-to-go-horribly-wrong-should-you-update-statistics/


Have a good weekend!

Leave a Reply




7 + 4 =