September 21, 2016 at 10:44 am
Hi,
I am in the process of setting up some of Ola Hallengren's database maintenance scripts. As most of you probably know, there is an Index Optimize job for user databases. I have a table in a vendor-delivered database that's responsible for importing data (we populate the table, then run something inside the application that pulls data from that table into other tables in the database). Well, that first table has hundreds of rows and hundreds of statistics. When I run Ola's script, I see that the clustered index was rebuilt and the statistics updated for that index, which I expected. However, none of the other, very outdated statistics in that table were touched. So I have two questions:
1) Does it make sense that none of those other statistics were updated? Is it likely only focusing on INDEX statistics and no other type? If the clustered index was updated, should I be worried about updating the other statistics?
2) Does it make sense that I have that many statistics period--a couple hundred!?
This "import" process from this staging table to other tables by the application used to take around an hour and now it takes several hours, so I'm trying to analyze some of the low-hanging fruit to understand what might be causing this.
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 21, 2016 at 12:45 pm
Hi Mike,
What Statistics option if any are you using in the Ola index command?
ALL - Update index and column statistics.
INDEX - Update index statistics.
COLUMNS - Update column statistics.
NULL - Do not perform statistics maintenance. This is the default.
September 21, 2016 at 3:31 pm
Oh, my apologies. I didn't see that option. I do now.
So obviously I wouldn't want to run statistics after an index rebuild, but what about the non-index columns--do all of you generally run statistic updates on those on an ongoing basis?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 22, 2016 at 6:38 am
Table statistics are there because at least one query has referenced a column in such a way that the statistic was needed to determine the execution plan. If those statistics are radically out of date and they're used regularly, it will affect the plans generated and will affect performance. I would ensure that they get updated as well.
"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
September 22, 2016 at 6:42 am
Thanks, Grant! That's very helpful.
Mike Scalise, PMP
https://www.michaelscalise.com
September 23, 2016 at 8:29 am
Actually, not to belabor the topic, but would it seem reasonable to update all (non-index) statistics as part of the maintenance plan?
So something like:
1) DBCC CHECKDB
2) Update all non-index statistics (with full scan?)
3) Analyze and either reorganize or rebuild indexes
4) Backups
Does the order make sense and is there a reason I wouldn't want to update any/all statistics if they're out of date?
Thanks again,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 23, 2016 at 9:01 am
Mike Scalise (9/23/2016)
Actually, not to belabor the topic, but would it seem reasonable to update all (non-index) statistics as part of the maintenance plan?So something like:
1) DBCC CHECKDB
2) Update all non-index statistics (with full scan?)
3) Analyze and either reorganize or rebuild indexes
4) Backups
Does the order make sense and is there a reason I wouldn't want to update any/all statistics if they're out of date?
Thanks again,
Mike
I think I'd probably do the backups immediately after the consistency check. They're not in the same realm (in my mind) as index & statistics maintenance. Regardless, have the backup right after the consistency because you could create consistency problems with the index rebuilds. You don't want to back those up.
Stats then indexes is a good order because a rebuild of an index updates the stats using a full scan. A more sophisticated approach would be to do the stats after the indexes, but have a date check to validates how old the statistics are. If they're recent, don't update them.
"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
September 23, 2016 at 9:12 am
Grant Fritchey (9/23/2016)
Mike Scalise (9/23/2016)
Actually, not to belabor the topic, but would it seem reasonable to update all (non-index) statistics as part of the maintenance plan?So something like:
1) DBCC CHECKDB
2) Update all non-index statistics (with full scan?)
3) Analyze and either reorganize or rebuild indexes
4) Backups
Does the order make sense and is there a reason I wouldn't want to update any/all statistics if they're out of date?
Thanks again,
Mike
I think I'd probably do the backups immediately after the consistency check. They're not in the same realm (in my mind) as index & statistics maintenance. Regardless, have the backup right after the consistency because you could create consistency problems with the index rebuilds. You don't want to back those up.
Stats then indexes is a good order because a rebuild of an index updates the stats using a full scan. A more sophisticated approach would be to do the stats after the indexes, but have a date check to validates how old the statistics are. If they're recent, don't update them.
Grant,
Thanks! And good points about when to take the backup and having a process that checks the date of the statistics. So let's say that I put the statistics update after the index rebuild--as long as it checks the date, it wouldn't overwrite the recently-updated full-scan index statistics, which is good, but for the other non-index statistics that it needs to update (because they're old)--would you run the stats update with full scan or let it update them with the default sampling?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 23, 2016 at 10:31 am
If I can afford a full scan, that's better. Absolutely.
"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
September 26, 2016 at 9:29 am
RVSC48 (9/21/2016)
Hi Mike,What Statistics option if any are you using in the Ola index command?
ALL - Update index and column statistics.
INDEX - Update index statistics.
COLUMNS - Update column statistics.
NULL - Do not perform statistics maintenance. This is the default.
RVSC48,
Do you happen to know--Let's say I'm using Ola's index optimize script with "ALL" for the @UpdateStatistics parameter:
1) If it recognizes and rebuilds some indexes, will it skip over these when it goes to update "ALL" statistics because these indexes just got a fresh full set of statistics? So really, "ALL" would be ALL minus the recently-rebuilt index statistics?
Another way of saying it is that I assume it would update the statistics for all of the columns and only the indexes that were re-organized, right?
2) In scenario #1, I believe a default sampling is used for the statistics update. If so, I could include the @StatisticsSample parameter with a value of "100" and it would do a full scan on on the columns and re-organized indexes, right?
Ultimately, I want to see how long it's going to take to intelligently re-organize/rebuild indexes (i.e., not take action if there are fewer than 1000 pages, etc.) and update all of the remaining index and column statistics with fullscan--all without doing additional unnecessary work, like updating statistics for indexes that were just rebuilt. Is Ola's script sophisticated enough to ensure that it doesn't duplicate efforts like this?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 26, 2016 at 10:01 am
1 Yes, if you do it in the same step, else it will look at modification count.
2. https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Just beware that if you have a billion row table and there is one change, then the Ola script will rebuilt the stats(excluding any recent index stats if they were rebuilt) as it doesnt use thresholds for stats, just plain, modcounter>0.
I'ave made a modification to exclude those if the modification count does not exceed 1% the rows in the table, which works for my purposes and massively reduced the stats rebuild window.
Also, for an oltp, you may want to do async stats update, i.e. no waiting while stats are rebuilt as it will leave old stats there till new stats are complete.
IF (@Version >= 10.504000 AND @Version < 11) OR @Version >= 11.03000
BEGIN
SET @CurrentCommand10 = @CurrentCommand10 + 'USE ' + QUOTENAME(@CurrentDatabaseName) + '; IF EXISTS(SELECT * FROM sys.dm_db_stats_properties (@ParamObjectID, @ParamStatisticsID) WHERE unfiltered_rows>0 and 1.0*modification_counter/[rows] > 0.01) BEGIN SET @ParamStatisticsModified = 1 END'
END
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply