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.
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.