Clustered index - lots of locks

Question

Clustered index - lots of locks

DamianC

SSCrazy Eights

Points: 8023
More actions
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

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

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

ScottPletcher SSC Guru Points: 101109 More actions · Answer 1

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

DamianC SSCrazy Eights Points: 8023 More actions · Answer 2

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

ScottPletcher SSC Guru Points: 101109 More actions · Answer 3

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".