5

We had some performance issues with our ERP system. Somewhere around 06:00 the problem started. Long story short; one of my colleagues executed a DBCC FREEPROCCACHE (I know that this is not a preferred action, but that is not the point here). After that, he restarted the whole ERP application, not the database engine. That didn't seem to help. I looked into the Query Store and saw that the query plan was changed somewhere around 06:05. I tried forcing the older plan and the performance issues were gone.

I compared the 2 plan XMLs and I saw that both were based on the same statistics (updated date was exactly the same). The number of changes on the indexes was different, but that did not trigger an update statistics, because else the new query plan would have had a new date for the used statistics. I checked all the statistics of the involved tables, but they were not updated since the last maintenance job. The last maintenance job ran this weekend, which was also the update date of the statistics used in both query plans.

The query is a parameterized query, so why the query plan changed suddenly is a little unclear to me. I would think just the opposite, that a query suddenly runs slow, because the same plan is used for different parameters. This did not seem to be the case here, because I forced the old query plan.

The query comes from some bespoke work that retrieves orders. Now, I suspect that someone issued the parameterized query which would have returned a very large dataset and that slowed down the ERP system. Then, my colleague ran a DBCC FREEPROCCACHE and that triggered the re-compile of the query plan, based on the new statistics from this weekend. The current query just kept running and that is were he decided to restart the ERP application. Now with the new query plan all new queries slowed down until I forced the old query plan.

Could this be the explanation? I am a little skeptical, because SQL Server would trigger a re-compile when statistics have changed, so the plan should already have been re-compiled after the index maintenance job.

I think I am missing something here, but I am not exactly sure what. It is SQL Server 2016 Standard and is a dedicated server for the ERP system.

Edit 15-4-2020 12:17: Now that I write this... I think the issue is just that after the DBCC FREEPROCCACHE the parameters of the first execution of the parameterized query in question used different parameters, which caused SQL Server to recompile the (bad) plan. Or is there still something I am missing?

Improve this question
0

1 Answer 1

Reset to default
5

Statistics are one input to the query optimizer but not the only one. As mentioned in the post, the optimizer also sniffs parameter values during compilation and uses those to generate estimates of the number of rows that will come out of various operations such as filters, joins, and group by using the statistics that exist at the time of compilation. Furthermore, the storage engine provides row counts for each compilation and this can also change without stats changing (though if you change enough rows, the optimizer eventually forces stats to be recomputed). Any of these things changing could be the cause of the plan changing from one compilation to the next. The query store actually also tracks other factors that it internally uses to represent to you whether the query is the same or different semantically through a table called sys.context_settings. So, ansi null on/off would be one such semantic change that could also cause differences in results. The query store abstracts these from you, but if you were to, for example, create a loop in a stored procedure that changed ansi nulls on/off and then run the same statement over and over in the loop, you'd actually get that to compile over and over (each time the settings are changed from the last time the statement in that part of the sproc is compiled) and this would also give you a chance to have new parameters sniffed on each execution. These would technically show up as different queries in the query store but to the SQL engine these are holding the same statement slot in the cached query plan for the stored procedure.

SQL 2017+ contains a feature called Automatic Tuning which can auto-force the old plan for you for large regressions. Azure SQL DB also has this same feature. In the future, we hope to make parameter sniffing less of a problem for customers, so I hope this gives you insight in the meantime to work around the issue for your scenario.

Improve this answer
6
  • Thanks for taking the time to return such a lengthy anwser Conor! I will also definitely take a look at sys.context_settings (quick look returned sys.query_context_settings btw) and AQT. One question; you stated 'Furthermore, the storage engine provides row counts for each compilation and this can also change without stats changing'. Iam a little bit confused here, because I thought that row count was input to get the plan compiled, not output. Did you mean every execution or does it take the amount of changes into account ( i saw that that is mentioned in the stats section of the XML plan)?
    – Niels Broertjes
    Apr 16, 2020 at 19:34
  • I checked out the ParameterCompiledValue and the bad plan did have them, but the good plan did not. So I need to also figure out why that is that the good plan didn't even have them although this is a parameterized query. Maybe you know, but I will try to look that up tomorrow. It is DBA bedtime now and my girlfriend is urging me to leave my computer :-).
    – Niels Broertjes
    Apr 16, 2020 at 19:40
  • I am not sure if it is parameter sniffing. I think that parameter sniffing would not have caused the plan to be recompiled, but instead would have re-used the plan with the query's new parameters. That is not the case here. The plan was re-compiled and the old plan is better than the new plan (the other way around). It is also recompiled, which is something parameter sniffing does not trigger, am I correct? It uses the same plan over and over again for queries with different params. The solution then would have been a re-compile. Or am i missing something here?
    – Niels Broertjes
    Apr 17, 2020 at 4:50
  • 2
    We look at the rowcounts when we compile. Some features, like memory grant feedback, indirectly look at per-execution information to correct potential cardinality errors as they relate to memory grant reservations for subsequent executions of a compilation. Adaptive (batch mode) joins and interleaved execution can look at cardinality in a more active way for patterns that are often heavily influencing performance. However, the general answer is that rowcounts are only looked at during compilations.
    – Conor Cunningham MSFT
    Apr 17, 2020 at 15:16
  • 2
    There is a row modification counter snapshotted into the statistics to keep track of when to later determine the stats to be stale. The storage engine provides the row counter during compilation separately than the stats to determine the expected row count from a table for a table scan. Both are combined by the optimizer to determine cardinality estimates in different operators
    – Conor Cunningham MSFT
    Apr 21, 2020 at 12:36

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