Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Index Fragmentation Expand / Collapse
Posted Friday, October 21, 2011 5:01 AM



Group: General Forum Members
Last Login: Monday, August 22, 2016 2:03 PM
Points: 2,053, Visits: 1,709
You've fallen into the classic trap of not filtering by allocation unit type - any LOB_DATA or ROW_OVERFLOW_DATA allocation units with fragmentation will trigger index rebuilds/reorganizes even if the IN_ROW_DATA allocation unit has no fragmentation. You need to update the script to handle that.

Paul Randal
CEO, Check out SQLskills online training! Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #1194263
Posted Friday, October 21, 2011 2:17 PM


Group: General Forum Members
Last Login: Thursday, August 18, 2016 2:14 PM
Points: 1,604, Visits: 447
I also think you can skip the update statistics after a rebuild based on BOL topic Using Statistics to Improve Query Performance, "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. "

Typical MS. First we are told you do not need to update the statistics after a rebuild since the data distribution has not changed but then we are told the rebuild causes the statistics to be updated.

HTH -- Mark D Powell --
Post #1194682
Posted Thursday, May 12, 2016 6:57 AM


Group: General Forum Members
Last Login: Yesterday @ 5:26 AM
Points: 7,969, Visits: 785
Thanks for the script.
Post #1785627
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse