June 4, 2017 at 11:55 pm
Hi All ,
I would like to know how often we need to update statistics for all databases ?
I need to create a maintenance plan for this
Daily or weekly ?
Any feedback are really appreciated
Cheers..
June 5, 2017 at 6:23 am
There just isn't a single answer here that's going to satisfy all situations. I've maintained systems that had statistics maintenance running anywhere from weekly to daily to, for one table in one horrible instance, every five minutes. It largely depends on the rate of change of your databases. Each database, and, as mentioned above, even individual tables or indexes, may change differently and may need a different schedule. I would say update the statistics as often as you can without causing a negative impact on the server. For very low volume databases, weekly is probably fine. Most of the middle, daily is fine. However, you'll have to figure out where you have unique situations that require more frequent or less frequent statistic updates.
You'll also need to investigate the need, situationally, to run statistics updates using a full scan instead of the standard sampled method. Most statistics will be fine with the sampled approach, but some may need to be more precise.
Just remember one important point, rebuilding an index also updates the statistics using a full scan. Don't run the standard sampled statistics updates on indexes that have just been rebuilt. That will actually reduce the accuracy of those statistics.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 5, 2017 at 6:25 am
WhiteLotus - Sunday, June 4, 2017 11:55 PMHi All ,
I would like to know how often we need to update statistics for all databases ?
I need to create a maintenance plan for this
Daily or weekly ?Any feedback are really appreciated
Cheers..
How often and how much does your data change?
Do you have auto-update statistics enabled on your databases, depending on your version, statistics are updated on different criteria - read up at https://docs.microsoft.com/en-us/sql/relational-databases/statistics/statistics.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
June 5, 2017 at 12:01 pm
Since this is in a 2008 forum, you should be aware that auto-update statistics settings is often not enough for large tables. There's a known condition since SQL Server will only decide to update statistics based on 20% + 500 rows of data worth of chanes:
https://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/
Unfortunately the best way to actually see the modify counter is using the old depreciated sys.sysindexes system view like this:
SELECT OBJECT_NAME(i.object_id) AS TableName, MIN(STATS_DATE(i.object_id, i.index_id)) AS OldestStatisticsDate,
MAX(si.rowmodctr) AS RecordsModified, MAX(si.rowcnt) AS TotalRecords, MAX(si.rowmodctr)*100.0/MAX(si.rowcnt) AS pct
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id=o.object_id
INNER JOIN sys.sysindexes si ON i.object_id=si.id AND i.index_id=si.indid
WHERE o.TYPE <> 'S'
AND STATS_DATE(i.object_id, i.index_id) IS NOT NULL
AND si.rowcnt > 0
AND si.rowmodctr > 0
GROUP BY i.object_id
ORDER BY RecordsModified DESC;
so I periodically schedule a SQL Agent job to handle these by querying sys.sysindexes and calling UPDATE STATISTICS command explicitly for those tables. You can also explicitly call UPDATE STATISTICS after a large process or data import has occurred.
June 5, 2017 at 6:44 pm
Grant Fritchey - Monday, June 5, 2017 6:23 AMThere just isn't a single answer here that's going to satisfy all situations. I've maintained systems that had statistics maintenance running anywhere from weekly to daily to, for one table in one horrible instance, every five minutes. It largely depends on the rate of change of your databases. Each database, and, as mentioned above, even individual tables or indexes, may change differently and may need a different schedule. I would say update the statistics as often as you can without causing a negative impact on the server. For very low volume databases, weekly is probably fine. Most of the middle, daily is fine. However, you'll have to figure out where you have unique situations that require more frequent or less frequent statistic updates.You'll also need to investigate the need, situationally, to run statistics updates using a full scan instead of the standard sampled method. Most statistics will be fine with the sampled approach, but some may need to be more precise.
Just remember one important point, rebuilding an index also updates the statistics using a full scan. Don't run the standard sampled statistics updates on indexes that have just been rebuilt. That will actually reduce the accuracy of those statistics.
Thank you very much for the feedback . What do u mean by sampled approach ? I have a plan to run statistic update on indexes that have been rebuilt
My plan is to run this stored procedure :
EXEC sp_updatestats;
What do you think ?
Thank you !
June 6, 2017 at 4:35 am
WhiteLotus - Monday, June 5, 2017 6:44 PMThank you very much for the feedback . What do u mean by sampled approach ? I have a plan to run statistic update on indexes that have been rebuilt
My plan is to run this stored procedure :
EXEC sp_updatestats;
What do you think ?
Thank you !
No. Don't update statistics on indexes that have just been rebuilt. Rebuilding an index also does a full scan update on the statistics. Read all about full scan versus sampled here in the documentation. sp_updatestats only does a sampled rebuild of the statistics, which is the default. Again, read the documentation for that. The difference is very accurate, and expensive to generate statistics, versus less accurate but inexpensive to generate statistics. An index rebuild gets the expensive but accurate set for free as a by-product of the rebuild. If you then update the statistics, you're removing the accurate set and substituting the inaccurate set.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 6, 2017 at 4:36 am
WhiteLotus - Monday, June 5, 2017 6:44 PMGrant Fritchey - Monday, June 5, 2017 6:23 AMThere just isn't a single answer here that's going to satisfy all situations. I've maintained systems that had statistics maintenance running anywhere from weekly to daily to, for one table in one horrible instance, every five minutes. It largely depends on the rate of change of your databases. Each database, and, as mentioned above, even individual tables or indexes, may change differently and may need a different schedule. I would say update the statistics as often as you can without causing a negative impact on the server. For very low volume databases, weekly is probably fine. Most of the middle, daily is fine. However, you'll have to figure out where you have unique situations that require more frequent or less frequent statistic updates.You'll also need to investigate the need, situationally, to run statistics updates using a full scan instead of the standard sampled method. Most statistics will be fine with the sampled approach, but some may need to be more precise.
Just remember one important point, rebuilding an index also updates the statistics using a full scan. Don't run the standard sampled statistics updates on indexes that have just been rebuilt. That will actually reduce the accuracy of those statistics.
Thank you very much for the feedback . What do u mean by sampled approach ? I have a plan to run statistic update on indexes that have been rebuilt
My plan is to run this stored procedure :
EXEC sp_updatestats;
What do you think ?
Thank you !
sp_updatestats doesn't provide very granular support for sampling, or for which tables have their statistics rebuilt. You might consider using UPDATE STATISTICS instead.
John
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply