Altering a table is taking lot of time

  • Shadab Shah

    SSCarpal Tunnel

    Points: 4759

    Hello,

    I have a table , having 900 records. I tried altering the table but it is taking decades to alter a table with just 900 records.

    The table was not having a PK, so i thought of adding a PK Constraint , but that query is also taking too long.

    Can anybody please help me to understand what is actually the problem over here.

    Thanks.

  • AlexSQLForums

    SSChampion

    Points: 14249

    Oops double post.

    Alex S
  • AlexSQLForums

    SSChampion

    Points: 14249

    You may have another process accessing this table.

    Check and wait or kill

    SELECT

    t1.resource_type,

    t1.resource_database_id,

    t1.resource_associated_entity_id,

    t1.request_mode,

    t1.request_session_id,

    t2.blocking_session_id,

    o1.name 'object name',

    o1.type_desc 'object descr',

    p1.partition_id 'partition id',

    p1.rows 'partition/page rows',

    a1.type_desc 'index descr',

    a1.container_id 'index/page container_id'

    FROM sys.dm_tran_locks as t1

    INNER JOIN sys.dm_os_waiting_tasks as t2

    ON t1.lock_owner_address = t2.resource_address

    LEFT OUTER JOIN sys.objects o1 on o1.object_id = t1.resource_associated_entity_id

    LEFT OUTER JOIN sys.partitions p1 on p1.hobt_id = t1.resource_associated_entity_id

    LEFT OUTER JOIN sys.allocation_units a1 on a1.allocation_unit_id = t1.resource_associated_entity_id

    Alex S
  • Bhaskar.Shetty

    Hall of Fame

    Points: 3054

    Hello,

    Check the activitylog and check if any active transactions locking the table, 900 records is not at all a big table.

  • free_mascot

    One Orange Chip

    Points: 27168

    Are you doing with GUI or commandline i.e. T-sql?

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • Grant Fritchey

    SSC Guru

    Points: 395510

    Sounds like it's probably blocking from other resources accessing the table. Otherwise, it shouldn't take that long at all.

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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 6 posts - 1 through 6 (of 6 total)

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