Sql-server – SQL Server AlwaysOn Availability Group – Creating an Index Online in SSMS

availability-groupsindexsql serversql-server-2016ssms

I am running a 2-node SQL Server 2016 AlwaysOn Availability Group. I would like to create a non-clustered index online in SSMS for one of the availability databases in the AlwaysOn Availability Group. For this operation, it is very important that the index create be Online; in other words, the table should not become locked and both reads and writes should continue without interruption or error during this time. Also, the cluster should not go into an unhealthy state where replication stops.

Here was my plan for doing this:

  1. Go into SQL Server Management Studio (SSMS) on the master node, go to the database, table and then Indexes section in Object Explorer and right-click on Indexes and choose New Index – Non-Clustered

  2. Add the appropriate field to the Index Key columns screen

  3. Go to Options – and set "Allow online DML processing" to "True"

  4. Click OK

Is the above approach the best way to do this? Will this actually create the index online? While the index is being created, will the cluster remain in a healthy and replicated state, will the index get created on the secondary automatically, and will the incoming reads and writes continue to happen without errors?

Any help with this is greatly appreciated!

Best Answer

Is the above approach the best way to do this?

Essentially this is the only way to approach this - indexes can only be created by one command. You could execute T-SQL for the CREATE INDEX manually, but the same command is going to be executed on the server regardless (barring any different options you choose, such as online).

As with any production rollout, you should have ideally tested this in a lower environment to get an idea for how long it will take and if it will cause any slow downs in the application - things we can't tell you based on the provided information.

Will this actually create the index online?

Yes, provided you are using Enterprise edition. Otherwise the index will not be created online. Again, this is something you should have already tested in a lower environment or copy of production.

While the index is being created, will the cluster remain in a healthy and replicated state

Index creation in general should not impact cluster health, but we also don't know your server specs, how big of an index this is, or a lot of other details that could make this a riskier operation. My advice is to monitor the index creation if you are concerned at all.

will the index get created on the secondary automatically

See above Enterprise Edition comment.

will the incoming reads and writes continue to happen without errors?

If the index is being created ONLINE then the table will not be locked during the process, but still necessitates a very brief schema lock at the first / final stage of creation. Creating an index does have a resource cost though, so you may see slowness in your queries that utilize the targeted table. Which is why you should test this before releasing to production.

There are other index options like MAXDOP which can be used to limit how many resources are utilized by the index creation process.

Related Question