Grant Fritchey (1/25/2013)

---

sqlnaive (1/25/2013)

---

Grant, in addition to this, does it means just declaring internal parameters won't resolve parameter snififng ? Is updating stats mandatory (I know there's always "as per situation" criteria but in generalized way) ? I am asking this because at times it becomes hard to update stats on big tables on daily basis in prod environment. In that case what would you suggest ?

"internal parameters" are called variables. And SQL Server can't sniff variables. The difference is that with a parameter on a stored procedure you get a precise seek against the statistics based on the value provided. With a variable you get a sampled average of the statistics. The issue is, are your queries better off with precise values or with averages? I can't tell you, you have to investigate it. But, the other issue comes into play when the statistics are bad (out of date or sampled instead of a full scan). Then, the precise seek returns a value, but it's an imprecise value leading the optimizer to make bad choices again. So, if you have lots of data being added to these tables and your statistics are going out of date (and this becomes especially true when dealing with date based data that is usually filtering for the latest information) then your query plans are going to go wonky. Your solutions are limited, update the stats.

Other than that... you can get into trying weird stuff. If the date range being searched against regularly is the latest data, then create a filtered index that includes today's date. Then the data set will be very small and you could update the statistics several times during the day (assuming the automatic updates don't do it for you).

But the issue is, if you want fast queries, you need good statistics and there's very few options around that.