SQL Server 2016 CTP – Alter Column Online Operation

Hi Friends,

Various new features and improvements are coming with SQL Server 2016. Various performance improvements are also added to the CTP versions. Today, I’ll show you one of them i.e. Alter Table … Alter Column online operation.

Blocking is one of the major problems when you work on performance tuning for SQL Server. We knows that when we perform any schema modification like Alter table … Alter column operation then it will take SCH_M lock and we found blocking for our select queries over that table. Generally these operations perform during scheduled time window. Let me show you that how this new feature can improve the performance. Here I am using CTP 3.3 and AdventureWorks2014 database for testing.

First let me create a copy of SalesOrderDetail table and insert some data into that:

USE [AdventureWorks2014]
  GO
  SELECT [SalesOrderID]
      ,[SalesOrderDetailID]
      ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
      ,[SpecialOfferID]
      ,[UnitPrice]
      ,[UnitPriceDiscount]
      ,[LineTotal]
      ,[rowguid]
      ,[ModifiedDate]
  INTO [AdventureWorks2014].[Sales].[Copy_SalesOrderDetail]
  FROM [AdventureWorks2014].[Sales].[SalesOrderDetail] 
  GO

  ALTER TABLE [AdventureWorks2014].[Sales].[Copy_SalesOrderDetail]
  ADD CONSTRAINT PK PRIMARY KEY CLUSTERED (SalesOrderID,SalesOrderDetailID)
  GO

  INSERT INTO [AdventureWorks2014].[Sales].[Copy_SalesOrderDetail]
  SELECT 
       [SalesOrderDetailID]
      ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
      ,[SpecialOfferID]
      ,[UnitPrice]
      ,[UnitPriceDiscount]
      ,[LineTotal]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks2014].[Sales].[SalesOrderDetail] 
  GO 4

Now Run below code into one session (for me session id is 55)

ALTER TABLE [AdventureWorks2014].[Sales].[Copy_SalesOrderDetail]
  ALTER COLUMN [CarrierTrackingNumber] VARCHAR(50)

Now try to execute below code in another session (for me session id is 57)

SELECT [SalesOrderID]
      ,[SalesOrderDetailID]
      ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
      ,[SpecialOfferID]
      ,[UnitPrice]
      ,[UnitPriceDiscount]
      ,[LineTotal]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks2014].[Sales].[Copy_SalesOrderDetail]
  ORDER BY [SalesOrderDetailID] DESC,[UnitPriceDiscount] ASC

You can see the session details by using below query in new query window.

Select * from sys.sysprocesses Where spid=55 OR spid=57

From the above output you can see that my Select query (session id 57) is blocked by Alter Column query (session id 55). After completion of above operations, Now let me add ONLINE=ON to my Alter column session as mention below:

ALTER TABLE [AdventureWorks2014].[Sales].[Copy_SalesOrderDetail]
  ALTER COLUMN [CarrierTrackingNumber] NVARCHAR(50)
  WITH (ONLINE=ON)

Now again execute the alter query first and then execute the select query in parallel. Now again check the session details using below query:

Select * from sys.sysprocesses Where spid=55 OR spid=57

This time SQL Server decides to go with parallelism but notice that there is no blocking between both the sessions 55 and 57.

Some limitations are also there for Alter Column Online operation, you can check them here.

HAPPY LEARNING!
Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook