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

blocking

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

No_blocking

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

Avatar

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published. Required fields are marked *