Consistent query performance over time We're running an intensive application load (thousands of operations/second) against a SQL Server database with quite a bit of data. Some tables have billions of rows, several of these have lots of inserts and updates.
DB performance is generally quite OK, but periodically we get query performance problems; rather simple queries that previously worked just fine may take 10-100x the time all of a sudden.
This appears to be related to table/index statistics and the query optimiser - most times a statistics update will fix the problem, then again other times an update of the statistics will make the situation worse (re-running the statistics update will then typically solve the problem eventually).
What appears to be happening is that the optimiser decides to use objectively wrong indexes for some queries; all of a sudden, after having used the correct one for days and weeks.
My question is: Why is this happening and what can we do about it?
This database has been running for years with basically the same load, pretty much the same queries, and the same amount of updates. For 99.995% of the queries, there should be no reason to ever decide on different index strategies over time, regardless of the input (and - indeed - doing so will demonstrably completely destroy query performance).
As indicated above, automatically updating statistics on a schedule will often generate horrible problems - if the statistics sample gets skewed (which appears to happen at least 5% of the times) we end up in a world of pain.
Is there any way to tell SQL Server (on certain tables) that the statistics histogram and density will not change over time, so please just continue using the same query plan for queries involving this table? If not, how can we ensure a predictable result of statistics update over time (avoiding the skewed statistics problem described above)?
No stored procedures. We do have control over the SQL, so it can potentially be changed but it's a LOT of code so it would be unfortunate if we had to change every single query (e.g. adding in an additional clause).
A follow-up question: parameter sniffing only appears to be relevant for stored procedures, is that correct?
I suggest you first determine whether its the stats or if its parameter sniffing that is hurting you.
Regardless of above I suggest you read Erland's article on the subject.
What to do about it is difficult to say. We don't know whether it is stats or sniffing.
But possibly adding OPTIMIZE FOR can be &quot;the&quot; solution. It is cheaper than RECOMPILE since you don't have to take the plan production hit on each execution. And it gives you predictability. This, of course, assumes that you don't have the case where the stats differ so much so the same parameter input yield different plans because of stats reasons.
Try to identify one query. See if you have one or many plans for the query. Test with OPTIMIZE FOR and/or RECOMPILE. The one &quot;global&quot; option at database scale you have is to disable parameter sniffing for the database. This means the optimizer optimizes as it doesn't have a clue of the value. All this and more in Erland's article.
Parameter sniffing does not only apply to stored procedures. It also applies to parameterized SQL (typically executed using sp_executesql), which likely is far more common nowadays than stored procedures.