April 6, 2017 at 7:16 am
Hi
I would like to understand when Statistics will automatically update?
From what I read, it is based on modification of the table. And for large tables it is 500 + 20%.
I have a table of about 20 million records, which is frequently access and modified, but on average only about 20,000 records get inserted per day, and about the same amount get modified.
Yet the auto update will update the statistics every morning, and when it does, it uses a very small sample set, and then performance becomes really bad on this table.
I then update the statistics on the table with FULLSCAN which seems to fix the problem. This has now been the case for the past 2 days - last week the performance was fine.
Why is the statistics automatically updated if modification is not close to the threshold? Unless I am understanding the threshold incorrectly?
April 6, 2017 at 7:19 am
difficult to say without more info, for this object you can set the norecompute to disable the auto stats updates and juts put in a manual task to handle this
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 11, 2017 at 8:50 am
A full re-index rebuild will update stats. Is the DB being re-indexed every day?
April 11, 2017 at 9:53 am
Talib123 - Tuesday, April 11, 2017 8:50 AMA full re-index rebuild will update stats. Is the DB being re-indexed every day?
This could be a possibility if the table is partitioned since starting with SQL Server 2012 it will use "the default sampling algorithm":
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes
I believe if the table is not partitioned a rebuild still uses the full scan for statistics though. Either way, hopefully you are NOT rebuilding indexes daily. For such a small percentage of inserts and updates it would initially seem of little value to do constant rebuilds. Have you analyzed the fragmentation and index usage (scans vs seeks) to see if a rebuild is even necessary?
If you're using the built in Maintenance Plans feature of SQL Server, it may benefit you to look into one of the freely available maintenance scripts that are more flexible and intelligent in how they maintain the data:
https://ola.hallengren.com/
http://minionware.net/reindex/
April 11, 2017 at 2:23 pm
When your records are inserted, is it bulk load or spread out throughout a given time period?
April 11, 2017 at 11:28 pm
I have a daily job that runs at 5 AM which reorganize/rebuilds indexes based on fragmentation.
But I see the auto update of the statistics has a date later then that - anywhere after 7 AM or 8 AM. The strange thing is, if I then do a update on those tables with FULLSCAN, the date remains static with a sample of 100%, until the next morning again - when the auto update happens with the small sample set
The problem still persists and it has been a week now.. I guess I can add NORECOMPUTE as suggested, but I would really like to understand why this is happening
April 11, 2017 at 11:31 pm
With regards to the question of bulk load or spread out: it is spread out over the day.
April 12, 2017 at 2:08 am
Casper101 - Tuesday, April 11, 2017 11:28 PMI have a daily job that runs at 5 AM which reorganize/rebuilds indexes based on fragmentation.
But I see the auto update of the statistics has a date later then that - anywhere after 7 AM or 8 AM. The strange thing is, if I then do a update on those tables with FULLSCAN, the date remains static with a sample of 100%, until the next morning again - when the auto update happens with the small sample set
The problem still persists and it has been a week now.. I guess I can add NORECOMPUTE as suggested, but I would really like to understand why this is happening
Is the 5am job by chance a maintenance plan built from the gui?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 12, 2017 at 2:17 am
Nope, no maintenance plan, it is T-SQL code I created
April 13, 2017 at 3:17 pm
If the FULLSCAN fix the problem why don't you add one additional line to your TSQL to update the stat with FULLSCAN
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply