June 1, 2018 at 1:58 pm
Hello everyone,
We have a table in our database that contains 80 million records. The statistics are rebuilt at 3 am every day. By 9 am upwards of 50K records have been added to this table and the system performance diminishes. I manually ran the update statistics and right as soon as that finished performance was back to good.
Database auto update stats is set to true, but after reading more on it I believe the rebuild condition will not get hit right away. If I understand this condition correctly:
The table contained more than 500 records and the records changed more that 500 plus 20% of the number of rows already in the table.
20% of 80 million is way less than the number of records added by mid-morning.
80M*20%=16M.
I was thinking of running the update Statistics on my table at 9 am, 12 pm, 3 pm. The statistic take less than 2 minutes to update. Or is there a better way to accomplish this?
Suggestions?
June 4, 2018 at 12:00 am
Hm,
I think this should be 20% and 500 Row.
Not 500 Rows + 20%
And than, your 50k records won't auto update your statistic.
Take a lock at this weblink:
https://blogs.msdn.microsoft.com/saponsqlserver/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371/
Kind regards,
Andreas
June 4, 2018 at 6:47 am
As every database is different, you may need / want to update statistics more frequently.
From the sound of it, you're not hitting the threshold for an auto-update, but you've got enough "churn" to need to update. One other thing that could be impacting your performance is, what kind of data is in the table in question? I would expect, if for example, the table is using something like a GUID as the primary key / clustered index, you're getting a LOT of fragmentation, so the stats get out of whack quickly. In which case, yes, more frequent statistics updates would be a good idea. A longer term solution would be to (again, presuming GUID PK/CI) look at re-designing the table(s) to use a better PK/CI (such as an INT or BIGINT) but that would still not 100% resolve the issue.
So, short answer, yes, you probably do want to set up a statistics update job to run more frequently.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply