HOW TO: ALTER TABLE in Windows Azure SQL Database Without Filling Up Your Transaction Log

One of the things I love most about Windows Azure SQL Database (WASD) is that you don’t have many options when it comes to performance tuning. WASD makes you focus on code and design in order to maximize performance. I would estimate that 98% of all performance issues are due to sub-optimal code and database design and that is why I prefer having a platform that allows me to focus on those aspects and less on the things I often don’t control such as hardware.

In a way WASD forces you to actually think about how to solve a problem as opposed to just throwing hardware at the issue and then tossing up your hands in exasperation when performance still doesn’t improve.

Here’s one example.

When using WASD your database is limited to a transaction log size of 10GB, and any single transaction is only allowed to consume 1GB of transaction log space. I’ve looked to see if this has changed for the new Premium version of WASD but the best document I could find makes no mention of this limit on the transaction log having been changed.

You will know if you hit the 1GB limit for a single transaction because you will be greeted with error message number 40552:

Msg 40552, Level 20, State 1, Line 1
The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.

 

One common example of where my customers see this error message is when they are making changes to a column and try to alter a table that has many millions of rows. Why are they trying to do this? Well, remember that with WASD you spend a lot more of your time changing code and design. So this tends to be something that crops up for a lot of folks and causes some pain as they try to move things around.

As such I thought it was time to outline one way to get the job done.

DISCLAIMER: Do not run code you find on the internet in your production environment without testing it first. Do not use this code if your vision becomes blurred. Seek medical attention if this code runs longer than four hours. On rare occasions this code has been known to cause one or more of the following: nausea, headaches, high blood pressure, popcorn cravings, and the impulse to reformat tabs into spaces. If this code causes your servers to smoke, seek shelter. Do not taunt this code.

I’m going to use AdventureWorks2012 as my sample database, and we will assume that we want to make a change to the Sales.Currency table, modifying the CurrencyCode column from NCHAR(3) to be NCHAR(4). We will also assume that users aren’t connected during this time. In other words, you have a maintenance window in which to perform this work.

First, create the new table, complete with the new datatype you want to use and be mindful that you need to preserve all constraints related to the original table:

CREATE TABLE [Sales].[Currency_New]
 ( [CurrencyCode] [nchar](4) NOT NULL, 
   [Name] [varchar](128) NOT NULL, 
   [ModifiedDate] [datetime] NOT NULL, 

   CONSTRAINT [PK_Currency_New_CurrencyCode] PRIMARY KEY CLUSTERED 
   ( [CurrencyCode] ASC ) 
   WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
   ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ) 
GO

Then, insert your records and define your batch size. I am using 10 here in this example but you will likely want to use something larger, say 10,000 rows at a time. Back in the day when I still worked for a living I would usually do 100,000 rows as an initial batch size and adjust up or down from there as needed:

DECLARE @RowsInserted INT, @InsertVolume INT
SET @RowsInserted = 1
SET @InsertVolume = 10 --Set to # of rows

WHILE @RowsInserted > 0
BEGIN       

INSERT INTO [Sales].[Currency_New] ([CurrencyCode]
           ,[Name]
           ,[ModifiedDate])
SELECT TOP (@InsertVolume)
       SC.[CurrencyCode]
           ,SC.[Name]
           ,SC.[ModifiedDate]
FROM [Sales].[Currency] AS SC
LEFT JOIN [Sales].[Currency_New] AS SCN 
    ON SC.[CurrencyCode] = SCN.[CurrencyCode] 
WHERE SCN.[CurrencyCode] IS NULL

SET @RowsInserted = @@ROWCOUNT
END

My OCD mandates that I do a sanity check and verify the rowcounts are the same before cleaning up:

SELECT COUNT(*) FROM [Sales].[Currency] 
SELECT COUNT(*) FROM [Sales].[Currency_New]

Once you are confident you have migrated your data successfully you can drop the original table:

DROP TABLE [Sales].[Currency]

Last step, rename the new table, so that users don’t have to change any code:

EXEC sp_rename '[Sales].[Currency_New]', '[Sales].[Currency]';
GO

This method of doing work in batches isn’t a revolutionary idea. It’s been around for decades and it can be applied to other scenarios besides just altering the datatype for one column. I find that due to the constraints for things like disk space in WASD that a lot of our old tricks are very relevant again.

LIke I said earlier, the method above is just one way to get the job done. There are other methods but this one works well enough for most situations. If you don’t have a clear maintenance window and users need to stay connected to the data then you are going to need to find an alternative method to what I have outlined here. I’d also recommend that you have a copy of the original database handy, or an export, just in case you need to recover to an earlier point in the process.