Post reply

Clustered index - lots of locks

  • October 24, 2019 at 3:08 pm

    #3691652

    Hi

     

    There may well be a simple answer to this

    When you create a clustered index on a sizable table, it creates 10's of thousands of KEY locks on sysrscols and sysrowsets

    Is this normal?

     

    Thanks

     

     

    - Damian

  • October 24, 2019 at 3:47 pm

    #3691700

    No, doesn't sound normal.  How did you determine the locking that was occurring?  Does the table have a lot of partitions?

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • October 24, 2019 at 4:25 pm

    #3691709

    It has a partition (not lots) - used for partition switching once that data has been built

    See code:

    ALTER TABLE [Load].[CreditAccountHeader] 
    ADD CONSTRAINT [PK_CreditAccountHeader] PRIMARY KEY CLUSTERED
     ( [CreditAccountHeaderID] ASC, [PartitionKey] ASC ) 
    ON [batch_pfn_sc]([PartitionKey])

    approx 80 million rows of data

     

    Locking showing up in sys.dm_tran_locks and sys.syslockinfo

     

    Thanks

     

    - Damian

  • October 24, 2019 at 7:45 pm

    #3691755

    Keys locks are normal with a clustered index (in fact, they must be key locks, since you can't get rid locks on a ci).  But I wouldn't expect that many normally.

    However, in reality, you should have created the ci before loading the table.  You might want to look into addressing that first rather than chasing the locking afterward.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

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

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