SQL Standard Edition ALTER TABLE ADD column NOT NULL CONSTRAINT DEFAULT 0 (Blocking and taking a long time)

The application is down! Blocking alerts are flooding your email inbox!  SQL Server is working as fast as it can but it's still your problem. Kill SPID, kill kill kill kill waiting for Roll Back.  We need to find a new way to do the same thing....

Now what just happened?

The creation of a new NOT NULL column with a default value to an existing table operation occurred on a large table: where every row in the table is trying to updated to add the default value of the new column.

Now why did this happen?

You are running SQL Standard 2016 or an Enterprise version prior to SQL 2012.  Small tables this is fairly insignificant as it will be a fast operation, but for large tables this will be problematic as to completely prohibit the operation as it will be a long operation causing blocking which will lead to application and job timeouts.

How can we reduce this and can we? 

Starting with SQL Server 2012 and if you are on ENTERPRISE Edition the operation in most cases, will be online and instantaneous as only the table metadata is changed with no rows being update assuming you are not trying to do this on a varchar(max) nvarchar(max) varbinary(max) or XML data type.  In our test environment we were using 4 million rows on a 3GB database with a SQL server that had 12GB ram and were not able to product locking/blocking if data was loaded into cache.  On production 80GB databases 40MM rows and 28GB of ram and step #3 produced around 5 minutes of down time as we paged out to disk and another 5 minutes on the FK additions.

If you are on SQL Standard or an older SQL version you can use a 3 step process to minimize locking/blocking and application downtime.  However, you are still only able to reduce the total duration and will still have some downtime.    

-- Initial script that was terminated due to running 6+ minutes and locking/blocking on the table
-- When tested on a full backup it ran for 36 minutes
ALTER TABLE [dbo].[SomeTable] ADD [SomeColumn] INT NOT NULL
    CONSTRAINT [DF_SomeTable_SomeColumn] DEFAULT (0)
GO
ALTER TABLE [dbo].[SomeTable] ADD CONSTRAINT [FK_SomeTable_SomeColumn] FOREIGN KEY ([SomeColumn]) REFERENCES [dbo].[SomeOtherTable] ([SomeOtherColumn])
GO


--step #1
--add SomeTable.SomeColumn as Null with Default Value
ALTER TABLE [dbo].[SomeTable] ADD [SomeColumn] INT NULL 
    CONSTRAINT [DF_SomeTable_SomeColumn] DEFAULT (0) -- Naming convention format DF_TableName_ColumnName  for default constraint
GO

--step #2  -- 
--clean up NULLs in table.  This will and can still run for long durations of time....
SET NOCOUNT ON;
DECLARE @BatchSize INT = 10000     -- Pick your batch size
WHILE 1=1
BEGIN
    UPDATE TOP (@BatchSize) [dbo].[SomeTable]
        SET [SomeColumn] = 0
        WHERE [SomeColumn] IS NULL 

    IF @@ROWCOUNT < @BatchSize
        BREAK;
END
GO

--step #3  -- This blocking
--modify status column to NOT NULL...
ALTER TABLE [dbo].[SomeTable] ALTER COLUMN [SomeColumn] INT NOT NULL
GO

-- You are now done and free to add any FOREIGN KEY CONSTRAINT as needed. **However, Note that this operation will cause blocking as well and could take as long or longer than step #3 above.

--Also in the above example we will assume that you have or put an index on that column or primed the cache using a select query.  

--Batching Incorrectly if you don't have an index and need to scan out can fill up the buffer pool and drop data out of cache and have the potential to blow up TempDB.  

Comments

Post a Comment

Popular posts from this blog

How to mass update all or some sql server agent job step commands

How to mass update all or some sql server agent job step and commands using TSQL.   This will use the built-in Replace function to search text in SQL agent jobs  SQL Agent Job information is stored in MSDB and can reviewed using some of the following queries: SELECT * FROM msdb.dbo.SysJobs SELECT * FROM msdb.dbo.SysJobSteps -- OR SELECT a.name as job_name, b.step_name, b.subsystem, b.command, b.database_name, a.enabled       ,a.description, a.date_created, b.job_id, b.step_id   FROM [dbo].[sysjobsteps] as b   INNER JOIN sysjobs as a ON a.job_id = b.job_id Since I will be focusing on the Commands column I will look at using this query to extract data into a temp table so that I can test any updates with the replace step before doing an update to MSDB. This would be your opportunity to archive or save any commands as needed.  Some people may prefer to generate all the syntax necessary to use EXEC sp_update_jobstep so you a...
Read more

SQL Monitoring for Blocking and Locking Capture and Log to table with sp_WhoIsActive or sp_BlitzWho

Image
You can use  sp_WhoIsActive or sp_BlitzWho and log the capture data to a table.   In my example I will use sp_whoisactive as I am more familiar with its use and data collection, however I will look at creating a future post using BlitzWho.  I will provide a few examples that were used to determine that the paid SQL monitoring products can be fancy tools but can be useless if you don't understand the basic concepts or even an understanding of how we capture similar data or in some cases more finely tuned to a specific scenario or use case.  Sometimes these paid solutions are incorrect or things can simply go wrong (Services Fail to start or the data is aggregated and misses something like the query commands and SQL Fragments) and it's always good to plan ahead and be prepared.   Sp_WhoIsActive is a procedure written by Adam Machanic. It can be found here:  sp_whoisactive downloads Sp_BlitzWho is a procedure written by the Brent Ozar team....
Read more

SQL Server General Index Naming Convention

SQL Server General Index Naming Convention  easy to follow:  PK_ for primary keys PK_  Primary Keys, which are logical constructs and not necessarily physical constructs, get tagged with a PK_ prefix. In most systems that I design the PK_ isn’t a clustered index.  IX_ for non clustered non unique indexes IX_  standard single or even multi-column indexes used for seeks or intersection just get prefixed with an IX_ (for  I nde X ). In cases where I need a composite index (i.e., multiple columns) but still explicitly ‘tolerate’ key/bookmark lookups, I’ll still call my seek/intersection/composite indexes IXes. UK_ for unique keys UK_  for unique key index UX_ for unique indexes UX_  for unique index. CLIX_ for Clustered Index (non PK) CLIX_  (non-PK) Clustered Index name is prefixed with CLIX (for  CL ustered  I nde X ) Additionally, I like to code all Create Indexes in SQL 2012 and above with an if not ...
Read more