DBCC IndexDefrag & DBCC DBReIndex are deprecated features in the next version of SQL Server. This blog has a look at some performance differences of these features against the replacement features.
Post
DBCC IndexDefrag has been the command that most of you will know of and use to defragment your indexes. The good people at Microsoft advise you when they are going to deprecate an existing feature and this command is no different. In Books On Line (BOL) 2008 R2 as part of SQL Server 2008 R2, apart from informing you what the feature does it also states “This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Alter Index instead”. With this in mind and with the next version of SQL Server, Code Name – Denali, just around the corner I figured it would be worthwhile doing some testing for you on these features.
To do some worthwhile testing with some worthwhile results I have created a test Database, containing 4 tables, with a Clustered Index (CI) and some NCI’s on each of the 4 tables. The tables have been loaded up with some random data generated using Visual Studio 2010. To read more how I went about generating this random data read my blog on SQL Server 2008 R2 Test Data Load Using VS2010. Each table has been loaded with 4,000,000 rows making the database just under 10Gb for the data file. Not a large Database by any means but gives a good account of itself for what we are doing for this blog.
So as to be able to get comparable results I have taken a backup of the database prior to undertaking either of the defragmentation approaches. Before starting any of the approaches – DBCC IndexDefrag or Alter Index ReOrganise, DBCC DBReIndex or Alter Index Rebuild the fragmentation levels for the 4 tables NCI’s are as can be seen below:
The first round of testing taken against the fragmented indexes as seen above was running the DBCC IndexDefrag command against those Indexes which were heavily fragmented. Normally with the level of Fragmentation I would not undertake a Defrag but would perform an Index Rebuild (DBCC DBReIndex) discussed later in this blog.
With running DBCC IndexDefrag statements against those Tables & Indexes highlighted above with high fragmentation the outcomes and performance of the statements a indicated below:
| Table | Index | Old Fragmentation | New Fragmentation | CPU Time (ms) | Elapsed Time (ms) |
| Table_A | IX_Table_A_NCI_Column1 | 99.309 | .512 | 5741 | 40540 |
| IX_Table_A_NCI_Column2 | 99.237 | .626 | 8237 | 110839 | |
| Table_B | IX_Table_B_NCI_Column1 | 99.249 | .549 | 6474 | 42594 |
| IX_Table_B_NCI_Column2 | 99.241 | 1.66 | 13151 | 249447 | |
| Table_C | IX_Table_C_NCI_Column1 | 99.186 | .461 | 15194 | 459587 |
| IX_Table_C_NCI_Column2 | 99.135 | .709 | 8830 | 112977 |
Alter Index Reorganise is the new feature command that you should be using and upgrading any of your existing code to use. In comparison to the old DBCC IndexDefrag command the results are as follows:
| Table | Index | Old Fragmentation | New Fragmentation | CPU Time (ms) | Elapsed Time (ms) |
| Table_A | IX_Table_A_NCI_Column1 | 99.309 | .512 | 3604 | 8649 |
| IX_Table_A_NCI_Column2 | 99.237 | .626 | 5647 | 25496 | |
| Table_B | IX_Table_B_NCI_Column1 | 99.249 | .549 | 4181 | 6995 |
| IX_Table_B_NCI_Column2 | 99.241 | 1.664 | 10031 | 101719 | |
| Table_C | IX_Table_C_NCI_Column1 | 99.186 | .461 | 12262 | 190518 |
| IX_Table_C_NCI_Column2 | 99.135 | .709 | 6053 | 12135 |
Analysing the results of both the DBCC IndexDefrag and the Alter Index ReOrganize the percentage of fragmentation after the command was run is the same but there is a big difference in the amount of time to complete the operation, nearly 1/3 of the time required to complete the old command.
DBCC IndexDefrag – 1015984 ms (1,016 secs or just under 17 minutes to complete)
Alter Index ReOrganize – 345512 ms (346 secs or just under 6 minutes to complete)
DBCC DBReIndex just like mentioned at the start of this blog is being deprecated. The results for this command are up for comparison against the Alter Index Rebuild command. Like the tests above the initial start point is as per the second screenshot above.
| Table | Index | Old Fragmentation | New Fragmentation | CPU Time (ms) | Elapsed Time (ms) |
| Table_A | IX_Table_A_NCI_Column1 | 99.309 | .055 | 11170 | 15250 |
| IX_Table_A_NCI_Column2 | 99.237 | .028 | 54209 | 17641 | |
| Table_B | IX_Table_B_NCI_Column1 | 99.249 | .040 | 24414 | 186370 |
| IX_Table_B_NCI_Column2 | 99.241 | .010 | 87002 | 31986 | |
| Table_C | IX_Table_C_NCI_Column1 | 99.186 | .010 | 93085 | 83810 |
| IX_Table_C_NCI_Column2 | 99.135 | .024 | 56863 | 21553 |
The DBCC DBReindex command in this instance has taken less time and produced better fragmentation levels than the DBCC IndexDefrag command.
Alter Index Rebuild is the new feature command that you should be using and upgrading any of your existing code to use. In comparison to the old DBCC DBReIndex command the results are as follows:
| Table | Index | Old Fragmentation | New Fragmentation | CPU Time (ms) | Elapsed Time (ms) |
| Table_A | IX_Table_A_NCI_Column1 | 99.309 | .055 | 5741 | 7573 |
| IX_Table_A_NCI_Column2 | 99.237 | .028 | 19391 | 18006 | |
| Table_B | IX_Table_B_NCI_Column1 | 99.249 | .040 | 6225 | 47538 |
| IX_Table_B_NCI_Column2 | 99.241 | .010 | 96316 | 43260 | |
| Table_C | IX_Table_C_NCI_Column1 | 99.186 | .010 | 154877 | 95009 |
| IX_Table_C_NCI_Column2 | 99.135 | .024 | 12886 | 6068 |
DBCC DBReIndex – 356610 ms (357 secs or just under 6 minutes to complete)
Alter Index Rebuild – 217454 ms (218 secs or just under 4 minutes to complete)
Analysing the results of both the DBCC DBReIndex and the Alter Index Rebuild the percentage of fragmentation after the command was run is the same but there is a big difference in the amount of time to complete the operation, nearly 1/3 of the time required to complete the old command.
What I have not covered for you in this blog is that the new feature does allow you to use many options to help improve the performance of you processing depending on the size of your databases and the setup of your Instances. The testing for this blog was undertaken on my laptop so no split in data files, log files, TempDB files to help with performance. Also I did not use any options with the commands. I hope this has given you some insight into the improved performance that you can expect from these new features over the old ones and gets you thinking about changing your current scripts.
Leave a Reply