Post reply

ALTER TABLE ALTER COLUMN WITH (ONLINE=ON) blocking

  • February 12, 2019 at 11:19 am

    #409508

    Surprisingly there was some blocking taking place when testing the ALTER COLUMN command online. It was seemingly due to the column being included in indexes. During the execution of the online alter query, selects continued to complete but inserts were being blocked with LCK_M_IX wait.

    From docs.microsoft.com:
    “While the online alter column operation is running, all operations that could take a dependency on the column (index, views, etc.) will block or fail with an appropriate error. This guarantees that online alter column will not fail because of dependencies introduced while the operation was running.”

    I suppose that the solution would be to remove the column from any non-clustered indexes and views prior to running the alter column operation but it is concerning that the online command does not warn or prevent execution if there are dependent indexes.

    Howard

  • February 14, 2019 at 12:37 am

    #2022026

    What was the exact command? Can you copying it?

  • February 14, 2019 at 8:10 am

    #2022076

    PHXHoward - Tuesday, February 12, 2019 11:19 AM

    Surprisingly there was some blocking taking place when testing the ALTER COLUMN command online. It was seemingly due to the column being included in indexes. During the execution of the online alter query, selects continued to complete but inserts were being blocked with LCK_M_IX wait.

    From docs.microsoft.com:
    “While the online alter column operation is running, all operations that could take a dependency on the column (index, views, etc.) will block or fail with an appropriate error. This guarantees that online alter column will not fail because of dependencies introduced while the operation was running.â€

    I suppose that the solution would be to remove the column from any non-clustered indexes and views prior to running the alter column operation but it is concerning that the online command does not warn or prevent execution if there are dependent indexes.

    Howard

    Disabling non-clustered indexes can put you in a world of hurt if they're unique indexes.  They could be for foreign keys and disabling the index will also disable the FK and it will do so without any notification.  My recommendation would be to tolerate the blocking.  You already know this but I have to say it for others reading this...  If this is something being done on a regular basis (such as for index maintenance), it would be best to schedule it during a low usage time frame.

    As a bit of a sidebar, if this is for index maintenance, consider determining what the actual reason for the fragmentation occurring actually is.  For example, a varchar column that is updated from NULL to any number of characters or a small number of characters to a larger number of characters (I call these "ExpAnsive Updates") will usually cause fragmentation and it can be quite serious when done in a "batch" mode where a large number of rows are updated.  Sometimes those can be fixed by using a properly sized default (sometimes just spaces) to prevent expansion.  Of course, if you have code that depends on NULL values rather than blank values, you don't want to make that change.

    There's also the trick of using CHAR instead of VARCHAR (for example) for anything smaller than, say, 10 bytes, which would still allow you to use NULLs instead of a blank default.  There's also the trick of changing some large VARCHAR (for example) columns to VARCHAR(MAX) and taking the steps to configure the table to force such MAX columns to out-of-row storage (and requires a one time special step to force existing values out of row on existing tables).  I have no idea why MS adopted the default philosophy of storing LOBs "in row if they fit" as of 2005.  It's secretly screwing performance for a lot of people, causes a lot of unnecessary fragmentation on Clustered Indexes, and makes Index Maintenance a whole lot slower and resource intensive.

    Up to and not including 2005, the default used to be to store LOBs out of row.

    Lookup sp_TableOption for more information on LOB storage.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 14, 2019 at 10:48 am

    #2022092

    Thanks for the replies and the warning about constraints. We have not faced a problem with online index rebuilds because the method that SQL Server uses to keep the index online during the rebuild works well.

    The alter column statement looks like this:

    USE database1
    ALTER TABLE table1 ALTER COLUMN column1 char(4) null WITH (ONLINE=ON);

    The original data type was nchar(4). The online hint does keep the table online during the alter but it is not as transparent as we expected because inserts were blocked due, I assume, to the dependency on updating indexes.  Just wanted to bring it to everyone's attention.  We were fortunate that our test environment was busy enough to show us this outcome before we executed an online alter column in production.

    Howard

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

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