5

I have an approximately 1 billion row table. I then create an index as follows:

CREATE NONCLUSTERED INDEX [IX_Index1] 
ON [dbo].[MyTable]( [CustomerID] ASC )  -- CustomerID is uniqueidentifier
WITH (DATA_COMPRESSION=PAGE);

This results in a 9.15GB index (compressed), however the transaction log went from empty to 158GB while the index was being created (no other DB activity). Why is the amount of log space used that much higher than the resulting size of the index?

The database is SQLServer 2014 Enterprise in full recovery mode in an availability group.

Update I dropped and recreated the index with the option SORT_IN_TEMPDB = ON. The resulting transaction log writes were 10.4GB for the DB's tlog and 1.27GB for the tempdb tlog.

  • Have you considered temporarily changing the recovery model to BULK_LOGGED during the index create to mitigate log usage and improve index create performance? – Dan Guzman May 18 '16 at 12:28
  • The tlog autogrowth must be set to a % increase as opposed to fixed MB value. % increment is like compound interest. – Kin Shah May 18 '16 at 12:32
  • @DanGuzman yes, we are looking at that. Currently we can't drop below full recovery because we're in an AG. We'd need to drop the AG, do our index builds and recreate the AG. – geofftnz May 19 '16 at 3:23
  • @Kin this is not about the size of the tlog (which is pre-sized), but the amount of tlog that is used compared to the resulting size of the index. I'm assuming this is probably due to the tlog being uncompressed or something like that. – geofftnz May 19 '16 at 3:24
  • I am curious if the page compression is affecting this as well? Anyone have thoughts on that? Or did you try creating the index without compression? – Chris Aldrich May 20 '16 at 13:22
3

Referring to MS documentation here. Also Creating a 60GB Index on SQLFool.

What I believe is happening is that when the index is being built with SORT_IN_TEMDB=OFF, the entire index build processed is logged via the DB's transaction log (DB is set to full recovery). (In addition, I expect there to be a high number of page splits due to the scattergun-nature of the GUID column that is being indexed)

When the index is built using SORT_IN_TEMPDB=ON, then most of the transaction log activity is going to be against tempdb which is in SIMPLE recovery mode.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.