Post reply

Syntax for adding a column with the WAIT_AT_LOW_PRIORITY Option

  • August 13, 2014 at 3:57 am

    #289711

    Hi,

    I am trying to get the syntax correct below but can't get it to compile - driving me crazy :crazy:

    ALTER TABLE [AdventureWorks2012].[dbo].[ErrorLog]

    ADD DemoColumn4 varchar(10) NULL

    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1, ABORT_AFTER_WAIT = SELF) ;

    I am trying to add a column to a table but wet the WAIT_AT_LOW_PRIORITY

    Thanks in advance for any help.

  • August 13, 2014 at 4:55 am

    #1737509

    I don't think the WAIT_AT_LOW_PRIORITY option is valid in ALTER TABLE ADD

    Looks like it can only be used in online index operations.

  • August 13, 2014 at 5:53 am

    #1737534

    WAIT_AT_LOW_PRIORITY is for online index builds

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • August 13, 2014 at 5:59 am

    #1737535

    Plus, adding a nullable varchar column is, as far as I recall, a metadata only operation, so it's going to be extremely fast. It does need an exclusive schema lock, but it won't run ages once it has the lock.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 13, 2014 at 5:58 pm

    #1737863

    Hi all,

    Thanks for the replies. I am currently just doing tests to put a case forward for moving the Data Warehouse Team to SQL 2014 as we have having so many issues with SCH_M and SCH_S blocking in our AlwaysOn environment as a result of schema changes on the primary while long running queries are running on the secondary.

    I was hoping to test both index rebuilds and ALTER TABLE ADD or DROP column commands as both cause blocking for us

    If you cannot use WAIT_AT_LOW_PRIORITY with ALTER TABLE ADD/DROP then it would only resolve the blocking issues we have when Rebuilding Indexes - not Alter Column schemas on tables - which is unfortunate.

    I tired to follow the syntax chain on the below and it seemed to be a valid option but maybe not!

    http://msdn.microsoft.com/en-AU/library/ms190273.aspx#add

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply