SQL Server, How to Reduce the Last Page Insert Latch Contention
Welcome back guys! Two posts ago i blogged about Choosing the Clustered Key and the last page insert latch contention problem I remember last time we broke up talking about some possibilities that you can adopt such as: Adopt a random clustered key value Reverse index technique Enable OPTIMIZE_FOR_SEQUENTIAL_KEY option in sql 2019 In memory technique Partitioning technique Today we make some considerations on how to reduce the contention of the last page latch (also by giving examples) by applying these methods: Adopt a random clustered key value (GUID) Reverse index technique We will run some tests to measure both the Contention generated and the Execution time. To generate a workload by running multiple simultaneous sessions of our query we use a microsoft tool named RML Utilities that i mentioned (at the momentin only in italian) here We also run the test to have our baseline which is inserting data into a table with an autoincrement integer ID column with a clustered index def...