August 6, 2008 at 8:51 am
hi guys i have a job that uses a maintenance plan that does a rebuilt for all my indexes in my DB every day, i also have to do an update in statistics with full scan, but i read that once you do a rebuild then it also does and update in all the statistics with full scan.
is this true?
August 6, 2008 at 8:59 am
I was doing some rebuild of indexes on one of my test systesm yesterday and yes it does update the stats associated with the index.
😉
Mark Johnson
MCP, MCTS Sql Server 2005,MCTS Sql Server 2008, OCP
August 6, 2008 at 9:08 am
If you rebuild the index then it will update the statistics. If you just reorganize them, then the statistics will not be updated.
Regards,
Andras
August 6, 2008 at 9:10 am
Andras Belokosztolszki (8/6/2008)
If you rebuild the index then it will update the statistics. If you just reorganize them, then the statistics will not be updated.Regards,
Andras
And a reference for the above: http://www.sqlskills.com/blogs/paul/2008/01/27/SearchEngineQA10RebuildingIndexesAndUpdatingStatistics.aspx
Regards,
Andras
August 6, 2008 at 9:19 am
You would still need to update the column statistics for the tables, if any. Only statistics for the indexes get updated during the index rebuild.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
August 6, 2008 at 9:25 am
That is true. Now if you have auto statistics turned on and a query touches those columns it would update those statistics at that time. Does that sound correct?
🙂
Mark Johnson
MCP, MCTS Sql Server 2005,MCTS Sql Server 2008, OCP
August 6, 2008 at 9:25 am
To the ones that were rebuilt does it update the statistics with FULL SCAN?
August 6, 2008 at 9:51 am
Based on the article by Paul Randall, it does.
Mark Johnson
MCP, MCTS Sql Server 2005,MCTS Sql Server 2008, OCP
August 6, 2008 at 10:11 am
Now if you have auto statistics turned on and a query touches those columns it would update those statistics at that time. Does that sound correct?
The statistics are updated only if a reasonable amount of modifications have occured to the table. Also, the statistics may be based on a sample of the data, instead of a full scan as required by the OP. Only column statistics for the smaller tables will be updated using a full scan.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
August 6, 2008 at 3:28 pm
thanks!then is not necessary create a separate job for a update statistics with full scan after the rebuild since it is already doing so.
Questions: is using the rebuild in the maintenance subplan wizard good for all indexes in the selected DB?
Should i also leave auto update stats and auto create stats on? if i already have the job to rebuilt the indexes every night?
August 6, 2008 at 4:04 pm
It is not a good idea to update the statistics after an index rebuild. The index rebuild operation has rebuilt, not only the index, but also the statistics.
Ola Hallengren
August 7, 2008 at 1:39 am
I think you need to distinguish between index statistics and column statistics.
Index statistics are those statistics for a specific index, that get generated regardless of the AUTO_UPDATE_STATISTICS setting. These statistics are automatically updated when you perform an index rebuild.
Column statistics are statistics for columns that are not the leading column in any indexes for a table. They are usually generated automatically by SQL Server, when a WHERE predicate references that column, if the AUTO_UDPATE_STATISTICS setting is active. They can also be generated manually. However, they are not automatically updated when you rebuild indexes. They are only updated when a certain threshold of changes have been made to the table (SQL2000) or column (SQL2005), and for large tables, it is likely that only a sample of the rows are used to update the statistics.
So should you leave the AUTO_CREATE_STATISTICS setting on? Yes, if you want SQL Server to create column statistics automatically.
What if you turned off the AUTO_UPDATE_STATISTICS setting?
- you'll need to manually update column statistics.
- your index and column statistics has to be representative of the table data until the next update. If there is a large number of modifications to the table, your statistics may become inaccurate, resulting in sub-optimal execution plans.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply