You can change your clustered primary key to Non-clustered Primary Key and can get the benefit of performance by reducing Pagelatch_EX.
I have personally implemented the solution suggested of resolving contention about creating an index with OPTIMIZE_FOR_SEQUENTIAL_KEY at my client’s place.
ur today’s goal is to understand what is the Last Page Insert PAGELATCH_EX Contention Due to Primary Key. Let us start understanding it together.
Recently, one of my clients showed me a very cool trick to check Primary Key for a single table with the help of the system stored procedure sp_pkeys.
Question: What is Trusted Constraint in SQL Server?
Question: How to Create Primary Key Without ANY Index?
Unique key on a column is used to identify a particular row easily. It is created either by explicitly mentioning it either part of the definition or when defined as Primary key (By default it is unique and not null). Let us learn about special stored procedure sp_special_columns customers today.