Running sp_updatestats probably overrides manually done UPDATE STATISTICS WITH FULLSCAN and ruins performance

Question

We experience this on both SQL Server 2014 and SQL Server 2019. We have an application, which loads some data into our database. At the end of this load process, the application runs sp_updatestats stored procedure, which makes sense since the volume of loaded data can be substantial.

We observed that in some cases, when we experience performance degradation, running UPDATE STATISTICS WITH FULLSCAN on relevant tables will fix the performance problem. However, the slowness will come back and I suspect that it happens when the next import comes along and executes sp_updatestats again. As a result, the default behavior of sp_updatestate (which samples data) overrides more precise statistics generated when running WITH FULLSCAN.

Does my explanation make sense? I don't think SQL Server has any way of knowing that the previous statistics were "better" and will just blindly overwrite them.

Answer 1

As I understand it, your issue is that you do UPDATE STATISTICS WITH FULLSCAN and then sp_updatestats (or possibly the auto update stats) kicked in and destroyed your "perfect" statistics with a sampled statistics.

Yes, your assumption is definitely plausible. You should check the documentation for UPDATE STATISTICS and work with what is available. I'm thinking specifically about the PERSIST_SAMPLE_PERCENT option. This allow you to make auto-update, a normal UPDATE STATISTICS (which is what sp_updatestats does) respect your earlier settings. Make sure you read the notes for this option, though.

You can also use the NORECOMPUTE option to selectively disable the auto-update stats for this specific stats. But that won't help if you run sp_updatestats so I believe that above option is more relevant to you.

Answer 2

Yes, this can definitely cause performance issues. Especially queries that rely heavily on sorting and are executed often but on wildly varying datasets become the victim of extreme performance penalties. When this happens I often see one query plan being overused and underperforming compared to almost identical plans.

Before fixing the problem you should save the query plans and statistics. There's a good chance you'll discover there's something like multiple versions of the same query with slight differences resulting in 25 plans to get the same data or a single rogue one that misses a datetime filter and causes a table scan for all others or something stupid like that.

In my experience there's no good reason to let developers trigger rebuilding statistics or indexes automatically. Especially when this happens during peak load or in the middle of the night this causes problems. SQL server can manage the statistics fine by itself on-the-fly, or during scheduled index maintenance. But that being said: when it causes problems like this there's usually a multitude of inefficiencies that are worth looking at. Happy hunting!

select *
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)

Answer 3

It sounds like you have heavily skewed data, which is causing the statistics histogram to not represent the full picture of the data.

To ensure that sp_updatestats does a full scan on those tables that you wish, without having to manually check for those specific tables, use a combination of @resample = 'resample' and PERSIST_SAMPLE_PERCENT.

First, ensure that the sample rate is persisted. This only needs to be done once.

UPDATE STATISTICS YourTable
  WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;

Then ensure that sp_updatestats does it correctly as part of your ETL process.

exec sp_updatestats @resample = 'resample';

If using an explict UPDATE STATISTICS command then you can use RESAMPLE

UPDATE STATISTICS YourTable
  WITH RESAMPLE;