• 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 SQL SERVER HEKATON | Tagged ABORT_AFTER_WAIT, raresql, SQL, SQL Server, WAIT_AT_LOW_PRIORITY |

  • Recent Posts

    • SQL Server 2019 -Verbose Truncation Warnings
    • SQL SERVER 2016 – TSQL Enhancements – DROP IF EXISTS
    • 2015 in review
    • SQL SERVER – sp_refreshsqlmodule – How to update the metadata of an object
    • SQL SERVER – Fix – Error – 11553 – EXECUTE statement failed because its WITH RESULT SETS clause specified a non-nullable type for column #%d in result set #%d
  • Archives

    • August 2020 (1)
    • June 2016 (1)
    • December 2015 (1)
    • September 2015 (2)
    • August 2015 (3)
    • July 2015 (1)
    • June 2015 (4)
    • 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

    • Microsoft Azure (9)
    • SQL SERVER (92)
      • ApexSQL (1)
      • Backup & Restore (1)
      • Change Data Capture (4)
      • Data Conversion (3)
      • Data Definition Language (DDL) Statements (2)
      • Database Features (2)
      • Email (1)
      • Errors (20)
      • Full-Text Search (1)
      • Policy Based Management (1)
      • Query Performance (1)
      • Raresql News (3)
      • Reports (1)
      • Security Enhancement (1)
      • SET Statements (1)
      • SQL SERVER TIPS (33)
      • System Stored Procedure (4)
      • Transact-SQL (4)
      • Upgrade (1)
      • User Defined Functions (5)
        • Financial Functions (4)
    • SQL Server 2012 (136)
      • Backup & Restore (1)
      • Behavior Changes (1)
      • Breaking Changes (4)
      • Built-in Functions (25)
        • Analytic Function (7)
        • Conversion Function (4)
        • Cryptographic Functions (1)
        • Date and Time Function (8)
        • Logical Functions (3)
        • String Function (2)
      • Control-of-Flow Language (3)
      • Database Features (27)
      • Deprecated Features (1)
      • Discontinued Features (1)
      • Discontinued Functionality (1)
      • DML Statements (2)
      • Dynamic Management Views and Functions (5)
      • Errors (37)
      • MANAGEMENT COMMAND (1)
      • Mathematical Functions (1)
      • Misc (2)
      • Query and Text Editors (3)
      • Scalability and Performance Enhancements (2)
      • Security Enhancements (3)
      • System Stored Procedures (3)
      • Tips & Tricks (11)
      • Transact-SQL Breakpoints (1)
      • xml (1)
    • SQL SERVER 2014 (6)
      • Database Features (2)
      • New Enhancements (1)
    • SQL Server 2019 (1)
    • SQL SERVER HEKATON (18)
      • Errors (2)
      • Memory optimized table (12)
    • SQL Server Recovery Tools (15)
    • SQL Server Solutions (131)
      • Analytic Functions (1)
      • Backup & Restore (3)
      • Computed column (1)
      • Conversion (8)
      • Datetime (5)
      • Management Command (1)
      • Mathematical Functions (1)
      • metadata (1)
      • Misc (6)
      • Modules (1)
      • Operator (1)
      • Ranking Functions (1)
      • Sequence Object (3)
      • String (7)
      • Triggers (1)
    • SQL Solutions (4)
    • Uncategorized (2)

  • Follow @mimran18
  • Blog Stats

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

    Join 6,412 other followers

  • 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.

Create a free website or blog at WordPress.com.

WPThemes.


Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
%d bloggers like this: