June 20, 2018 at 8:36 am
I ran into an issue with statistics that was resolved by creating a statistic with fullscan. This is for a DataMart that's reloaded nightly. I assumed that creating that stat with fullscan would mean it'd full scan every time it was auto-updated, however, looking after the refresh of the DB the next day, this stat is sampling again.
Is it possible to somehow have this always full scan, or do I need to manually update stats on the table this stat is attached to after reload?
June 20, 2018 at 10:38 am
If I understand what you're saying, there may be a new setting on the statistics that you could use called PERSIST_SAMPLE_PERCENT:
https://blogs.msdn.microsoft.com/sql_server_team/persisting-statistics-sampling-rate/
June 20, 2018 at 12:05 pm
That's exactly what I needed! Yes, it's resetting my sampling percent when it auto-updates, so this will hopefully fix it.
June 20, 2018 at 12:34 pm
You can rebuild the statistics with no_recompute - this will stop the statistics from being updated through auto update stats. You will then have to insure the statistics are updated through your own process or manually as needed.
With that said - if the table is loaded once a night, how is an auto update stats triggered? If this is being triggered by the load process itself - you might want to consider rebuilding the indexes on the table after the load process. In fact, you may want to consider disabling all non-clustered indexes prior to the load, then after the load rebuild all indexes. This will insure all index statistics have been updated with a full scan.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 20, 2018 at 1:05 pm
That's something to investigate, but I think the drop and recreating of the indexes is part of the reload. So when an index is built the first time, it creates the statistics with a full scan, not sampling?
June 20, 2018 at 2:27 pm
Are the indexes re-created before or after the load of the data? If before, how is the data loaded and how many rows are loaded? Are the statistics in question actually from the index or are they separate? Those are factors to consider if they will get updated when and how you expect.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply