August 15, 2016 at 3:59 am
Do we still need to do this after the index was rebuilt ?
Thanks .
August 15, 2016 at 4:00 am
Yes, but column statistics only, not index statistics (they got updated with the index rebuild)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 16, 2016 at 3:33 am
But only update the column statistics if the automatic statistics updates are not adequate for maintaining them.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 16, 2016 at 9:13 am
I've always been a little fuzzy on this one - So, is just executing sp_updatestats a safe bet after indexing? Realizing that rebuilildng indexes will only affect the stats for those indexes that get rebuilt, is that sufficient? The sp_updatestats determines which ones need updating anyway, correct?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 16, 2016 at 10:52 am
MyDoggieJessie (8/16/2016)
I've always been a little fuzzy on this one - So, is just executing sp_updatestats a safe bet after indexing? Realizing that rebuilildng indexes will only affect the stats for those indexes that get rebuilt, is that sufficient? The sp_updatestats determines which ones need updating anyway, correct?
Well, sp_updatestats has a sophisticated algorithm that it uses to determine if statistics should be updated. If the rowmod counter is > 0, meaning, if one row has been touched, sp_updatestats will update the statistic in question.
So imagine this scenario. You rebuild the index, which means that you get a full scan set of statistics, about as accurate a set of statistics you're going to get. After that index rebuild is done, you rebuild several other indexes. In the mean time, a single row is inserted/updated/deleted so that the rowmod counter goes up. After rebuilding all the other indexes, you run sp_updatestats. This then proceeds to replace your statistics that have just been created using a full scan with a set of sampled statistics, which, by their nature, are generally less accurate than the full scan statistics.
In short, no, you can't both rebuild indexes and then run sp_updatestats with any assurance that you're not actively harming your performance.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 16, 2016 at 11:32 am
Grant Fritchey (8/16/2016)
MyDoggieJessie (8/16/2016)
I've always been a little fuzzy on this one - So, is just executing sp_updatestats a safe bet after indexing? Realizing that rebuilildng indexes will only affect the stats for those indexes that get rebuilt, is that sufficient? The sp_updatestats determines which ones need updating anyway, correct?Well, sp_updatestats has a sophisticated algorithm that it uses to determine if statistics should be updated. If the rowmod counter is > 0, meaning, if one row has been touched, sp_updatestats will update the statistic in question.
So imagine this scenario. You rebuild the index, which means that you get a full scan set of statistics, about as accurate a set of statistics you're going to get. After that index rebuild is done, you rebuild several other indexes. In the mean time, a single row is inserted/updated/deleted so that the rowmod counter goes up. After rebuilding all the other indexes, you run sp_updatestats. This then proceeds to replace your statistics that have just been created using a full scan with a set of sampled statistics, which, by their nature, are generally less accurate than the full scan statistics.
In short, no, you can't both rebuild indexes and then run sp_updatestats with any assurance that you're not actively harming your performance.
I though the number of row mods needed was based on the number of rows in the table, and was either a fixed number or a percentage of the total rows, but not a single row (unless perhaps a temp table or a table that had zero rows before).
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 16, 2016 at 11:57 am
Gotcha, so it would make more sense to run the stats before the index rebuilds...if use a more concentrated/targeted process before the indexing rebuilds even take place...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 16, 2016 at 11:59 am
ScottPletcher (8/16/2016)
Grant Fritchey (8/16/2016)
MyDoggieJessie (8/16/2016)
I've always been a little fuzzy on this one - So, is just executing sp_updatestats a safe bet after indexing? Realizing that rebuilildng indexes will only affect the stats for those indexes that get rebuilt, is that sufficient? The sp_updatestats determines which ones need updating anyway, correct?Well, sp_updatestats has a sophisticated algorithm that it uses to determine if statistics should be updated. If the rowmod counter is > 0, meaning, if one row has been touched, sp_updatestats will update the statistic in question.
So imagine this scenario. You rebuild the index, which means that you get a full scan set of statistics, about as accurate a set of statistics you're going to get. After that index rebuild is done, you rebuild several other indexes. In the mean time, a single row is inserted/updated/deleted so that the rowmod counter goes up. After rebuilding all the other indexes, you run sp_updatestats. This then proceeds to replace your statistics that have just been created using a full scan with a set of sampled statistics, which, by their nature, are generally less accurate than the full scan statistics.
In short, no, you can't both rebuild indexes and then run sp_updatestats with any assurance that you're not actively harming your performance.
I though the number of row mods needed was based on the number of rows in the table, and was either a fixed number or a percentage of the total rows, but not a single row (unless perhaps a temp table or a table that had zero rows before).
Straight from the code for sys.sp_updatestats:
if ((@ind_rowmodctr is null) or (@ind_rowmodctr <> 0) or ((@is_ver_current is not null) and (@is_ver_current = 0)))
And you can see the query used to retrieve the @ind_rowmodctr value:
select name, indid, rowmodctr from sys.sysindexes
where id = @table_id and indid > 0 and indexproperty(id, name, 'ishypothetical') = 0
and indexproperty(id, name, 'iscolumnstore') = 0
order by indid
And it isn't manipulated in any other location throughout the rest of the code. Since rowmodctr is just an incremental value, one modification and you have a value that is <>0. No calculations at all are in the code that I can see.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 16, 2016 at 12:02 pm
MyDoggieJessie (8/16/2016)
Gotcha, so it would make more sense to run the stats before the index rebuilds...if use a more concentrated/targeted process before the indexing rebuilds even take place...
Yeah, pretty much. This stuff isn't easy. Although, for most of us, most of the time, the easy way works fine.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 16, 2016 at 12:14 pm
I though the number of row mods needed was based on the number of rows in the table, and was either a fixed number or a percentage of the total rows, but not a single row (unless perhaps a temp table or a table that had zero rows before).
I think your thinking of the algorithm for auto update stats. It works different than the logic used in sp_updatestats.
Sue
August 16, 2016 at 12:20 pm
Also worth mentioning is that the rowmodctr value after 2005 is just an estimate. Microsoft says it will be, something along the lines of, "mostly the same values" as it was in 2005 and earlier. So, while it may be a somewhat inaccurate number, it's the one that most of us will use to understand what's changed. Jason Strate has a more accurate calculation on his blog if you really need to track down a more precise count of rows that have been modified.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 16, 2016 at 11:04 pm
Heh... I keep it simple. I've not done any index maintenance on my "money maker" box since 18 Jan 2016 and performance has actually and significantly improved. I do, however, rebuild statistics.
--Jeff Moden
August 17, 2016 at 12:05 am
Hey Jeff - care to share your job script for the stats?
😀
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 17, 2016 at 12:12 am
Thank you for the feedback. It's much appreciated. One question though : Is it possible to replace Index rebuild with update statistics - fullscan?
August 17, 2016 at 2:55 am
ScottPletcher (8/16/2016)
I though the number of row mods needed was based on the number of rows in the table, and was either a fixed number or a percentage of the total rows, but not a single row (unless perhaps a temp table or a table that had zero rows before).
The fixed number/percentage is used for the automatic stats update. sp_updatestats (the procedure) will update the stats if more than 0 rows have changed since the last stats update.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply