We are using SQL Server 2016 and have 3 partitioned tables (every table around 300-500 milion of records). For every table would like to create NONCLUSTERED PARTITIONED INDEX. What is the best way to avoid blocking table in process of creating each NONCLUSTERED PARTITIONED INDEX. Could we use ONLINE option for this kind of indexes ?


Could we use ONLINE option for this kind of indexes ?

Maybe. The documentation states:

Online index operations are not available in every edition of Microsoft SQL Server. For a list of features that are supported by the editions of SQL Server, see Editions and Supported Features for SQL Server 2016.

The reference shows online indexing requires Enterprise Edition.

  • Thank you. Just to confirm - The only option to avoid blocking is ONLINE option correct ?
    – grochu
    Oct 22 at 14:02
  • @grochu, that is correct. You'll need to plan a maintenance window if ONLINE=ON isn't feasible in your environment.
    – Dan Guzman
    Oct 22 at 14:20
  • 1
    Also note that ONLINE=ON does not mean there will be no blocking, it just reduces the amount of blocking fairly significantly. Additionally, keep in mind that even with minimal blocking, depending on how resource intensive the index creation is and how your server is provisioned, it could still cause global resource contention and affect the performance of your server for other queries. So you may want to consider a maintenance window, if possible, as well.
    – J.D.
    Oct 23 at 0:24

Be aware that if your system is very busy the ONLINE option can take a very long time to run. BTW don't forget to partition align the index.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.