A couple of days ago, I encountered an interesting case with a customer concerning a rebuild index operation on a datawarehouse environment. Let me introduce the situation: a “usual DBA day” with an almost usual error message found in your dedicated mailbox: “The transaction log for database ‘xxx’ is full”. After checking the concerned database, I notice that its transaction log has grown up and has fulfilled the entire volume. In the same time, I also identify the root cause of our problem: an index rebuild operation performed last night that concerns a big index (approximately 20 GB in size) on a fact table. On top of all, the size of the transaction log before raising the error message was 60 GB.

As you know, on datawarehouse environment, the database recovery model is usually configured either to SIMPLE or BULK_LOGGED to minimize write operations of bulk activity and of course the concerned database meets this requirement. According to the Microsoft document we could expect to get minimally logged records for index rebuild operations (ALTER INEX REBUILD) regardless the offline / online mode used to rebuild the index. So why the transaction log has grown heavily in this case?

To get a response we have first to take a look at the rebuild index tool used by my customer: the OLA script with INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE values for FragmentationHigh parameter. Don’t worry OLA scripts work perfectly and the truth is out there 🙂 In the context of my customer, rebuild indexes online was permitted because the edition of the concerned SQL Server instance was Enterprise and this is precisely where we have to investigate here.

Let me demonstrate with a pretty simple example. On my lab environment I have a SQL Server 2014 instance with Enterprise edition. This instance hosts the well-known AdventureWorks2012 database with a dbo.bigTransactionHistory_rs1 table (this table is derived from the original script provided by Adam Machanic).

Here the current size of the AdventureWorks2012 database:

select
       name as logical_name,
       physical_name,
       size / 128 as size_mb,
       type_desc,
       cast(FILEPROPERTY(name, ‘SpaceUsed’) * 100. / size as decimal(5, 2)) as [space_used_%]
from sys.database_files

 

blog_28_1_database_situation

and here the size of the dbo.bigTransactionHistory_rs1 table:

exec sp_spaceused@objname = N’dbo.bigTransactionHistory_rs1′;
go

 

blog_28_2_bigTransactionHistory_rs1_situation

Total used size: 1.1 GB

Because we are in SIMPLE recovery model, I will momentary disable the checkpoint process in order to have time to get log records inside the transaction log by using the traceflag 3505

dbcc traceon(3505, -1);

 

Case 1: ALTER REBUID INDEX OFFLINE
alter index pk_bigTransactionHistory
on dbo.bigTransactionHistory_rs1 rebuild
with (online = off, maxdop = 1);
go

Let’s check the size of transaction log of the AdventureWorks2012 database

blog_28_3_database_situation_after_rebuild_offline

Case 2: ALTER REBUID INDEX ONLINE
— to initiate a tlog truncation before rebuilding the same index online
Checkpoint;
 
alter index pk_bigTransactionHistory
on dbo.bigTransactionHistory_rs1 rebuild
with (online = off, maxdop = 1);
go

Let’s check again the size of the transaction log of the AdventureWorks2012 database:

blog_28_3_database_situation_after_rebuild_online

It is clear that we have an obvious difference of size concerning the transaction log for each operation.

– offline: 4096 * 0.35% = 14MB
– online: 4096 * 5.63% = 230MB.

Stay curious and let’s have a look deeper at the records written inside the transaction log for each mode by using the undocumented function sys.fn_dblog() as follows:

select
       COUNT(*) as nb_records,
       SUM([Log Record Length])/ 1024 as kbytes
from sys.fn_dblog(NULL, NULL);
go

 

Offline Online
 blog_28_4_tlog_detail_offline_mode  blog_28_4_tlog_detail_online_mode

As expected we may notice a lot of records with index rebuild online operation comparing to the index rebuild offline operation (x21)
Let’s continue looking at the operations performed by SQL Server during the index rebuild operation in both cases:

select
       Operation,
       COUNT(*) as nb_records,
       SUM([Log Record Length])/ 1024 as kbytes
from sys.fn_dblog(NULL, NULL)
group by Operation
order by kbytes desc
go

 

Offline Online
blog_28_5_tlog_detail_offline_mode_2 blog_28_5_tlog_detail_online_mode_2

 

The above picture is very interesting because we may again see an obvious difference between each mode. For example, if we consider the operations performed in the second case (on the right, some of them doesn’t concern bulk activity as LOP_MIGRATE_LOCKS, LOP_DELETE_ROWS, LOP_DELETE_SPLITS, LOP_MODIFY_COLUMS an unknown allocation unit, which probably concerns the new structure. At this point I can’t confirm it (I don’t show here all details o these operations. I let you see by yourself). Furthermore, in the first case (on the left), the majority of operations concerns only LOP_MODIFY_OPERATION on the PFS page (context).

Does it mean that the online mode doesn’t use minimaly mechanism for the whole rebuild process? I retrieved an interesting response from this Microsoft KB which confirms my suspicion.

Online Index Rebuild is a fully logged operation on SQL Server 2008 and later versions, whereas it is minimally-logged in SQL Server 2005. The change was made to ensure data integrity and more robust restore capabilities.

However I guess we don’t have the same behavior than the FULL recovery model here. Indeed, there still exists a difference between SIMPLE / BULK_LOGGED and FULL recovery models in term of amount of log records generated. Here a picture of the transaction log size after rebuilding the big index online in full recovery model in my case:

 

blog_28_3_database_situation_after_rebuild_online_full_recovery

 

Ouch! 230MB (SIMPLE / BULK-LOGGED) vs 7GB  (FULL). It is clear that using FULL recovery model with rebuild index online operations will have a huge impact on the transaction log compared to the SIMPLE / BULK-LOGGED recovery model. So the solution in my case consisted in switching to offline mode or at least reducing the online operation for the concerned index.

Happy maintenance!

By David Barbarin

Post Views: 1,029
Thumbnail [60x60]
by
Microsoft Team