Sql-server – Index creation leads to large transaction log growth

indexsql serversql server 2014transaction-log

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.

Best Answer

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.

Related Solutions

Sql-server – Identify cause of unusual transaction log growth

This SQL shows queries with most "logical_writes":

SELECT TOP 100
    st.text,
    execution_count,
    total_elapsed_time,
    total_worker_time,
    total_logical_reads,
    total_logical_writes,
    total_physical_reads,
    total_clr_time,
    creation_time, last_execution_time,
    pl.query_plan
FROM sys.dm_exec_query_stats ps with (NOLOCK)
    Cross Apply sys.dm_exec_sql_text(ps.sql_handle) st
    Cross Apply sys.dm_exec_query_plan(ps.plan_handle) pl
ORDER BY total_logical_writes desc
OPTION (RECOMPILE);

This T-SQL could help to find direction, but it is not really what you asked for, as logical write != write. and logical write != log write.

It could be very very interesting to find more complete answer to your question.

Sql-server – Encountered unexpected growth Transaction log file size

How to reduce the size of transaction log file without shrink process (Because it’s Bad – Increases Fragmentation – Reduces Performance)

That is not true. log shrink is quite benign, you are thinking data shrinks. See How to shrink the SQL Server log for an explanation why it grows, how to shrink it and why is benign.

My first recommendation is to use a smart index maintenance plan. Overactive rebuild is expensive, harmfull and completely unnecessary. Many swear by Ola Hallengren's index maintenance scripts.

You must also look into leveraging minimal logging. Index maintenance are a prime candidate for minimally logged operations, but you must use enable them by using the bulk-logged recovery model for your database. See Operations That Can Be Minimally Logged:

If the database is set to the simple or bulk-logged recovery model, some index DDL operations are minimally logged whether the operation is executed offline or online.

Best Answer

Related Solutions

Sql-server – Identify cause of unusual transaction log growth

Sql-server – Encountered unexpected growth Transaction log file size

Related Question