7

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?

1

3 Answers 3

7

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 "the" 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 "global" 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.

0
3

Answer generated from comments

You could get the wrong query plans due to the wrong statistics you got after update statistics. But you also could get the wrong query plans due to parameters sniffing when after update statistics the first parameters that your query got were not as usual. It's impossible to understand from your question which of the issues is presented. Try to recompile the query plan when the query gets worse instead of updating statistics to split the two different issues. – Denis Rubashkin

There is many factor that can cause a "rebuild" of the execution plan. So it would explain why it works fine for a while and suddenly start to work slow. When you update the stats, all execution plan that have something to do with this object are invalidate and this will cause the next execution to build a new plan. Depending on the values used for it, it may fix or not the problem (most of the values fixes it while some other don't, which explains why sometime it works, sometime not).

Another way to "fix" the execution plan would be to use Query Store (I think it started with SQL Server 2016) and "fix" the plan to use. It could have some drawbacks if the data changes a lot (as SQL Server will not be able to produce a better plan) but it could fix that kind of issue (I have a query running with a fix execution plan since 2 years now and didn't have the parameter sniffing issue since). – Dominique Boucher

-1

Are you checking the index fragmentation over time? Having many updates on a table will always cause index fragmentation so having a schedules maintenance plan to rebuild indexes is definitely recommended, or even after a large data import.

I have dropped indexes before large data imports and then rebuilt them afterwards, speeding up the import, and maintaining index fragmentation.

Also, confirming your ISOLATION LEVEL configuration. Using row versioning is more optimistic than READ COMMITTED with regards to record and table locks, and can make a huge difference to query performance when reading from heavily used tables. Helps prevent deadlocks and reduces query waiting time without the threat of dirty data being presented.

Finding out which queries and which tables are concerned with the go slow would obviously be useful. Sometimes parallelism causing an huge increase in wait times especially when stats and index frag is apparent. Forcing MAXDOP 1 can stabalise query times and sometimes improve the overall performance. Minimally it will make query times consistent, even if it reduces performance overall, it helps reduce the risk of them falling off the edge.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.