Post reply

What is the user impact to drop a noncluster index on table while in use?

  • May 13, 2014 at 5:05 am

    #307933

    What is the impact on the users to drop an index on a table while in use? I will recreate the index afterwards. The table is used constantly by a three of processes/users at all times.

  • May 13, 2014 at 5:40 am

    #1712976

    Dropping it, not much. Recreating it however will require table locks unless you have Enterprise edition and specify ONLINE for the index creation.

    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

  • May 13, 2014 at 5:43 am

    #1712978

    you also wont be able to drop it if it's currently in use

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • May 13, 2014 at 6:00 am

    #1712989

    And if that index is needed by the queries, while it's gone you'll see table scans instead of index access, so that impact could be substantial.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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