Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 1,702,994 hits
  • Select GETDATE()

    December 2019
    M T W T F S S
    « Nov    
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  

Posts Tagged ‘Managed Lock Priority’

SQL 2014 Learning Series # 15 – New Feature – Managed Lock Priority (Part 3)

Posted by blakhani on June 24, 2014


In earlier two blogs (Part 1 and Part 2) we have seen various new options added to support MLP in online Index rebuild using ALTER INDEX. Same holds true for ALTER TABLE … SWITCH PARTITON as well. In this blog, we would see new enhancements done in DMVs and Extended Events support these options.

Enhancement in DMVs. As per books online, request_status column in sys.dm_tran_locks has three new values added to support this feature. LOW_PRIORITY_CONVERT, LOW_PRIORITY_WAIT and ABORT_BLOCKERS. In the similar way, the wait_type column in sys.dm_os_wait_stats has new values, LOW_PRIORITY and ABORT_BLOCKERS.

In previous blog, we have seen enhancements done in ERRORLOG logging. If you are familiar will the “KILL” command in SQL Server, you must be aware that this is logged into ERRORLOG as well. In the same fashion, if any session is aborted due to ALTER DDL command, it would also be logged in ERRORLOG. We would get information about hostname, ObjectName and session ID which I have put below for reference.

2014-06-19 09:00:20.850 spid55       An ‘ALTER INDEX REBUILD’ statement was executed on object ‘OnlineIndexRebuild’ by hostname ‘SQLServerHelp’, host process ID 13560 using the WAIT_AT_LOW_PRIORITY options with MAX_DURATION = 1 and ABORT_AFTER_WAIT = BLOCKERS. Blocking user sessions will be killed
2014-06-19 09:01:21.780 spid55       An ABORT_AFTER_WAIT = BLOCKERS lock request was issued on database_id = 7, object_id = 245575913. All blocking user sessions will be killed.
2014-06-19 09:01:21.780 spid55       Process ID 53 was killed by an ABORT_AFTER_WAIT = BLOCKERS DDL statement on database_id = 7, object_id = 245575913.

Enhancement in xEvents Let’s look at same example of online index rebuild which we have used, in earlier blog, to learn more about extended events added to support monitoring of managed lock priority feature. There are three new events which have been introduced which will help us in diagnostics.

  1. ddl_with_wait_at_low_priority – this event is fired when a ALTER INDEX / TABLE DDL statement is executed using the WAIT_AT_LOW_PRIORITY option.
  2. lock_request_priority_state – The priority state of a lock request.
  3. process_killed_by_abort_blockers – this event occurs if we chose ABORT = BLOCKERS and a process is killed due to that. This event would have information about who was killed by whom, what the was DDL fired etc.

Let’s create an extended event session and capture some data by reproducing the scenario. If you are comfortable with T-SQL then use below.

CREATE EVENT SESSION [MLP] ON SERVER 
ADD EVENT sqlserver.ddl_with_wait_at_low_priority(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.lock_request_priority_state(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.process_killed_by_abort_blockers(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text)) 
ADD TARGET package0.event_file(SET filename=N'MLP')
WITH (STARTUP_STATE=OFF)
GO

You can also use “New Session Wizard” as shown below and make sure that you choose new events which are listed above.

image

Let’s start the session first.

ALTER EVENT SESSION [MLP] ON SERVER STATE=START

Let’s create database (MLPDemo), table (OnlineIndexRebuild) and populate it. Refer earlier blog for script. I have executed below T-SQL and as per my server, SPID 60 is running select statement which is going to block ALTER INDEX DDL command.

BEGIN TRAN 
SELECT count(*) 'Count'
FROM   OnlineIndexRebuild (HOLDLOCK) 
WHERE  iID <= 1000  

Select @@SPID '@@SPID'
-- ROLLBACK TRAN

image

In another session, we would now execute ALTER INDEX DDL.

Select @@SPID 'SPID'
go

ALTER INDEX PK_OnlineIndexRebuild ON OnlineIndexRebuild
REBUILD WITH 
( 
ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) )
)

image

and as expected it went to executing state and got blocked by 60. After 1 minute, SPID 60 was killed to complete rebuild process which was started by SPID 64. Here is what is logged in MLP session which we created using T-SQL.

SNAGHTML337c4ac9

At row # 1, we started DDL command and “ddl_with_wait_at_low_priority” event is fired. This event captured abort_after_wait column as “BLOCKERS” and also shows object_name. Then we can see next event lock_request_priority_state where it shows the lock mode. The process was able to acquire schema shared (SCH_S) lock, intent shared lock (IS) and shared (S) lock. But when it came for schema-modification (SCM_M) lock (which is after build phase is complete) it waited (row #6). Exactly after MAX_DURATION (notice time gap between row # 6 and 7) the time got expired and “abort blockers” action was taken (row 8). Due to that we are seeing “process_killed_by_abort_blockers” and in “killed process id” column shows as 60 as it’s the session which was doing Select (refer image # 2)

Below is the same profiler with ABORT_AFTER_WAIT = NONE (see row # 1 below). Notice below that after expiration on the time (row # 7 below) the state was “Normal Priority” as oppose to Abort Blockers. Nothing was killed here.

SNAGHTML337bbd2f

You can check yourself and find what would happen if we give ABORT_AFTER_WAIT = SELF.

At this point, we have covered various aspects of MLP. It’s time to cover the limitations and good practices about this feature. Below are some limitations.

  • One of the limitation which we should keep in mind (and it’s obvious as well) that even if we specify kill blockers, SQL Server would not kill any system transactions to get locks. SQL Server would kill only user blocking sessions. There could be situations where system transactions (ghost cleanup, shrink and others) is causing blocking as they also acquire some locks and those would not be killed by SQL Server.
  • Another limitation is with AlwaysOn and Replication. The low priority queue are only available on primary replica (in availability group) or publisher (in replication) . If DDL has to move and execute (via data movement) on secondary replica in AlwaysOn Availability Group or Subscriber in replication – it would always run in normal queue.
  • SWITCH PARTITION can only be used for between two tables or indexed views.
  • SSMS doesn’t show the options for MLP. SMO and PowerShell can be used though.

We also need to keep in mind that MAX_DURATION setting can affect the transaction log flush. If you decided to choose a big number, make sure that you have enough disk space to hold that much of transaction log because it can’t be flushed/truncated. The size of transaction log can effect database recovery, replication performance, availability group performance etc. So it’s always good if you can predict how much time OIR would take and manage the space accordingly.

I truly hope that this blog has covered the pending things about this new feature, managed lock priority. We would continue the series and cover other features as well.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in SQL 2014 Learning Series, SQL Server 2014 | Tagged: , , , , , | Leave a Comment »

    SQL 2014 Learning Series # 14 – New Feature – Managed Lock Priority (Part 2)

    Posted by blakhani on June 19, 2014


    In last part Managed Lock Priority (Part 1) we have seen the reason of introducing such behavior. In this part, we would see some demo about this feature. I would try to cover as many demo as I can.

    Demo 1 : ABORT_AFTER_WAIT = NONE

    -- Create Database 
    CREATE DATABASE MLPDemo 
    go 
    
    -- Create Table in Database 
    USE MLPDemo 
    go 
    CREATE TABLE OnlineIndexRebuild 
      ( 
         iID     INT, 
         vcFName VARCHAR(100), 
         vcLName VARCHAR(100), 
         CONSTRAINT PK_OnlineIndexRebuild PRIMARY KEY (iID) 
      ) 
    
    go 
    
    -- Populate Table
    SET nocount ON 
    go 
    DECLARE @iLoop INT 
    SET @iLoop = 1 
    WHILE @iLoop <= 100000 
      BEGIN 
          INSERT INTO OnlineIndexRebuild 
          VALUES      (@iLoop, 
                       'FName' + CONVERT(VARCHAR(10), @iLoop), 
                       'LName' + CONVERT(VARCHAR(10), @iLoop) ) 
          SET @iLoop = @iLoop + 1 
      END 
    go 
    SET nocount OFF 
    go   
    

    Now, we are going to take a shared lock on the table and run index rebuild in separate query window. You may need to modify the SPID as per your demo environment.

    Query 1: Taking Shared Lock and Holding it.

    BEGIN TRAN 
    SELECT count(*) 'Count'
    FROM   OnlineIndexRebuild (HOLDLOCK) 
    WHERE  iID <= 1000  
    
    Select @@SPID '@@SPID'
    -- ROLLBACK TRAN
    

     

    image

    Query 2: Running Online Index Rebuild with MLP settings.

    Select @@SPID 'SPID'
    go
    
    ALTER INDEX PK_OnlineIndexRebuild ON OnlineIndexRebuild
    REBUILD WITH 
    ( 
    ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = NONE ) )
    )
    

     

    image

    Since we have asked SQL Server to run this work on lower Priority, It would wait behind 53 (refer earlier screenshot) but only for 1 minute.

    select session_id, blocking_session_id, wait_resource, wait_time, wait_type
    from sys.dm_exec_requests where session_id = 55
    

    image

     SELECT request_session_id
        ,request_mode
        ,request_type
        ,request_status
        ,resource_type
    FROM sys.dm_tran_locks
    WHERE request_session_id = 55

    image

    After MAX_DURATION is elapsed, we would see below in the same DMVs. This confirms that after MAX_DURATION the DDL thread came to normal queue.

    image

    image

    Demo 2 : ABORT_AFTER_WAIT = SELF

    This option is self-explanatory. Suicide Smile

    image

    Demo 3 : ABORT_AFTER_WAIT = BLOCKERS

    As the options says, SQL Server is going to kill all blockers. I have run same command again with this option and as expected, the command got executed after waiting for 1 minute. (Please note that it waited for 1 minute because there was a session blocking it)

    image 

    Here is the message in ERRORLOG

    2014-06-19 09:00:20.850 spid55       An ‘ALTER INDEX REBUILD’ statement was executed on object ‘OnlineIndexRebuild’ by hostname ‘SQLServerHelp’, host process ID 13560 using the WAIT_AT_LOW_PRIORITY options with MAX_DURATION = 1 and ABORT_AFTER_WAIT = BLOCKERS. Blocking user sessions will be killed

    2014-06-19 09:01:21.780 spid55       An ABORT_AFTER_WAIT = BLOCKERS lock request was issued on database_id = 7, object_id = 245575913. All blocking user sessions will be killed.

    2014-06-19 09:01:21.780 spid55       Process ID 53 was killed by an ABORT_AFTER_WAIT = BLOCKERS DDL statement on database_id = 7, object_id = 245575913.

    If we go back and look at first image, it was SPID 53 which had locks held due to HoldLock hint and hence killed. Here is what we would see if try to execute the query in the session which was killed.

    Msg 0, Level 20, State 0, Line 0

    The connection is broken and recovery is not possible.  The connection is marked by the server as unrecoverable.  No attempt was made to restore the connection.

     

    Also notice the time gap between green highlighted time. This means whenever such request is submitted, the message is written into ERRORLOG. And if sessions are killed, its also logged in ERRORLOG.

    We will see more demos in next part of MLP.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in SQL 2014 Learning Series, SQL Server 2014 | Tagged: , , , , , | 3 Comments »

    SQL 2014 Learning Series # 13 – New Feature – Managed Lock Priority (Part 1)

    Posted by blakhani on June 17, 2014


    Imagine a situation where DBA wants to perform maintenance operations like online index rebuild [using ALTER INDEX…REBUILD (ONLINE=ON)] or SWITCH partition [using ALTER TABLE… SWITCH PARTITION] on a highly OLTP system. Due to kind of locks taken by these activities, it’s highly possible that there would be blocking. If DDL is running first then it would block user workload. If user workload is already running and we need to perform DDL then our query/operation would be blocked. This might not be a problem for partition switch because it’s normally quick but can be a problem which you might have seen during online index rebuild.

    Let’s understand these operation in little more detail to understand the reason of this feature.

    Partition switch is a metadata operation which means it has to update some system tables and setup appropriate links in system tables so that SQL Server knows that you have bought a staging table into an existing table. To achieve this, SQL Server needed to take Schema Modification (SCH-M) lock on the source and the target table. Since SCH-M lock is incompatible with Shared Lock (S), this can block production users activity. The converse is also true. If we have a very heavy OLTP system and the table is very hot in terms on workload, it’s possible that we may not get a windows to acquire the lock and partition switch has to wait. 

    Online Index Rebuild (a.k.a. OIR) process requires table short table S lock at the beginning, in the build phase, where it need to take consistent snapshot of the table and create a version of the table. The version store is used to manage and create new index. After the index is built, in the last phase, process has to do a metadata changes (just like partition switch) where it needs SCH-M lock. This can be blocked or can block concurrent user workload depending on who came first. In order to execute the DDL statement for OIR, all active blocking transactions running on a particular table must be completed. Here also, converse is true.

    In earlier version of SQL Server, there was no option to provide priority of lock taken by certain operation (except deadlock_priority). Now, we have option to provide our choice during online index rebuild and partition switch.

    Kill all blockers – When we execute the DDL (either ALTER TABLE .. SWITCH PARTITON or ALTER INDEX… REBUILD (ONLINE=ON)), we can specify to kill all user session which are blocking the activity and start the DDL. This is typically a business decision depending on priority of user workload and time of the day. If we don’t want to kill the blockers immediately then we have an option to ask the operation to wait for certain time which can be specified by “MAX_DURATION” switch. The number provided after MAX_DURATION is number of minutes the DDL should wait before killing the sessions. At the end of the duration, if DDL process still cannot get a lock, it can go ahead and kill all user transactions. This fact might be intuitive but I must point out here – If we specify certain value in MAX_DURATION (let’s say 5 minutes) but there are no concurrent workload on the system taking conflicting locks, the request would be processes immediately (and will not wait for 5 minutes). Max value possible is 71,582 minutes (=49 days)

    Switch to normal queue – This is the default behavior in SQL Server 2012 where DDL operation waits in the same lock queue where other transactions are waiting for lock. SQL Server lock manager is First In First Out (FIFO) model which means if we submit a lock request, it won’t be granted until the earlier requesters have been granted their lock and have been release also. This option means that if our DDL didn’t acquire the lock even after MAX_DURATION time is elapsed, it would switch to normal queue and wait behind existing user requests. This is default behavior.

    Exit DDL after wait – This option is opposite to first option (kill all blockers). If business says that user workload is more important than these maintenance operation then DBA would pick this choice. As the name implies, the DDL (SWITCH/OIR) would wait till the MAX_DURATION and if the locks are not acquired for maintenance operation, the DDL would abort itself and end-user would get “Timeout Expired” message.

    Using above three options, a DBA should be able to manager partition switch and online rebuild operation. This all the possible due to a new lock queue introduced in SQL Server 2014 called as “Low Priority Lock Queue” and that’s why it doesn’t interfere with regular user workload.

    Below image shows that all SPIDs are waiting in same queue. SPID 53 is a maintenance activity which is having conflicting lock and hence waiting.

    image

    Below would be the situation with the new lock queue. As we can see SPID 54 is NOT waiting for SPID 53 (as compared to regular queue in earlier image)

    image

     

    Here is the syntax as shown in books online.

    (ALTER TABLE) http://msdn.microsoft.com/en-us/library/ms190273(v=sql.120).aspx 
    (ALTER INDEX) http://msdn.microsoft.com/en-us/library/ms188388(v=sql.120).aspx

    image

    Few points I must highlight:

    • MAX_DURATION is not the duration of the operation itself. It’s the max wait duration.
    • Specifying very high value in MAX_DURATION would not be advisable because it prevents the transaction log from truncating from the point the DDL was submitted until it is executed . 71,582 minutes (1,193 hours or 49 days) unless you have the transaction log drive space to support 49 days’ worth of transactions, you might want to keep the MAX_DURATION a bit lower.

    In next part of this series, we would look at example and demo of MLP feature.

    To look at complete list of blog on SQL 2014 Learning Series, please visit here

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in SQL 2014 Learning Series, SQL Server 2014 | Tagged: , , , , , | 2 Comments »