When index is created or recreated it usually decreases performance of database. Either SQL takes long time for response or it does not response at all as transactions are blocked. When new table or database goes live it is not possible to find out exactly how many indexes are needed. After running queries on near to production data it is possible to find out which index can perform better. It is important in highly sensitive application to have data always available.
SQL Server 2005 and later versions have provided feature called “Online Indexing”. Everytime index is updated it puts lock on table where index operations are happening. Depending on the situation SQL Server puts schema lock or shared lock while index is modified. If feature of “Online Indexing” is used SQL Server will behave normally when Index is modified.
One shortcoming of “Online Indexing” is when this feature is on, it takes little more time to complete index modification due to it lets database its normal operation continue. If there is no need of database or table offline indexing feature should be used as it is faster to complete whole process.
Let us see example of how index is specified with ONLINE option along with it.
CREATE INDEX idx_Table ON MyTable (MyColumn) WITH (ONLINE = ON)
The default value for the ONLINE option is OFF. This feature is not available with datatypes like TEXT, NTEXT, IMAGE or binnary.
Reference : Pinal Dave (https://blog.sqlauthority.com)
Is the online indexing feature only available in the Enterprise edition? For SQL 2005 I believe it is only available in Enterprise edition. Just to want to verify.
I know in SQL 2005 you must have Enterprise Edition to do online indexing. Do you know if they have made this available for all versions of SQL 2008, or do you still have to get the Enterprise Edition?
We are going to have multiple database servers, but would like some of the servers to use SQL 2008 Web Edition for some of our web sites, but the indexes must be run online.
I deployed online indexing, but alas, this does not solve my problem at all . Batchruns are still locked at night , systemjs are running slower than normal.
Online index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions.