Partition Switching and Indexing

Next Post
By Admin at 12 Jul 2016, 17:59 PM
  • A good way to minimize the maintenance downtime and increase availability in SQL Server 2014 is to perform Partition Switching and Online Index Rebuilding. These operations use Sch-M, an exclusive table lock during the DDL operation that can cause the database operation to increase its workload.

    Two locks (table S-lock and Sch-M) are required for Online Index Rebuilding. Two Sch-M locks are needed for the SWITCH - one for the destination table and the other for the source table.

    Note: All table blocking operations must be completed prior to the execution for a DDL statement for a SWITCH or Online Index Rebuild.

    SQL Server 2014 introduced two new features to improve the availability of the application during online operations:

    • Managed Lock Priority
    • Single Partition Online Index Rebuild

    Managed Lock Priority

    Managed Lock Priority permits you to manage the priority of the locks used, reducing the negative impacts of these locks. Managed Lock Priority uses the following methods to reduce the primary workload on the server, if the function exceeds the maximum duration:

    • Switch to normal queue: Place the lock in the regular lock queue.

    • Exit DDL after wait: Exit the Online Index Rebuild or SWITCH operation without any action.

    • Kill all blockers: Enforce an Online Index Rebuild or SWITCH by killing all blockers immediately.

    NOTE: The MAX_DURATION is the maximum time allotted to wait for the operation to complete.

    The MAX_DURATION can be set up to 49 days, but having a very high value will not allow the transaction logs to be truncated until the DDL is completed. If you have enough space to log the transaction, you can have higher values as MAX_DURATION; otherwise it’s best to indicate a lesser amount of time.


    1. <low_priority_lock_wait>: :=
    2. {
    5. }

    Enforce the index maintenance process by having an option to kill the blockers. However, you may still experience some issues. This is because it is sometimes difficult to guess the transactions that need to be rolled back. It is also not possible to define the precedence for some of these transactions; usually some transactions need more time to rollback.

    Quick Tips:

    • For critical objects, perform an offline index rebuild. Otherwise, perform an online index rebuild.
    • To rebuild all the indexes in a database, use a filtered index rebuild instead of a maintenance plan rebuild index task.
    • While using the OIR precedence option, use an xEvent session to identify what has been killed in the ALTER INDEX operation.
    • Have a retry login for every transaction.

    Single Partition Online Index Rebuild

    Single Partition Online Index Rebuild is another advanced feature in SQL Server 2014. In previous versions, all partitions had to be rebuilt for a partitioned table.

    To specify a specific partition instead of all partitions, run the following query:

    1. ALTER INDEX [Index_detail]
    2. ON [dbo].[Indexed_Table]
    4. WITH (ONLINE= ON);
    5. GO


    1. <single_partition_rebuild_option> : :=
    2. {
    3. SORT_IN_TEMPDB = { ON | OFF }
    4. | MAZDOP = max_degree_of_parallelism
    6. ONLINE = { ON [ <low_priority_lock_wait> ) ] | OFF }
    7. |
    8. }

    While doing index rebuild, the database engine needs to track all activities. The log is needed when something wrong happens to the database (i.e. a server rebooted when the rebuild is in progress), so a large amount of log records will be generated for a complete rebuilding process.

    Single partition online index rebuilds make the table available while minimizing the amount of log records created during the operation.



    Post a comment

    Please correct the following: