In SQL Server 2019, a new index option was added called OPTIMIZE_FOR_SEQUENTIAL_KEY that is intended to address an issue known as last page insert contention. Most of the solutions to this problem that have been suggested in the past involve making changes to either the application or the structure of the contentious index, which can be costly and sometimes involve performance trade-offs. Rather than making major structural changes, OPTIMIZE_FOR_SEQUENTIAL_KEY addresses some of the SQL Server scheduling issues that can lead to severely reduced throughput when last page insert contention occurs. Using the OPTMIZE_FOR_SEQUENTIAL_KEY index option can help maintain consistent throughput in high-concurrency environments when the following conditions are true:
- The index has a sequential key
- The number of concurrent insert threads to the index far exceeds the number of schedulers (in other words logical cores)
- The index has a high rate of new page allocations (page splits), which is most often due to a large row size
This sample illustrates how OPTIMIZE_FOR_SEQUENTIAL_KEY can be used to improve throughput on workloads that are suffering from severe last page insert contention bottlenecks.
About this sample
Before you begin
Run this sample
Disclaimers
Related links
- Applies to: SQL Server 2019 (or higher)
- Workload: High-concurrency OLTP
- Programming Language: T-SQL
- Authors: Pam Lahoud
- Update history: Created August 15, 2019
To run this sample, you need the following prerequisites.
- SQL Server 2019 (or higher)
- A server (physical or virtual) with multiple cores
- The AdventureWorks2016_EXT sample database
[!NOTE]
This sample was designed for a server with 8 logical cores. If you run the sample on a server with more cores, you may need to increase the number of concurrent threads in order to observe the improvement.
-
Copy the files from the root folder to a folder on the SQL Server.
-
Download AdventureWorks2016_EXT.bak and restore it to your SQL Server 2019 instance.
-
From SQL Server Management Studio or Azure Data Studio, run the Setup.sql script.
-
Modify the SequentialInserts_Optimized.bat and SequentialInserts_Unoptimized.bat files and change the -S parameter to point to the server where the setup script was run. For example,
-S.\SQL2019points to an instance named SQL2019 on the local server. -
Open the SQL2019_LatchWaits.htm file to open a Performance Monitor session in your default browser.
-
Right-click anywhere in the browser window to clear the existing data from the session.
-
Click the play button to start the Performance Monitor session.
-
From a Command Prompt, browse to the folder that contains the demo files and run SequentialInserts_Unoptimized.bat, then return to the Performance Monitor window. You should see a high number of Page Latch waits as well as high average wait times. Note the time it takes for the script to complete.
-
Run the SequentialInserts_Optimized.bat script from the same Command Prompt window and again return to the Performance Monitor window. This time you should see much lower number and duration of Page Latch waits, along with higher Batch requests/sec. Note the time it takes for the script to complete, it should be significantly faster than the Unoptimized script.
-
OPTIONAL - Modify the
-n256parameter in the Optimized and Unoptimized scripts to see the effect on performance. Generally, the larger the number of concurrent sessions, the greater the improvement will be with OPTIMIZE_FOR_SEQUENTIAL_KEY.
The code included in this sample is not intended to be a set of best practices on how to build scalable enterprise grade applications. This is beyond the scope of this quick start sample.
For more information, see these articles: