Just a day ago, during one interview question of Online Indexing come up. I really enjoy discussing this issue as I was talking with candidate who was very smart. We talked about what an online index rebuild is and where it falls short.
Following two questions were discussed.
1) What is Online Index Rebuild Operation?
Online operation means when online operations are happening the database are in normal operational condition, the processes which are participating in online operations does not require exclusive access to database.
Read about this in-depth in my previous article SQL SERVER – 2005 – Explanation and Script for Online Index Operations – Create, Rebuild, Drop
2) What are the limitation of the Online Index Rebuild Operation?
Following indexes can not be build online. We were able to come up with first four kind of the indexes. I researched book online before I wrote this article have listed all the six exceptions of index rebuild operation.
- Disabled indexes
- XML indexes
- Indexes on local temp tables
- Partitioned indexes
- Clustered indexes if the underlying table contains LOB data types
- Nonclustered indexes that are defined with LOB data type columns
Before You Plan an Online Index Rebuild
The list above is only half the story. In SQL Server 2005, online operations are an Enterprise Edition feature, so on Standard Edition the ONLINE = ON option returns an error. Check your edition with SELECT SERVERPROPERTY('Edition') before you build a maintenance plan around it.
Online also does not mean zero impact. The rebuild still takes a short shared lock at the start and a schema modification lock at the end, so a long running query can make it wait. It needs extra space too, because the old and the new copy of the index exist side by side until the switch, and it usually runs longer than an offline rebuild.
Newer versions removed some of these limits, for example for large value columns such as varchar(max). If you are on a later release, read the documentation for that version before you rule out an online rebuild, and test it on a copy of the database first.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
4 Comments. Leave new
Hi Pinal Dave,
We are planning to enable our application to support sql server 2005 and while migrating oracle scripts we found that,
creating index in a separate schema is not allowed. Could you
please let us know is there any way to create an index in separate schema?.
Thanks for your your help.
With best regards,
Manja
Hi
How can i know time with in the script
Regards/manoj
Hi
We have online indexing through maintance plan, but while the maintance plan ran i got the below error. I am having table with clustered index on int datatype column and the table have a column with text datatype also.
“failed with the following error: “Online index operation cannot be performed for index ‘PK_DLINKED_SE__689D8392’ because the index contains column ‘TextData’ of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.”