Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
frodel
Starting Member
1 Post |
 Posted - 2009-02-23 : 09:45:54
|
| I'm setting up a Maintenance Plan in SQL 2005 to speed up the indexes on all tables.EXEC sp_MSforeachtable "DBCC DBREINDEX('?', '', 90)"EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'Is there any point in running the UPDATE STATISTICS after running DBREINDEX?Frode |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-23 : 09:47:04
|
quote:
Originally posted by frodel
I'm setting up a Maintenance Plan in SQL 2005 to speed up the indexes on all tables.EXEC sp_MSforeachtable "DBCC DBREINDEX('?', '', 90)"EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'Is there any point in running the UPDATE STATISTICS after running DBREINDEX?Frode
NO need to update stats after rebuilding index. |
 |
|
|
Holger
Starting Member
2 Posts |
Posted - 2009-02-23 : 10:33:14
|
| Hi Frodel,every index has one statistic correlated with it. Whenever you rebuild (not reorganize!) an index, the regarding statistic is also updated - based on ALL index data. update statistics on the other hand, does only pull out some sample data out of your table for updating of statistics. So the statistics updated during index rebuild is general of higher quality, because it is based on more data.But this of course only holds true for statistics that have a regarding index. Usually our database will have a lot more statistics included, so an update statistics absolutely makes sense. SQL Server can do this automatically, when you turn on the database option for AUTO_STATISTICS (see bol). Manual update of statistics during periodic maintenance may however is usefull, because the build in mechanism only will perform an auto update when approximately 20% of your statistic relevant date have changed (roughly spoken).I' suggest that you simply change the order of your two steps: First do the update statistics and rebuild the index afterwards. This will update the index-related statistics twice, but normally you can afford the extra time, needed for this.Regards,Holger |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-23 : 10:37:40
|
quote:
Originally posted by Holger
Hi Frodel,every index has one statistic correlated with it. Whenever you rebuild (not reorganize!) an index, the regarding statistic is also updated - based on ALL index data. update statistics on the other hand, does only pull out some sample data out of your table for updating of statistics. So the statistics updated during index rebuild is general of higher quality, because it is based on more data.But this of course only holds true for statistics that have a regarding index. Usually our database will have a lot more statistics included, so an update statistics absolutely makes sense. SQL Server can do this automatically, when you turn on the database option for AUTO_STATISTICS (see bol). Manual update of statistics during periodic maintenance may however is usefull, because the build in mechanism only will perform an auto update when approximately 20% of your statistic relevant date have changed (roughly spoken).I' suggest that you simply change the order of your two steps: First do the update statistics and rebuild the index afterwards. This will update the index-related statistics twice, but normally you can afford the extra time, needed for this.Regards,Holger
Updating stats before Rebuild index doesn't make sense. Remember,When you rebuild index,you are also doing UPDATE STATS with FULL SCAN.SO REMOVE that step. Well you can manually use sp_updatestats in between timeframe to update the needed stats if you data are constantly changing with DML operation. |
|
|
|
Holger
Starting Member
2 Posts |
Posted - 2009-02-23 : 11:28:43
|
| Frodel, sodeep,please keep in mind that update statistics (resp. sp_updatestats), like used in the provided script, also does an update for statistics that do not have a regarding index. By all means, it makes sense to keep those statistics up to date, too. An index rebuild does NOT update those statistics, so you should let SQL Server do this (AUTO_STATISTICS ON) or do it on your own (UPDATE STATISTICS) or use a combination of the two, which is what I'd suggest.Regards,Holger |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-23 : 12:11:37
|
quote:
Originally posted by Holger
Frodel, sodeep,please keep in mind that update statistics (resp. sp_updatestats), like used in the provided script, also does an update for statistics that do not have a regarding index. By all means, it makes sense to keep those statistics up to date, too. An index rebuild does NOT update those statistics, so you should let SQL Server do this (AUTO_STATISTICS ON) or do it on your own (UPDATE STATISTICS) or use a combination of the two, which is what I'd suggest.Regards,Holger
Please read booksonline before you post.Without knowing, you are just blindly answering. |
|
|
|
gigiduru
Starting Member
3 Posts |
Posted - 2009-03-24 : 07:25:50
|
quote:
Originally posted by sodeep
quote:
Originally posted by Holger
Frodel, sodeep,please keep in mind that update statistics (resp. sp_updatestats), like used in the provided script, also does an update for statistics that do not have a regarding index. By all means, it makes sense to keep those statistics up to date, too. An index rebuild does NOT update those statistics, so you should let SQL Server do this (AUTO_STATISTICS ON) or do it on your own (UPDATE STATISTICS) or use a combination of the two, which is what I'd suggest.Regards,Holger
Please read booksonline before you post.Without knowing, you are just blindly answering.
Holger is right. Update statistics touches all the other statistics that might have been created automatically by SQL if the auto create statistics option is on. So it is quite different from a rebuild indexes job as it does not touch only indexes. |
|
|
|
|
|
|
|
|
Update statistics after dbreindex?