Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 1,982,223 hits
  • Select GETDATE()

    May 2022
    M T W T F S S

Archive for the ‘SQL 2014 Learning Series’ Category

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.


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)



Here is the syntax as shown in books online.



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 »

    SQL 2014 Learning Series # 12 – New DMV – sys.dm_exec_query_profiles (Part 2)

    Posted by blakhani on June 12, 2014

    In my earlier post we have discussed about the use of the DMV. In this blog, we would see demo about the feature. In the last post we have learned that we must enable actual execution plan to get information about the query in sys.dm_exec_query_profiles. How would you do that if the queries are coming from application? You can’t ask application team to add “set statistics profile on” in their code. Well, there are multiple ways to handle such situation. (all of them might cause performance issue as we are doing additional work)

    • Start a Server Side trace (profiler UI has more performance impact) with any one of below events (both not needed)


    Here is the code which can be used to create server side trace. This code is auto generated by profiler. Event id 98 = “Showplan Statistics Profile”

    -- Create a Queue
    declare @rc int
    declare @TraceID int
    declare @maxfilesize bigint
    set @maxfilesize = 5 
    -- Please replace the text InsertFileNameHere, with an appropriate
    -- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
    -- will be appended to the filename automatically. If you are writing from
    -- remote server to local drive, please use UNC path and make sure server has
    -- write access to your network share
    exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL 
    if (@rc != 0) goto error
    -- Client side File and Table cannot be scripted
    -- Set the events
    declare @on bit
    set @on = 1
    exec sp_trace_setevent @TraceID, 98, 10, @on
    exec sp_trace_setevent @TraceID, 98, 12, @on
    -- Set the Filters
    declare @intfilter int
    declare @bigintfilter bigint
    -- Set the trace status to start
    exec sp_trace_setstatus @TraceID, 1
    -- display trace id for future references
    select TraceID=@TraceID
    goto finish
    select ErrorCode=@rc


    • Start an extended event session with event query_post_execution_showplan as shown in UI below.


    We can send the data to a file (if we want the plans to be saved) or send them to ring buffer target.

    ADD EVENT sqlserver.query_post_execution_showplan 
    ADD TARGET package0.ring_buffer

    Once we start any one of above, we should be able to profiler the query plan in the real time. If we use any of the method, it is going to cause performance issue so make sure we are running for limited amount of time. Here is the query which can be used to get the information once traces (profiler or XE) is running.

    SELECT physical_operator_name, 
    FROM   sys.dm_exec_query_profiles p 
           CROSS apply sys.Dm_exec_sql_text(sql_handle) h 
           CROSS apply sys.Dm_exec_query_plan(plan_handle) ph 
    WHERE  session_id <> @@SPID 
           AND session_id NOT IN (SELECT session_id 
                                  FROM   sys.dm_exec_sessions 
                                  WHERE  program_name LIKE 'SQL Server Profiler%') 
    ORDER  BY session_id, 


    To learn more, you can run below query, which should take little more time so that you get chance to look at real time data.

    SELECT * 
    FROM   sys.all_columns c 
           CROSS JOIN sys.all_objects o 
    WHERE  type = 'P' 
    ORDER  BY c.is_computed 

    On my machine, it look takes little more than 4 minutes to complete and in the mean time, we can check data in DMV (using query provided earlier) and see that almost all the work finishes instantly but nested loop takes a lot of time here. And it’s purely due to amount of data I have thrown to nested loop join.

    Hope this helped you in bringing your knowledge one step ahead than earlier.

  • 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 # 11 – New DMV – sys.dm_exec_query_profiles (Part 1)

    Posted by blakhani on June 10, 2014

    How many time you have been asked to find out to know what is the exact piece in the query plan is being executed currently? or Imagine a situation where a query running for hours or query never finishes? Well, the new DMV can provide a new way to troubleshoot the problem. So far, you might have heard two kind of execution plans: estimated execution plan and actual execution plan. As the name says estimated plan can be seen without execution of the query whereas for actual execution plan we need to wait till query execution completes.

    From SQL Server 2014 onwards, product has added functionality so that we can monitor query progress in the real time (i.e. while the query is being execution) using the DMV sys.dm_exec_query_profiles. In other words, we can monitor the progress of a query execution and determine where the query uses the most time. As per book online 

    Monitors real time query progress while the query is in execution. Use this DMV to monitor the progress of a query execution and determine where the query uses the most time. Join this DMV with other system DMVs using the columns identified in the description field. Join this DMV with other performance counters (such as Performance Monitor, xperf) by using the timestamp columns.

    To serialize the requests of sys.dm_exec_query_profiles and return the final results to SHOWPLAN XML, use SET STATISTICS PROFILE ON; or SET STATISTICS XML ON;,

    It must be noted that unless we enable any other kind of actual execution plan (i.e. statistics profile, statistics xml) we will NOT be able to use sys.dm_exec_query_profiles. If we don’t enable them, SQL Server will not capture data to populate into the DMV.

    Let us see a quick demo.

    set nocount on
    Create database QPDemo
    use QPDemo
    CREATE TABLE Balmukund (iID INT, cName char(4000) DEFAULT 'Name')
    DECLARE @loop  INT 
    SET @loop = 1
    WHILE @loop <= 100000
           INSERT INTO Balmukund (iID) VALUES (@loop) 
           SET @loop = @loop + 1

    I have created new database, a table and a column store clustered index on that table. Now, if we run below query, it would take little time to finish.

    Select * from Balmukund where iID > 50


    While the query is running, we can execute below

    Select physical_operator_name, row_count, estimate_row_count, *
    from sys.dm_exec_query_profiles

    As I said earlier, we would NOT get the output. unless actual plan settings are enabled. Now, run below from query window.

    set statistics profile on
    Select * into NewTable from Balmukund where iID > 50
    set statistics profile on


    Now, if we check, the data in the DMV would be populated.


    If we run again, we can see row_count is getting increased.


    There are other useful columns which we would discuss in next blog.

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