Alter Index Vs DBCC IndexDefrag Vs DBCC DBReIndex

Views 16029

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.

clip_image001

 

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:

clip_image002

 

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

clip_image003

 

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

clip_image004

 

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

clip_image005

 

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

clip_image006

 

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

Your email address will not be published. Required fields are marked *

Warwick

I am a Microsoft Data Platform MVP as well as a Microsoft Certified Master working as the Principal Consultant here at SQL Masters Consulting. When I am not working with the SQL Server Stack I like to get away to the Snow and spend time Snowboarding.

Search