THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: What gets logged for index rebuild operations?

This blog post was inspired by a question from a future student. Someone who was already booked for my SQL Server Internals class in June asked for some information on a current problem he was having with transaction log writes causing excessive wait times during index rebuild operations when run in ONLINE mode. He wanted to know if switching to BULK_LOGGED recovery could help.

I knew the difference between ALTER INDEX in FULL vs BULK_LOGGED recovery when doing normal OFFLINE rebuilds, but I wasn't sure about ONLINE. So I dug out some old scripts, massaged them a bit, and ran some tests.

A minimally logged operation is one that does not always log every single row. In FULL mode, SQL Server will log the entire page as each page is filled, so that we end up with individual  log records of over 8K. In BULK_LOGGED mode, SQL Server just logs the fact that a new page was generated (and some information about the page) and doesn't log the full 8K. So the log space used is quite a bit less.

I will use Microsoft's AdventureWorks database that you can download from codeplex. I used the SQL Server 2005 version.

After making sure the database is set to FULL recovery, I make a copy of the Sales.SalesOrderDetail table (which I call Details) and build a clustered index on it.  The table has about 1580 pages and 121,317 rows.


ALTER DATABASE AdventureWorks SET RECOVERY FULL
GO
USE AdventureWorks;
GO
IF EXISTS (SELECT 1 FROM sys.tables    
            WHERE name = 'Details')
       DROP TABLE Details;
      
 
SELECT * INTO Details
FROM Sales.SalesOrderDetail;
GO


CREATE CLUSTERED INDEX CL_details_indx ON Details(SalesOrderID);
GO

I then build a table to store log information that I will gather after the different index rebuild operations. After that I back up the database to make sure I have a base for making log backups.

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'AlterIndexLoggingData')
    DROP TABLE AlterIndexLoggingData;
GO

CREATE TABLE AlterIndexLoggingData
(DBRecModel CHAR(12), IndexBuildMode CHAR(3), MaxLogRowSize INT, SumLogSize BIGINT, NumLogRows INT, NumLargeRows INT);
GO
BACKUP DATABASE AdventureWorks TO DISK = 'c:\backups\AW.bak' WITH INIT;
GO

Now starts the testing. There are four sections, each of which basically does the same thing, with the four permutations of FULL vs BULK_LOGGED recovery, and ONLINE vs OFFLINE index builds.  Each section backs up the transaction log to truncate it, and then reports the number of rows in the log, just to verify we really have (logically) cleared it out. It then performs a rebuild of the index on the Details table. Finally, it captures the following information from the log using fn_dblog:

  • Max log record size
  • Sum of log record sizes
  • Number of log records
  • Number of log records larger than 8K

----  FULL Recovery, ONLINE = OFF

BACKUP LOG AdventureWorks TO DISK = 'c:\backups\AW_log.bak' WITH INIT;
SELECT COUNT(*) FROM fn_dblog(null, null);
GO
ALTER INDEX  CL_details_indx  ON Details REBUILD
GO

INSERT INTO AlterIndexLoggingData
SELECT 'FULL', 'OFF',
        MAX([Log Record Length]),
        SUM([Log Record Length]),
        COUNT(*),
        (SELECT COUNT(*) FROM fn_dblog(null, null) WHERE [Log Record Length]  > 8000)
        FROM fn_dblog(null, null);
GO
 
 
--- FULL Recovery, ONLINE = ON


BACKUP LOG AdventureWorks TO DISK = 'c:\backups\AW_log.bak' WITH INIT;
SELECT COUNT(*)  FROM fn_dblog(null, null);
GO

ALTER INDEX  CL_details_indx  ON Details REBUILD
WITH (ONLINE = ON)
GO

INSERT INTO AlterIndexLoggingData
SELECT 'FULL', 'ON',
        MAX([Log Record Length]),
        SUM([Log Record Length]),
        COUNT(*),
        (SELECT COUNT(*) FROM fn_dblog(null, null) WHERE [Log Record Length]  > 8000)
        FROM fn_dblog(null, null);
GO
--SELECT * FROM AlterIndexLoggingData;
GO

--- BULK_LOGGED Recovery; ONLINE = OFF


ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED;
BACKUP LOG AdventureWorks TO DISK = 'c:\backups\AW_log.bak' WITH INIT;
SELECT COUNT(*)  FROM fn_dblog(null, null);
GO

ALTER INDEX  CL_details_indx  ON Details REBUILD
GO

INSERT INTO AlterIndexLoggingData
SELECT 'BULK_LOGGED', 'OFF',
        MAX([Log Record Length]),
        SUM([Log Record Length]),
        COUNT(*),
        (SELECT COUNT(*) FROM fn_dblog(null, null) WHERE [Log Record Length]  > 8000)
        FROM fn_dblog(null, null);
GO
--SELECT * FROM AlterIndexLoggingData;
GO

--- BULK_LOGGED Recovery, ONLINE = ON


BACKUP LOG AdventureWorks TO DISK = 'c:\backups\AW_log.bak' WITH INIT;
SELECT COUNT(*)  FROM fn_dblog(null, null);
GO

ALTER INDEX  CL_details_indx  ON Details REBUILD WITH (ONLINE = ON)
GO

INSERT INTO AlterIndexLoggingData
SELECT 'BULK_LOGGED', 'ON',
        MAX([Log Record Length]),
        SUM([Log Record Length]),
        COUNT(*),
        (SELECT COUNT(*) FROM fn_dblog(null, null) WHERE [Log Record Length]  > 8000)
        FROM fn_dblog(null, null);
GO
SELECT * FROM AlterIndexLoggingData;
GO

Here are my results:

image

Notice the following:

  • Only FULL recovery with an OFFLINE rebuild logged the 8K pages.
  • FULL recovery with an ONLINE rebuild actually did log every single row; in fact, it looks like at least two log records were generated for each row. There is a LOT of logging for this set of options and by far, the largest amount of log space used.
  • BULK_LOGGED mode used substantially less log space than FULL, but the difference between ONLINE and OFFLINE is even more pronounced. In FULL recovery, ONLINE used about twice the log space, in BULK_LOGGED, the logging required more than 8 times the space!

I'm sure there are more fun facts you can pull out of the results from fn_dblog, but for my first look at the logging differences between ONLINE and OFFLINE index rebuilds, this is what I wanted to know. It's not a complete answer to the student's question, but it does indicate there is a lot more logging with ONLINE index builds, so it should not be surprising if there are a lot more log write waits. And I would assume that switch to BULK_LOGGED recovery could make a noticeable difference.

I hope this is useful!

~Kalen

Published Tuesday, March 08, 2011 11:54 AM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jay said:

Hi Kalen, Thanks for the excellent post, the [Log Record Length] does it return the size in bytes or is that the number of 8k pages?

Thanks

March 9, 2011 3:23 PM
 

Kalen Delaney said:

Hi Jay

The size is in bytes, which is why I was looking for log records > 8000. That would be records that log the entire page. I wouldn't be looking for log records that are greater than 8000 pages!

HTH

Kalen

March 10, 2011 12:05 PM
 

Kalen Delaney said:

Last week, I wrote about logging for index rebuild operations. I wanted to publish the result of that

March 15, 2011 3:46 PM
 

Chowdhury said:

Karen,You won't believe I attended many trainings just to learn this basic that the rebuilding index online will require a huge log space and bulk-looged can be a solution.Unfortunately, in the training they don't explian this kind of thing.Thanks Karen.

July 13, 2011 11:00 PM
 

Kalen Delaney said:

Hi Chowdhury

(My name is Kalen, not Karen.)

As I said in the post, I don't consider this basic information, as I just discovered it right before I made this blog post. I have been teaching some of the most advanced SQL Server classes in the world for over a decade, and didn't realize this. So I am not surprised that the other training you took did not mention it either.

SQL Server is a HUGE product. There is no way someone can know everything about it. In my classes, I try to teach how I discover behaviors like this, so that my students can continue to learn on their own, after the class is over.

Best regards,

Kalen

July 15, 2011 7:54 PM
 

Rama Mathanmohan said:

Have you done the same testing for "Simple" Recovery Model ?

January 27, 2012 5:14 AM
 

Kalen Delaney said:

Rama

I have done lots of testing and written blog posts that include information about simple, which in general, performs the same logging as bulk_logged. The main difference is that the log is truncated regularly so there is less chance it will grow, and you cannot make log backups.

It would be very straightforward for you to run my scripts after changing your recovery model to SIMPLE.

Best regards,

Kalen

January 27, 2012 12:22 PM
 

Andy said:

This is very helpful Kalen! Thanks!

October 2, 2012 2:51 PM
 

Jeff Keller said:

Hi Kalen,

   Great article.  Was this on SQL Server 2008 with the 2005 AdventureWorks DB ?

I didn't realize until recently that online reindexes in SQL Server 2005 are minimally logged regardless of the recovery model (according to MS - I haven't verified personally).  I'd be curious if you got results to that effect running the same scripts/same tables on SQL Server 2005.

http://support.microsoft.com/kb/2407439

December 20, 2012 11:28 AM
 

Kalen Delaney said:

Hi Jeff

Yes, this was run on SQL 2008 with the 2005 AW database. I do not have a 2005 instance to test this on.

Thanks

Kalen

December 20, 2012 6:03 PM
 

Rajeev said:

Is there any advantage in performance if we switch to Bulk-recovery model and do an index rebuild?

February 13, 2013 1:00 AM
 

tobi said:

Might be worth measuring the actual log size by backing it up. That will then include the bulk logged pages which are not visible right now.

March 18, 2013 6:13 AM
 

Yagnesh said:

As far as I know,  Nasty corruption problems happened in minimum logging of online rebuild of indexes in SQL 2005 and hence they  implemented full logging under the cover for online rebuilding of indexes in all recovery modes.

May 15, 2013 12:18 PM
 

Satya. said:

Hi Kalen,

Which version is supported to do the online indexing?

June 14, 2013 12:08 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement