Skip to main content
added 81 characters in body
Source Link
LowlyDBA - John M
  • 11k
  • 11
  • 45
  • 62

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 by thisduring the process, but creatingstill 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.

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 by this process, but creating an index does have a resource cost, 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.

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.

Source Link
LowlyDBA - John M
  • 11k
  • 11
  • 45
  • 62

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 by this process, but creating an index does have a resource cost, 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.