Your browser (Internet Explorer 7 or lower) is out of date. It has known security flaws and may not display all features of this and other websites. Learn how to update your browser.

X

SQL Server 2014 Managed Lock Priority for online index rebuild, PART 2

Can I have predictable Online Index Rebuilds (cont.)

The Managed Lock Priority in SQL Server 2014 give us a choice for managing locks of the online index rebuilt operation (as I started to write about in PART 1)

We need to do that because the online index rebuild operation is not performed entirely online. There are Schema locks that the process need to obtain at the beginning and at the end of the index rebuild and those type of locks (especially Sch-M one) could have incompatibility with other locks currently obtained on the object. Because of that some parts of the process need to be done in “offline” mode, and as a result we experience blocking.

We have two new options controlling the behavior of the online index rebuild when a blocking with a user defined transaction occurs:

Option 1: Wait for blockers and after the wait time expires (MAX_DURATION) kill the blocker transaction

Option 2: Wait for blockers and after the wait time expires (MAX_DURATION) exit the ALTER IDEX REBUILD statement and give the precedence to the user transaction

In this part II I am continuing to describe the Managed Lock Priority OIR with the second new option we have. What happens when we give a priority to a user transaction instead of the online index rebuild process.

The Short answer

Waiting for blockers in the case of giving priority to the transaction over index rebuild is implemented by using the lock timeout mechanism. After reaching the MAX_DURATION, the OIR “exits” the statement (rolls back the alter index rebuild transaction), and returns error 1222:

Yes, it is that simple!

The long answer

Let’s dive into this a little bit. In the example below the SPID 52 runs a transaction without committing it:

The SPID 53 runs a rebuilding index as in the first picture above. Because it encounters the incompatibility of its own Sc-M lock with the currently held Lck_M on the object, it just waits with a LOW_PRIORITY LOCK (LCK_M_SCH_M_LOW_PRIORITY) wait type.

As I mentioned in the PART 1, there are new wait types showing the lock wait that is occurring during the Priority OIR operation. We can see them by querying the sys.dm_os_waiting_tasks during the MAX_DURATION time. There are about 42 new wait types available in the sys.dm_os_wait_stats cumulating every single lock wait during every managed lock OIR operation since the last SQL Server start. Those new types help you easy define at what stage of the OIR operation the most of the waits occur – the wait type shows the blocking lock of the user transaction on the first place, then the alter index lock type that is waiting on a low priority.

For example when the IS lock of the OIR is at low priority waiting for LCK_M lock currently held on the object:

Here is the respective locks list in the sys.dm_tran_locks DMV:

So you could expect two types of LOW_PRIORITY_WAIT lock requests statuses in the case of abort_after_wait=self:

  • The first one is IS request mode which appears when there is a user transaction modifying the object that prevents the index rebuild from starting – shown as LCK_M_IS_LOW_PRIORITY wait type and
  • The second one is Sch_M request mode which appears at the end of the online rebuild index process when it should switch to the newly rebuilt index structure. This lock request is even more restrictive and needs no other locks to be held on the resource to continue. It appears as LCK_xxxx_SCH_M_LOW_PRIORITY wait type and as a Sch_M LOW_PRIORITY lock

The difference in this process comparing to the option 1 process (where the OIR kills blockers) is that the ABORT_BLOCKERS wait type and event is not registered, because the OIR is not killing any user transactions but itself.

When the MAX_DURATION is reached, the error 1222 is raised. No messages are logged in SQL Server Error log.

As you probably know the error 1222 appears when the session level SET LOCK_TIMEOUT is set and a statement reaches the defined number of seconds waiting to obtain a lock. It is not a server level but a session level setting, and by default it is not set. Very often the client application including those developed using Entity Framework, change this setting and you couldn’t even guess about that until you track Errors and Warnings event or obtain some inconsistencies in transaction that do not have proper error handling logic. The result of specifying the LOCK_TIMEOUT is that when the lock timeout period is reached by a statement, it is cancelled (rolled back) but the transaction it is enclosed in, if any, is not rolled back, the next statement inside the transaction continues to execute. This could of course bring problems unless you manage well your transactions and put error handling logic inside. (http://technet.microsoft.com/en-us/library/ms177413(v=sql.105).aspx)

Obviously the same is happening with a rebuild index transaction. The MAX_DURATION is set internally as lock_timeout at the beginning of the rebuild index statement, but it doesn’t touch the session level setting. When I checked the @@LOCK_TIEOUT after the rebuild index completed it returned a value of –1, which was the default setting at the beginning of a connection:

This is gerat – we are aware of how the process works, what kind of locks and waits it obtains during its execution. But imagine that we have a scheduled OIR as part of a job and we want to know how the operation completes, how many index rebuild transactions has been rolled back or some other detailed information.

How we could get that info?

Even longer answer

Let’s track the process again using an xEvent session. I am using the same event session as I have used in PART 1:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE EVENT SESSION [KilledByOIR] ON SERVER

ADD EVENT sqlserver.ddl_with_wait_at_low_priority,
ADD EVENT sqlserver.process_killed_by_abort_blockers(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([sqlserver].[database_id]=(9) AND [sqlserver].[session_id] >=(50))), –dont forget TO CHANGE the database_id accordingly
ADD EVENT sqlserver.sql_transaction(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([package0].[equal_uint64]([sqlserver].[database_id],(9)) AND [package0].[greater_than_equal_uint64]([sqlserver].[session_id],(50)) AND [transaction_state]=(2)))
ADD TARGET package0.event_file(SET filename=N'KilledByOIR') --this will create the file in default Log folder location
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

I have included all 3 events that are coming with SQL Server 2014 for monitoring the managed lock priority OIR process:

  • ddl_with_wait_at_low_priority – it raises an event when a Manage Lock Priority operation is starts
  • lock_request_priority_state– it shows the lock status change of the OIR process during the whole operation
  • process_killed_by_abort_blockers – it raises an event when a process has been killed by the Managed lock operation

I also included the sql_transaction event in order to find transactions that are changing their status near the time of process abort. I put some additional actions to the xevents session like sql_text to track even more information.

Here is the process in details, a very interesting one. The attach_activity_id.guid shows the guid of the main event – the Manage Lock priority operation, which remains the same for all subsequent events. The attach_activity_id.seq (the fourth column) shows the sequence number of a connected event after the main event starts. Events with attach_activity_id.seq  show the phases that the process of low priority index rebuild goes trough and their lock requests which matches exactly to the picture on online locking above  – IS –> S –> mapping index structure build –> Sch_M. Every stage starts the Low Priority Period, if there is no blocking transactions then it just continues without counting the MAX_DURATION.

 

 

 

 

 

The low priority period starts waiting on MAX_DURATION when the process is trying to obtain the Sch_M lock (I started a user transaction after the start of the index rebuild). Waiting on the lock priority takes exactly 1 min – 23:46 – 23:47 on attach_activity_id.seq 6-7 After the Low priority Period Expired state is reached the process is aborting itself (attach_activity_id.seq = 8)

meaning the transaction is rolled back (attach_activity_id.seq = 9), which appears as error 1222 of the alter index rebuild operation

The “What if” answer

The think that bothers me is what if the index that is being rebuild is a large one, the database is in full recovery model, and the rebuild operation reaches the Sch_M lock when the rebuild is almost done? When it is killed at the beginning no problem. But to be killed when it is almost completed.. The index rebuild process is a resource intensive operation – it consumes I/O, CPU, Memory, tempdb. It also creates a huge amount of transaction log records – see an excellent post that Kalen Delaney wrote on what gets logged for index rebuilds –  http://sqlblog.com/blogs/kalen_delaney/archive/2011/03/08/what-gets-logged-for-index-rebuilds.aspx So it really takes a lot to get there. The rollback of such a transaction could take sometimes even longer than the time it took to reach to the point of killing and as such making the wait more than it is acceptable. Such a case definitely creates more problem that it resolves, because if I just leave the index rebuild to complete, it will probably take additional few seconds instead of rolling it back which could take forever. But we don’t have such a fine grain control on which wait stage of the process to perform an abort self action (at least in this release).

The only option that we have is either to execute the rebuild index offline, or to give priority to OIR over the user transaction (option 1) The good think is that we have a choice on statement level so we could perform rebuild operation with different setting of the abort_after_wait depending on the index. This gives us an opportunity to make a wise decision how to organize the whole index maintenance process and probably not choose abort=self for very large indexes.

The next question what if I need to complete the rebuild process anyway or to continue to manage it, how to get info which indexes has been rebuild at the end and which OIRs have been rolled back?

The first think that is coming to my mind is to check the last_system_scan column for the respective indexes in the sys.dm_db_index_usage_stats?

BUT the “system” is accessing/scanning the index structure from the beginning of the priority rebuild process, the rollback could occur at the end, so the DMV is not giving the info that is has been successfully rebuilt.

Tracking error 1222 makes not so much sense unless you track the blocked process threshold in details and extract the specific wait_types

You can check stats using stats_date, but it is also not guaranteed the operation has completed successfully.

There are two options that remain:

  1. Just check fragmentation level, if it still stays the same, then index hasn’t been rebuilt. If your index rebuild script is based on fragmentation level as it should be, then next time you run the script it will just rebuild all the indexes that hasn’t been rebuilt yet.
  2. List the rolled back transactions with their sql_text statements from the xEvent session and run them again after a while. You have to filter carefully (sql_transaction event with the same attach_activity_id.guid as a ddl_with_wait_at_low_priority event, with transaction_type=system and  transaction_state=2)

You can relatively easy implement those in your index maintenance scripts.

Summary

The new coming priority online index rebuild operation in SQL Server 2014 gives us better control and additional options to choose from. It could provide a good solution to achieve a predictable online index rebuild. It can also create problems rather than solving them if we do not take into account the way it works. The main thinks and rules that you should consider:

  • Rolling back a user transaction could be an option in case you don’t expect critical transactions to happen during OIR and of course in case you have a transaction retry logic.
  • You can choose to give a priority to the index only for those indexes that are very large and an eventual rebuild rollback could be a costly operation
  • You can choose to group your OIR per indexes and define different priorities for them
  • Whatever priority option you choose you have to think and implement a logic for tracking the operation and generating the results of the OIR process. A good solution is to use the provided xEvent monitoring.

In order to be a great feature it needs a wise planning and implementation, which is true for every feature 🙂

Thanks for reading!