Sql-server – CREATE NONCLUSTERED INDEX ONLINE, DROP EXISTING

indexnonclustered-indexsql server

I want to add three included columns to an existing non-clustered index. With that I'm solving performance issues in the database.

Here is some information about the index:
Rows: 112 Millions
Disk space: 30 GB

I'm using SQL Server 2008 R2 Enterprise Edition.

I'm running CREATE INDEX … WITH DROP EXISTING = ON, ONLINE = ON

In my QA environment I'm currently running the CREATE INDEX statement. It has been running 1 1/2 hours from now and I run on purpose an INSERT statement in another SSMS window, this statement is blocked since the CREATE INDEX operation is running.

In production this database is being accessed 24x7x365, so I can not accept so much time of blocking, but there's a time when the database has low access (3AM – 6AM).

I was expecting that the WITH ONLINE = ON behaves different. There's a way to prevent so much blocking? Or should I ask for a long maintenance window for the CREATE INDEX operation?

Thanks in advance for any response.

Best Answer

You need to analyze the wait chain. What is likely happening is that the OIB has completed the first two phases (perhaps long ago), and is now waiting for user queries to drain in order to do the final phase. One or more user queries is blocking OIB final phase, and as OIB has requested an SCH_M lock it places every other user queries in the wait list. The culprit is the user query blocking the final phase. Look at sys.dm_exec_request.blocking_session_id but make sure to walk up the chain until you find the one spid that is blocking w/o being blocked in turn.

Related Solutions

Sql-server – Differences Between Two Different Create Index Commands

It boils down to looking what the default values are. Lets break this down:

CREATE UNIQUE NONCLUSTERED INDEX [DEID_MAP_IDX1] ON [dbo].[DEID_MAP]

nonclustered is specified here. The default (i.e. nothing specified) is nonclustered. So unless clustered is specified it will default to nonclustered. So that's the same in both scripts.

[dbo] is specified here explicitly. As for the second un-specified CREATE INDEX then it all depends on what the current user's default schema is. Only you can answer that at the moment, so that may or may not default to dbo.

WITH (
    PAD_INDEX  = OFF, 
    STATISTICS_NORECOMPUTE  = OFF, 
    IGNORE_DUP_KEY  = OFF, 
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]

PAD_INDEX: the default is OFF, so unspecified will be the same in the second script as it is in the first.

STATISTICS_NORECOMPUTE: the default is OFF, so the second script unspecified has the same value.

IGNORE_DUP_KEY: the default is OFF, so the second CREATE INDEX is identical with this parameter.

ALLOW_ROW_LOCKS: the default is ON, so the second CREATE script has the same behavior.

ALLOW_PAGE_LOCKS: the default is ON...the second script has identical behavior.

... ON [PRIMARY]: just like the default schema one, this all depends on what your default filegroup is. If PRIMARY is the default filegroup, your second CREATE INDEX script will also create the index on PRIMARY. If PRIMARY is not the default filegroup, then it will be a different filegroup, as an unspecified filegroup will go to the default filegroup.

All of this information and default values can be found on this BOL reference here.

Sql-server – Why does CREATE INDEX … WITH ONLINE=ON block access to the table over a period of minutes

When creating an index with online = on, the create index process will not block when creating the index object itself, but when it comes to near the end of the process, it will acquire a schema modification lock* for a period in order to actually add the index to the table, this lock type will block all outside operations until the lock is released, which could account for your blocking issues.

* An Sch-M lock is not required for online build of a new nonclustered index, though it is required in all other cases. A new nonclustered index requires only a table-level shared lock during the final phase, same as was needed during the preparation phase.

See this White Paper for details:

Online Indexing Operations in SQL Server 2005

As suggested by Mushtaq Mohammed in a comment on the question, also see:

Unicorns, rainbows, and online index operations by Paul Randal

Best Answer

Related Solutions

Sql-server – Differences Between Two Different Create Index Commands

Sql-server – Why does CREATE INDEX … WITH ONLINE=ON block access to the table over a period of minutes

Related Question