Is it normal to UPDATE STATISTICS this frequently?

Question

I have a stored procedure that processes data from a source table and is executed many times (about 20,000 executions) throughout the day.

I believe it's getting choked up sometimes, due to parameter sniffing and I've been updating statistics on the relevant tables (8 tables) in the procedure to fix this.

When it gets choked up, a backlog of unprocessed data builds up (which I'm able to monitor the count on.)

Once I update the statistics the backlog is instantly cleared.

I find myself updating statistics about 10 times a day (almost once an hour). Does this seem normal or is there a bigger issue I should be looking to fix?

score 2 · Answer 1 · edited Jan 08 '20 at 02:14

There is multiple ways to fix the parameter sniffing issue.

If you update your statistics 10 times a day and especially if that is causing performance issues, then I would look at other options.

There is some good chance that the update of the stats is not what's fixing your parameter sniffing issue. The update of the stats causes the plan to be flushed from the cache (which is probably fixing your issue). Flushing only that plan from the cache would probably work as well without having to rebuild the stats ;)

If I were you, I would take it from the start and find which set of parameters causes SQL to take a bad execution plan. Once you have this, you can then start looking at the way to fix it.

You could use the QueryStore to fix this, forcing SQL to use always the "good" execution plan for example.

Sometimes, A good index can also fix parameter sniffing issue (that can cause SQL to build a different execution plan that would be good for whatever the parameter is)

John Eisbrener · Answer 2 · 2020-01-08T16:39:57.490

You may be running into a scenario where the (perhaps default) sampling percentage isn't sufficient for the stats to be useful for longer periods of time. You can quickly check to see what sample rates were used during the last UPDATE STATISTICS statement via the following query:

SELECT  OBJECT_SCHEMA_NAME(st.object_id) + '.' + OBJECT_NAME(st.object_id) AS TableName
    ,   col.name AS ColumnName
    ,   st.name AS StatsName
    ,   sp.last_updated
    ,   sp.rows_sampled
    ,   sp.rows
    ,   (1.0*sp.rows_sampled)/(1.0*sp.rows) AS sample_pct
FROM sys.stats st 
    INNER JOIN sys.stats_columns st_col
        ON st.object_id = st_col.object_id
        AND st.stats_id = st_col.stats_id
    INNER JOIN sys.columns col
        ON st_col.object_id = col.object_id
        AND st_col.column_id = col.column_id
    CROSS APPLY sys.dm_db_stats_properties (st.object_id, st.stats_id) sp
--WHERE OBJECT_SCHEMA_NAME(st.object_id) + '.' + OBJECT_NAME(st.object_id) = 'dbo.Mytable'     -- <-- uncomment to filter for a specific table
ORDER BY 1, 2

If you filter for the tables causing your issues and you see the sample rates are low, you may want to experiment with increasing the sample rate to see if the stats remain relevant for longer periods of time. You may even find FULLSCANs are necessary. Here are a couple of examples showing what I'm talking about in regards to increasing the sample rate:

-- reference 20 percent of the column table when building the stat
UPDATE STATISTICS dbo.MyTable myTableStatName WITH SAMPLE 20 PERCENT

 -- or -

-- reference all the column data when building the stat
UPDATE STATISTICS dbo.MyTable myTableStatName WITH FULLSCAN

Of note, if you do find that increasing the sample rate improves the lifespan of the stats AND you're on SQL Server 2016 SP1 CU4 (or SQL Server 2017 CU1) or later, a new keyword was included PERSIST_SAMPLE_PERCENT for the UPDATE STATISTICS command. This keyword forces any auto-update statistics runs against said statistic to use whatever sample percentage you manually specified. Without including this keyword, default sampling will be used during auto-update statistics runs which can become a major headache if you require a higher sampling percent.

The Tiger Team released a nice blog post on the subject which I recommend you check out if you want some more in-depth information on this new keyword.

Thanks to @RandiVertongen's comment, as outlined in Erin Stellato's blog post it will likely make more sense to try a FULLSCAN before choosing a larger SAMPLE percentage for your initial testing.

Is it normal to UPDATE STATISTICS this frequently?

2 Answers2