CREATE INDEX <index_name> ... WITH (DROP_EXISTING = ON, ONLINE = ON)

Question

Hi All,

Microsoft SQL Server 2017 (RTM-CU20) (KB4541283) - 14.0.3294.2 (X64) Mar 13 2020 14:53:45 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

I am trying to understand the behavior of CREATE INDEX WITH (DROP_EXISTING = ON).

I currently have a very active table with approximately 45 million rows. There is NO clustered index on this table.

There is a non-clustered index that currently looks like this:

• CREATE NONCLUSTERED INDEX IX_NC_01 on my_table (Col1);

I would like to modify the index by adding an include clause:

• CREATE NONCLUSTERED INDEX IX_NC_01 on my_table (Col1) Include (Col2) WITH (DROP_EXISTING = ON, ONLINE = ON)

I want this action to be as non-intrusive as possible.

I have read this snip from the documentation:

The DROP_EXISTING clause will not sort the data again if the same index key columns are used in the same order and with the same ascending or descending order, unless the index statement specifies a nonclustered index and the ONLINE option is set to OFF. If the clustered index is disabled, the CREATE INDEX WITH DROP_EXISTING operation must be performed with ONLINE set to OFF. If a nonclustered index is disabled and is not associated with a disabled clustered index, the CREATE INDEX WITH DROP_EXISTING operation can be performed with ONLINE set to OFF or ON.

Questions:

1. Will the old version of the index be available while the statement is running?
2. Please explain the interaction between ONLINE=ON and DROP_EXISTING=ON. My understanding of this is that I should include both clauses.
3. Will I increase locking/blocking while the statement is running?

Thank you for your help.

--Dan

Answer 1

Will the old version of the index be available while the statement is running?

Yes.

Please explain the interaction between ONLINE=ON and DROP_EXISTING=ON. My understanding of this is that I should include both clauses.

I think so too. ONLINE = ON means that you want the table to be available while the CREATE INDEX operation is running.

DROP_EXISTING = ON, means that you want to create a new index with the same name, without first dropping the existing index. This is particularly powerful when redefining a clustered index, since this saves you from one round of updating all the non-clustered index.

But as the BOL topic says, you may also get the gain of that SQL Server is able to use the existing sort order when all you do is to add an included column.

Will I increase locking/blocking while the statement is running?

Yes. While it is an ONLINE operation, Sch-M (schema modification) locks are taken on two occasions: once when the operation starts, once when it completes. These locks are for metadata operations and are usually shortlived. However, if there is a long-running query, this query will block the Sch-M lock, and the request for the Sch-M will block all other processes from accessing the table. Some operations has a WAIT_AT_LOW_PRIORITY option to avoid this issue, but I can't see it for CREATE INDEX.

Thus, you may not want to run this command during periods when the table is really busy. Then again, if most queries are short, this is unlikely to be an issue.

During the it actually builds in the index, no locks are held.

Answer 2

Thank you Erland,

This is my thinking too.

On a test server, the operation completed relatively quickly (under 2 minutes). However, there is no load in the test environment.

This is a critical table, and blocking would be painful, as I have thousands of connections. There is no "window" as this is 24x7x365 ...

Thanks again.

--Dan

Answer 3

Hi Erland,

I decided to put my cowboy hat on, monitor the heck out of it, and run the CREATE INDEX ...

All is well. The command completed in about 9 minutes, with very minor blocking throughout, because as you guessed, the heavy hitters on this table are short-duration transactions.

It needed to be done, but I was afraid to do it "alone" !

Thanks again!

--Dan