Index Rebuild Enhancements with Newer SQL Versions

 

Wow! It has been a long time since I have blogged. Microsoft is firing on all cylinders lately and keeping us Premier Field Engineers very busy. I am going to try very hard to get back to some regular blogging about topics that I feel are good points for customers. The other day I had to go back and remind myself about index rebuild option changes. I have customers that run varying versions of SQL as I am sure many of you are as well. They are also upgrading to newer versions. With version changes, sometimes the general database maintenance does not always get updated. This is not always required, but it can really help improve uptime and impact of index maintenance when you look at new enhancements. When working with multiple versions we can all get confused on which version includes which feature. I came up with a little chart to help show index rebuild enhancements as it pertains to online/offline, partition level, and LOB data types.

 

	SQL 2008R2	SQL 2012	SQL 2014
Table Level	Online for non-LOB data	Online for some LOB data	Online for some LOB data
Partition Level	Offline Only	Offline Only	Online for some LOB data
LOB data	image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml offline only	image, ntext, and text offline only	image, ntext, and text offline only
Managed Lock Priority	No	No	Yes

Here is a short explanation on what you can/cannot do with the different versions:

SQL 2008R2: You cannot do online index rebuilds at the partition level and you cannot do online index rebuilds of a clustered index with LOB data types (varchar(max), text, image, etc)

SQL 2012: You cannot do online index rebuilds at the partition level.  You CAN, however, do online index rebuilds of the whole index of all data types except text, ntext, image.  This means that tables with varchar(max), nvarchar(max), and varbinary(max) columns can be rebuilt online in SQL 2012

SQL 2014: You CAN do online index rebuilds at the partition level.  You CAN do online index rebuilds of varchar(max), nvarchar(max), and varbinary(max) data types.  Just like with SQL 2012, text, ntext, image are not included.  These are legacy data types which really should not be phased out over time.

Key difference between online and offline index rebuilds

Offline: A Sch-M lock is placed at the table level throughout the rebuild.  This is a Schema Modification lock as if the schema is getting modified.  The same type of lock when you add a column, etc. This blocks any DML or DDL operation.

Online: A Sch-S lock is placed at the table level throughout the rebuild. This is a Schema Stability lock.  Any select also gets a Sch-S lock. This just makes sure that the table is not getting changed while it is doing its work.  At the end of the online index rebuild there is a quick Sch-M lock in order to publish the rebuild changes. This typically takes less than a second, but this request can get blocked as well as block others (while it is waiting).  This is where Managed Lock Priority comes into play to help tweak what you want to happen in those cases.  It can allow other processes to jump in front of it in line and not get blocked while the index rebuild is waiting for a lock.

TIP: Don't let partition level rebuild confuse you when it comes to locking:

In SQL 2012 and below, even though you may be only rebuilding 1 partition, there is a Schema Modification lock (sch-m) at the TABLE level.  This blocks other DML operations for the entire table.  The benefit of the partition level index rebuild here is that it takes less time to do only 1 partition, but the entire table is still inaccessible during the rebuild.  You may think that you are rebuilding the index of an old partition that none of your queries will be using, therefore there should be no blocking, right? This is not the case

Also keep in mind that online index rebuild options are only available in Enterprise Edition of SQL Server!

Here is a link with some good guidelines on online index rebuilds. Note that with any BOL article, you can click on the "other versions" link. That is a great way to compare and see how a particular feature differs between SQL Server versions.

https://msdn.microsoft.com/en-us/library/ms190981(v=sql.120).aspx

I know I touched upon Managed Lock Priority, but I did not explain it. That will be a follow up blog post.

Lisa Gardner