What we decided to do instead was to look closer at the contention itself and see if there was some way to improve scalability without making major changes to the way the data is physically stored. When thinking about addressing this problem automatically in SQL Server, these options as well as others were considered, but most of them require some sort of performance trade-off and would involve major changes to the database engine.
#Essential sequential how to#
How to resolve last-page insert PAGELATCH_EX contention in SQL Server The following article outlines some of the most common solutions: There are many different methods to address this issue that have been suggested in the past, most of them involve making changes to either the application or the structure of the index. In an OLTP system that is frequently inserting data, PAGELATCH waits will often be observed, particularly on a busy system. This problem can be identified by many threads waiting with a wait type of PAGELATCH_xx on the same page resource in a user database. As concurrency increases, contention for this page in memory will increase, which limits scalability of such workloads.
This means that when you are inserting rows into a table that has a clustered index where the key is a value that is always increasing, such as an identity column or a datetime column, all the new rows will be inserted on the last page of the B-tree.
In a clustered index, the leaf level contains the data pages themselves, ordered by the index key. In SQL Server, indexes are stored as B-trees which are ordered by the index key. These workloads commonly face an issue known as last page insert contention. In SQL Server 2019 CTP 3.1 a new index option was added called OPTIMIZE_FOR_SEQUENTIAL_KEY that is intended to address throughput issues that may occur when a workload has a high number of concurrent inserts.
0 kommentar(er)
