I currently have a table with about 29 million records. The table currently has no indexes. However, I would like to build a clustered composite index built on three columns.
When I attempt to add the NON NULL constraint, my logs fill up. So my approach is to create three new columns with NON NULL, fill them up with data from the original three columns, and create my index on the three new columns.
In order to do this, I first loop through the table in batches to populate the column. This is because I was running out of transaction log space. This was based on the lock escalation page on Microsoft's site. The threshold is 5000 locks. If rows are being locked, I wanted to come in under that limit. So 4000 rows at a time.
I didn't create a new table, insert into it, then drop & rename because I thought it would be more efficient to just change some data than move everything - I might be wrong about this. When I did try it, I ran out of log space again.
This is the SQL I am using to update my new columns looks like this:
DECLARE @Rows INT, @BatchSize INT; -- keep this below 5k SET @BatchSize = 4000; SET @Rows = @BatchSize; BEGIN TRY WHILE (@Rows = @BatchSize) BEGIN UPDATE Top(@BatchSize) Master.VOUCHER_LINE SET tempBusinessUnit = BUSINESS_UNIT FROM Master.VOUCHER_LINE WHERE tempBusinessUnit = 'xxxxx'; /* This is a default value for the column. It identifies columns that have not been updated. */ SET @Rows = @@ROWCOUNT; /* This prints the time that each loop is completed. I use it to estimate how much time this script will take. */ DECLARE @Time nvarchar(100) SET @Time = CAST(CURRENT_TIMESTAMP as nvarchar) RAISERROR(@Time, 0, 1) WITH NOWAIT END END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH;
With 29 million records (and about 100 columns), I expect this operation to take a while. After running for the first hour, the performance is 4,000 records about every 2 minutes. At that rate this single column will be done in approximately 10 days.
Is there something I can do to improve performance?