• Home
  • Articles’ Index
  • About

SQL Server Portal

Real world SQL scenarios & its unique & optimized solutions

Feeds:
Posts
Comments
« SQL SERVER HEKATON – Northwind sample database for CTP 1
SQL SERVER – Date formatting in excel »

SQL SERVER HEKATON – WAIT_AT_LOW_PRIORITY

July 7, 2013 by Muhammad Imran

In the earlier versions of SQL Server, whenever you need to rebuild an index (pass DDL statement for an online index), you sometimes end up with deadlock situation. Given below is the script that we use to rebuild an online index in the earlier version of SQL Server.

USE AdventureWorks2012
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);

In SQL Server Hekaton, a solution to this problem has been introduced namely WAIT_AT_LOW_PRIORITY . It allows DBA to control the locking mechanism that is required to rebuild an online index and causing deadlock situations.
Given below is the script :

USE AdventureWorks2012
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
FILLFACTOR = 80,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON,
ONLINE = ON ( WAIT_AT_LOW_PRIORITY
( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) ),
DATA_COMPRESSION = ROW
)
;

In the above script, you can see two arguments MAX_DURATION and ABORT_AFTER_WAIT. Given below are the details.

MAX_DURATION :
It describes the waiting time in minutes, but you can just pass the integer number and MINUTES can be removed from the syntax.

ABORT_AFTER_WAIT:
ABORT_AFTER_WAIT came up with three nice options. It basically provides you the hands on the different locking mechanism. Given below are the details :

NONE : It implements no locking on the online index rebuild operation,it performs operation like a normal scenario.
Given below is the script if you would like to implement NONE in ABORT_AFTER_WAIT.

USE AdventureWorks2012
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
FILLFACTOR = 80,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON,
ONLINE = ON ( WAIT_AT_LOW_PRIORITY
( MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE ) ),
DATA_COMPRESSION = ROW
)
;

SELF: It aborts the online index rebuild operation by using normal priority. It gives priority to the user operation instead of rebuilding index.
Given below is the script if you intend to implement SELF.

USE AdventureWorks2012
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
FILLFACTOR = 80,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON,
ONLINE = ON ( WAIT_AT_LOW_PRIORITY
( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = SELF) ),
DATA_COMPRESSION = ROW
)
;

BLOCKERS: It kills all the user transactions that usually block the online index rebuilding, so that you can rebuild index easily. This is not recommended if you are using at the peak hours.
Given below is the script if you would like to implement BLOCKERS.

USE AdventureWorks2012
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
FILLFACTOR = 80,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON,
ONLINE = ON ( WAIT_AT_LOW_PRIORITY
( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS) ),
DATA_COMPRESSION = ROW
)
;

Let me know if you implement it in CTP 1 and find any issues.

Rate this:

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Like this:

Like Loading...

Posted in Functions, SQL SERVER | Tagged ABORT_AFTER_WAIT, raresql, SQL, SQL Server, WAIT_AT_LOW_PRIORITY |

  • Recent Posts

    • SQL SERVER – How to identify delayed durabilty is disabled using Policy Based Management
    • SQL Server 2022 – Improved backup metadata – last_valid_restore_time
    • SQL Server 2022 – TSQL – QAT_DEFLATE – Default Database Backup Compression Algorithm
    • SQL Server 2022 – How to Install Intel® Quick Assist Technology
    • SQL Server 2022 – TSQL – MS_XPRESS – Default Database Backup Compression Algorithm
  • Archives

    • January 2023 (1)
    • December 2022 (12)
    • November 2022 (17)
    • August 2020 (1)
    • June 2016 (1)
    • December 2015 (1)
    • September 2015 (2)
    • August 2015 (3)
    • July 2015 (1)
    • June 2015 (3)
    • May 2015 (3)
    • April 2015 (1)
    • March 2015 (3)
    • January 2015 (5)
    • December 2014 (8)
    • November 2014 (2)
    • July 2014 (4)
    • June 2014 (3)
    • May 2014 (4)
    • April 2014 (5)
    • March 2014 (10)
    • February 2014 (13)
    • January 2014 (14)
    • December 2013 (13)
    • November 2013 (13)
    • October 2013 (8)
    • September 2013 (23)
    • August 2013 (15)
    • July 2013 (26)
    • June 2013 (25)
    • May 2013 (28)
    • April 2013 (27)
    • March 2013 (26)
    • February 2013 (25)
    • January 2013 (22)
    • December 2012 (7)
    • November 2012 (2)
    • October 2012 (24)
    • September 2012 (11)
    • August 2012 (9)
    • July 2012 (5)
    • June 2012 (1)
    • May 2012 (1)
    • April 2012 (5)
    • February 2012 (3)
    • January 2012 (1)
    • December 2011 (2)
    • October 2011 (1)
  • Categories

    • SQL SERVER (440)
      • Audit (6)
      • Backup & Restore (4)
      • Data Definition Language (DDL) Statements (1)
      • Database (2)
      • DML (5)
      • Dynamic Management Views (2)
      • Errors (66)
      • Extension (2)
      • Features (8)
      • Functions (59)
      • Performance (6)
      • Raresql News (9)
      • Security Enhancement (2)
      • SET Statements (1)
      • Solutions (225)
      • System Stored Procedure (8)
      • Tables (6)
      • Tips (26)
      • Tools (4)
      • Trainings (3)
      • Triggers (1)

  • Follow @mimran18
  • Blog Stats

    • 3,973,800 hits
  • Enter your email address to follow this blog and receive notifications of new posts by email.

    Join 6,409 other subscribers
  • Find us on Facebook

    Find us on Facebook
  • Disclaimer

    This blog/website is a personal blog/website and all articles, postings and opinions contained herein are my own. The contents of this blog/website are not intended to defame, purge or humiliate anyone should they decide to act upon or reuse any information provided by me. Comments left by any independent reader are the sole responsibility of that person. Should you identify any content that is harmful, malicious, sensitive or unnecessary, please contact me via email (imran@raresql.com) so I may rectify the problem.

Blog at WordPress.com.

WPThemes.


  • Follow Following
    • SQL Server Portal
    • Join 338 other followers
    • Already have a WordPress.com account? Log in now.
    • SQL Server Portal
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Copy shortlink
    • Report this content
    • View post in Reader
    • Manage subscriptions
    • Collapse this bar
%d bloggers like this: