June 3, 2009 at 3:41 pm
Hi All,
I am looking for documentation on what happens to existing Statistics when indexes are maintained using "Alter Index Rebuild" or "Alter Index Reorganize".
I have been going around in circles in BOL and MSDN. I find lots of detail on maintaining indexes and statistics but I have not found the connection.
As I recall, the statistics for an index are recomputed when the index is ReBuilt or Reorganized but other statistics (eg column) are not updated by the index maintenance action.
Can you 1) confirm my recollection and hopefully 2) provide a reference:-D
Ray
June 3, 2009 at 3:57 pm
Statistics are updated (with fullscan) for an index when that index is rebuilt. Not when it is reorganised.
Reference: Books Online
http://technet.microsoft.com/en-us/library/ms190397.aspx
Operations such as rebuilding, defragmenting, or reorganizing an index do not change the distribution of data. Therefore, you do not need to update statistics after performing ALTER INDEX REBUILD, DBCC REINDEX, DBCC INDEXDEFRAG, or ALTER INDEX REORGANIZE operations. The query optimizer updates statistics when you rebuild an index on a table or view with ALTER INDEX REBUILD or DBCC DBREINDEX, however; this statistics update is a byproduct of re-creating the index. The query optimizer does not update statistics after DBCC INDEXDEFRAG or ALTER INDEX REORGANIZE operations.
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
June 3, 2009 at 4:10 pm
Thanks Gail,
I was running in circles for sure. 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply