Originally posted 2011-09-09 21:27:00. Republished by Blog Post Promoter

Over the past few months, I’ve been doing a lot of work with the table and index compression introduced in SQL Server 2008. If you’ve worked with this stuff much, you know that, in an existing database, it involves index rebuilds – lots and lots of index rebuilds. In my case, that also means lots of waiting, because we have some very large tables. This is truly a case where “start it and go get coffee” applies. The question, however, is how long do you have to get that coffee? I spent enough time waiting around that I decided to try to answer that question, and I found that the answer is lurking in the sys.partitions table.

Assume that we have a table named ExamRegistration, with multiple indexes, one of which is named IX_ExamRegistration_Disposition:

The sys.partitions table can tell us some additional things about this index, such as the number of rows, and its compression status:

SELECT *
    FROM sys.partitions
    WHERE OBJECT_ID = OBJECT_ID('dbo.ExamRegistration')
        AND index_id = INDEXPROPERTY(OBJECT_ID('dbo.ExamRegistration'), 'IX_ExamRegistration_Disposition', 'IndexID')

From this, we can see that this index contains just over 5 million rows, and is currently not compressed. We’re going to compress it, and use the sys.partitions table to track the progress of that compression effort.

ALTER INDEX IX_ExamRegistration_Disposition ON dbo.ExamRegistration REBUILD
            WITH (ONLINE=ON, DATA_COMPRESSION=PAGE)

Before we proceed, I’d like to make a comment on this ALTER statement. Note that it is doing an ONLINE rebuild. I do the vast majority of my index rebuilds ONLINE, allowing me to work while the system is live, with minimal disruption to the users. It’s also important to note that this sys.partitions trick won’t work with OFFLINE rebuilds. The rebuild places locks that prevent querying the sys.partitions table. With that said, let’s move on.

If we re-run the sys.partitions query while the index rebuild is taking place, we’ll see something interesting:

<span style="font-family: Consolas, Monaco, monospace; font-size: 12px;">SELECT *</span>
    FROM sys.partitions
    WHERE OBJECT_ID = OBJECT_ID('dbo.ExamRegistration')
        AND index_id = INDEXPROPERTY(OBJECT_ID('dbo.ExamRegistration'), 'IX_ExamRegistration_Disposition', 'IndexID')

There are now TWO rows in sys.partitions for our index! Notice the difference in the row counts – this is how we can track the progress of the index rebuild. As the rebuild progresses, the row count of the new index will increase until it matches the original index. Once the rebuild completes, the original index is dropped, and sys.partitions will again return only one row, but that row now shows us the compressed index:

The post SYS.PARTITIONS: Better Than X-Ray Vision appeared first on RealSQLGuy.