Problem
When a schema modification is performed on a database table, a special schema lock type SCH_M will be issued and will not be released until this schema modification is completed. If you try to perform a SELECT query on that table during that process, the query will be blocked by the schema change waiting for the change completion. Is there any way to perform the schema changes during working hours without blocking the SELECT queries running on the table?
Solution
SQL Server 2016 brought us many new features and enhancements to existing features. One of these T-SQL enhancements is the ability to perform the table schema changes online, reducing downtime required to apply the change. The WITH (ONLINE = ON | OFF) is a new option in the ALTER TABLE…ALTER COLUMN T-SQL statement that allows you to perform changes on the columns such as changing the column data type, length, collation , sparseness and nullability while the table is online and available for users. This new feature is disabled by default and can be enabled within the ALTER TABLE…ALTER COLUMN WITH (ONLINE = ON) T-SQL statement.
Let us go through a practical demo to understand how this new feature works. We will create a new table Production.TransactionHistory_New on the MSSQLTipsDemo test database:
USE [MSSQLTipsDemo]
GO
CREATE TABLE [Production].[TransactionHistory_New](
[TransactionID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[ReferenceOrderID] [int] NOT NULL,
[ReferenceOrderLineID] [int] NOT NULL,
[TransactionDate] [datetime] NOT NULL,
[TransactionType] [nchar](1) NOT NULL,
[Quantity] [int] NOT NULL,
[ActualCost] [money] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
[Address] nvarchar(500) NOT NULL)
ON [PRIMARY]
GO
Then add records to the new table from the Production.TransactionHistory table:
INSERT INTO [Production].[TransactionHistory_New] SELECT * FROM [Production].[TransactionHistory]
GO
If we try to run a normal ALTER TABLE…ALTER COLUMN T-SQL statement to change the data type of the Address column from nvarchar(500) to varchar(600), which will run with session ID 53 in my example:
ALTER TABLE [Production].[TransactionHistory_New] ALTER COLUMN [Address] varchar(600) NOT NULL
At the same time, try to run the below SELECT statement from the same table, which will run with session ID 56 in my example:
SELECT TOP 10000 * FROM Production.TransactionHistory_New ORDER BY [Address]
If we query the sys.sysprocesses system object to check the status of these two sessions:
Select spid, blocked,dbid,status,cmd from sys.sysprocesses Where spid=53 OR spid=56
The result will show us the SELECT statement is blocked by the ALTER COLUMN statement, and will not retrieve the data until the schema change is complete:
In this case, the ALTER COLUMN statement will be completed in 4ms without interruption as we can see from the time statistics result below:
If we try now to run the same ALTER COLUMN statement with the online execution enabled for that process as shown below:
ALTER TABLE [Production].[TransactionHistory_New] ALTER COLUMN [Address] varchar(600) NOT NULL
WITH (ONLINE=ON)
Then run the previous SELECT statement from the same table:
SELECT TOP 10000 * FROM Production.TransactionHistory_New ORDER BY [Address]
And try again to query the sys.sysprocesses system object to check the status of these two sessions:
Select spid, blocked,dbid,status,cmd from sys.sysprocesses Where spid=53 OR spid=56
The result will show us that the ALTER COLUMN statement will not block the SELECT statement, and the SELECT statement will run concurrently, but this time using a parallel plan retrieving the data without waiting for the ALTER COLUMN statement to complete:
The cost you will pay is the time required for this ALTER COLUMN statement to execute. This second online execution will take about 30 seconds to complete as compared with the offline process that took only 4 ms:
So, you need to compromise between running the ALTER COLUMN statement offline taking less time, but blocking all other processes on that table, or running it online, taking longer time without blocking other processes on the table.
There are many limitations for the online ALTER COLUMN operation such as altering the column from NOT NULL to NULL, which is not supported if the column is involved in a non-clustered index. If we create a non-clustered index on the Address column in the previous table:
USE [MSSQLTipsDemo]
CREATE NONCLUSTERED INDEX [IX_TransactionHistory_New_Address] ON [Production].[TransactionHistory_New]
( [Address] ASC )
GO
Then try to ALTER that column using the online option:
ALTER TABLE [Production].[TransactionHistory_New] ALTER COLUMN [Address] varchar(600) NULL
WITH (ONLINE=ON)
The ALTER operation will fail because the non-clustered index is dependent on that column as in the below error message:
Other limitations for the online ALTER COLUMN operation are that it does not support ADD/DROP PERSISTED and ADD/DROP ROWGUIDCOL/NOT FOR REPLICATION T-SQL statements. Altering from/to CLR data types, altering to XML datatype with a different schema collection and altering a table with change tracking enabled on that table are not allowed too for the online ALTER COLUMN statement.
Take into consideration that you can only use the online ALTER COLUMN to alter one column at a time and that this process requires twice the storage, as the original column in order to accommodate users connectivity during the new column creation.
Next Steps
