So I was recently reading SQL Server’s blog, specifically the article Customers using SQL Server 2012 today! and some brave businesses are already using and enjoying some of SQL Server 2012 features that help their databases stay available. I’m excited about the new features too. For example, index rebuilds have been improved. We can now rebuild indexes online that include “blob” columns (like nvarchar(max), image, etc…). This means that (almost) every index can be rebuilt without requiring a table lock for the entire operation. And that’s good news for availability!
This wasn’t the case in earlier versions. In earlier versions, you couldn’t rebuild an index online if it included columns that were large strings. If you tried, you would get this message:
/*
An online operation cannot be performed for index 'ix_t1_1' because the
index contains column '<column name>' of data type text, ntext, image, varchar(max),
nvarchar(max), varbinary(max), xml, or large CLR type. For a non-clustered
index, the column could be an include column of the index. For a clustered
index, the column could be any column of the table. If DROP_EXISTING is
used, the column could be part of a new or old index. The operation must be
performed offline.
*/ |
Logic to determine when ONLINE=ON is supported
So that means there’s one more thing to check when finding out whether you can rebuild an index on-line. The logic now goes like this. You can rebuild an index online if:
- You’re using Enterprise Edition or higher
- And you’re using SQL Server 2012 or later
- Or you’re using SQL Server 2008 or earlier
- And your index is clustered and the table contains no blob columns
- Or your index is non-clustered and the index includes no blob columns
Hmm… You can’t really put parentheses in a bullet point list. Here try this flow chart:
Clear as mud
Hmm… that really doesn’t clear things up too well either. And I haven’t even mentioned partitioned indexes.
Just Try It
Maybe you’re like me, you may have to deal with multiple versions, multiple editions, and multiple tables and their indexes. Instead of wading through that logic above, just TRY it and let SQL Server figure it out. Here’s a sproc which takes a table name and index name and tries to rebuild the index online. If it can’t, it builds it offline.
create procedure s_TryRebuildOnlineOtherwiseOffline ( @schema sysname = 'dbo', @tablename sysname, @indexname sysname ) as begin set @schema = QUOTENAME(@schema); set @tablename = QUOTENAME(@tablename); set @indexname = QUOTENAME(@indexname); declare @sqlRebuild nvarchar(max) set @sqlRebuild = N'ALTER INDEX ' + @indexname + ' ON ' + @schema + '.' + @tablename + ' REBUILD'; declare @sqlRebuildOnline nvarchar(max) set @sqlRebuildOnline = @sqlRebuild + ' WITH (ONLINE=ON)'; begin try EXEC sp_executesql @sqlRebuildOnline; print @sqlRebuildOnline; end try begin catch EXEC sp_executesql @sqlRebuild; print @sqlRebuild; end catch end go |
Alternatively, you can adjust the script to REORGANIZE an index when it can’t rebuild it online.
By the way, if you use Ola Hallengren’s maintenance scripts, he’s already taken all this logic into account! If you write your own maintenance scripts, feel free to incorporate what I have here.
