Reorganizing a Large Index from the SQL Management Studio GUI
It takes a ...long... time.
Just tried reorganizing the clustered index on a table with about 5.5 GB of data in it. I'm at about 90 minutes and counting...
Rebuilding the clustered index and two non-clustered indexes (1.3GB and 2.5GB) through the command line took about 10 minutes.
Mine is sort of a diabolical situation, but still.
I have a table with lots of rows, and the average record is about 110 bytes. So there are about 73 (8060/110, where 8060 is the non-header portion of a data page) records on a data page. I removed an unused variable character field, which should save 2 bytes per record.
Strangely enough, a DBCC SHOWCONTIG before and after dropping this field shows exactly the same thing. I had to think about that for a minute, but it makes sense. SQL Server just removes the field from the schema, but makes no actual changes to the underlying data. So it happens very fast. And DBCC SHOWCONTIG is likely calculating its results from sys.allocation_units and sys.partitions, which would not necessarily get updated from a schema change.
By removing my one varchar field, the average record size is now 108 bytes, which would allow about 74 records on each data page. The table in question happens to have 700,000+ pages in it. And each page has very low fragmentation.
If I were doing a REBUILD, SQL Server would essentially copy the entire table into a new area, then swap it into the old table's place. By default, this is an offline operation.
However, a REORGANIZE is an online operation, and is done in-place. Each leaf-level page is defragmented, then more records are added, if possible. So, in my situation, it will do something like this:
- go to first page
- defrag it
- go get some records from the next page to fill up this page
- go to next page
- if the page is empty, de-allocate it and go to next page
- go to step 2
The good news about this, is that it is an online operation, and can be stopped at any time without a huge rollback.
The bad news is that in my situation, SQL Server has a lot of work to do because every record is fragmented - 2 bytes have been removed. Which means every page is fragmented. And every page will need some new records to fill it up.
Not sure how much of this is the GUI, and how much is the REORGANIZE. But unfortunately, I can't seem to stop the REORGANIZE from the GUI.
Also, it's nice to be able to see the elapsed seconds in the lower right corner of the screen when you do things from the command line. With the GUI, you can't see how long it is taking.
Hopefully, this will be done by tomorrow morning.
