Improve scalability with Buffer Pool Parallel Scan in SQL Server 2022

This blog post is part of the SQL Server 2022 blog series.

Buffer Pool Parallel Scan is a new feature in SQL Server 2022 that improves the scalability of several common scenarios in SQL Server and as a result, could significantly improve the performance of your database workloads.

The buffer pool and buffer pool scans

The buffer pool is the area of memory that SQL Server uses to cache data. All pages must be copied into the buffer pool before they can be used in a query, and it is necessary to scan the buffer pool for a number of operations.

Buffer pool scans are a common internal operation requiring iterating through potentially millions of buffers. For example, a SQL Server instance with 1 TB of buffer space requires a buffer pool scan to iterate over 130 million buffers. This is true for any SQL Server environment regardless of its buffer space usage as the buffer pool scan always iterates through the entire buffer descriptor array to find any buffers that belong to a specific database.

Operations that scan the buffer pool, especially on servers with large amounts of memory, will benefit the most from the new SQL Server 2022 Buffer Pool Parallel Scan capability.

What scenarios are affected?

Operations such as database startup/shutdown, creating a new database, file drop operations, backup/restore operations, Always On failover events, DBCC CHECKDB and DBCC Check Table, log restore operations, and other internal operations (e.g., checkpoint) will all benefit from Buffer Pool Parallel Scan.

In SQL Server 2019 and previous releases, operations that require scanning the buffer pool can be slow, especially on large memory machines such as the M-series Azure SQL virtual machine and large on-premises SQL Server environments. Even log restore operations and availability group failover operations can be impacted. Currently, there’s no way to eliminate this issue prior to SQL Server 2022, and dropping buffers using DBCC DROPCLEANBUFFERS would likely result in some degree of performance degradation as any subsequent query executions will have to reread the data from the database files increasing I/O.

What makes these buffer pool scan operations appear to be slower than expected?

Scanning the buffer pool in SQL Server 2019 and earlier releases is always a serial operation. The larger the machine, the greater the impact and it doesn’t necessarily matter about the size of the operation, this impact can be seen even when creating a new empty database.

This may seem counterintuitive that we would have this experience on larger machines, but it’s due to the serial scan process and the larger amounts of memory SQL Server has to address in regard to the buffer pool.

The scalability of scanning the buffer pool has significantly improved in SQL Server 2022 allowing customers to get the most out of their hardware investments.

SQL Server 2022 Buffer Pool Parallel Scan benefits

• Buffer pool scans are parallelized by utilizing multiple cores.
• Benefits both small and large database operations on larger memory machines.
• Improvement adds buffer pool scan diagnostics to improve supportability and insights with new buffer pool scan events.
• Customers running mission-critical OLTP, hosted service providers, and data warehouse environments will witness the most improvements in overall processing speed.

In SQL Server 2022, the Buffer Pool Parallel Scan feature improves the performance of buffer pool scan operations by utilizing multiple CPU cores. Customers running SQL Server 2022 may see up to a 10 – 30x improvement in executions which were previously slower due to serialized buffer pool scans.

Below is an example of “creating a new database” on an HPE ProLiant DL580 server with 2 TBs of memory with 1.84 TB devoted to the SQL Server buffer pool. 

Creating a new database on a SQL Server 2019 machine took 17.204 seconds whereas the same database creation script took just over 1 second on SQL Server 2022.

Similarly, to simulate a database failover on the same server we took a sample database, set it offline, and then brought it back online. On SQL Server 2019 this event took over 3 minutes and 15 seconds. On SQL Server 2022, the same event took just over 28 seconds.

As we can see, the parallel scan feature improves the Buffer Pool scan performance of database maintenance and SQL Server workloads residing on large-memory machines by adding processing power to scan the buffer pool more efficiently, so even small operations on large machines will show a benefit.

This is a benefit that many customers will witness simply by upgrading to SQL Server 2022 as the capability is enabled by default.

“As a company with 24/7 availability requirements, we are looking forward to embracing all SQL Server 2022 features that can make database failover faster, such as Buffer Pool Parallel Scan, Parallel Redo, and Accelerated Database Recovery (ADR) enhancements. On the development side, we expect to further utilize continuous improvements in the Intelligent Query Processing package. In an environment with a lot of servers and huge databases, even when you have people and resources to deal with performance issues, each feature that can improve performance or fix performance issues automatically or without touching the code is very valuable.”

Miloš Radivojević, Head of MSSQL Database Engineering at Entain.

How does it work?

Functionally, buffer pool scans are parallelized by utilizing multiple cores. There will be one task per 8 million buffers (64 GB) where a serial scan will still be used if there are less than 8 million buffers.

This is one of those features we are very excited about and again, you are just going to notice that SQL Server is even faster now, especially when applications regularly scan the buffer pool on large servers of 1 TB of memory or more.

So, customers can already start looking for slow buffer pool scan events in their current SQL Server deployments?

Yes, with the latest cumulative updates, long buffer pool scans will be visible in the ERRORLOG starting in SQL Server 2016 SP3. The new diagnostics will provide an ERRORLOG message whenever a buffer pool scan takes longer than 10 seconds to complete.

The error log messaging is helpful to collect when you are looking to verify if the buffer pool parallel scan feature will benefit your environment.

Additionally, SQL Server 2022 also adds new Extended Events for scan start/complete, capturing error events, and Flush Cache operations for parallelized buffer pool events.

The main event to focus on is the buffer_pool_scan_complete event which is fired when a buffer pool scan takes longer than a second to complete.

This event contains the elapsed time, parallel tasks, the number of scanned buffers, the command, and the operation. 

Next steps

Buffer Pool Parallel Scan is just one of the many benefits of migrating to SQL Server 2022.

Download the latest release of SQL Server 2022 if you haven’t already done so, and check out the SQL Server 2022 Overview and What’s New references. There are many new features and improved functionality being added to this release.

Learn more

For more information and to get started, check out the following references:

• What’s New in SQL Server 2022
• Operations that trigger buffer pool scan may run slowly on large-memory computers
• Watch the Data Exposed SQL Server 2022 overview YouTube video: SQL Server 2022 Storage Engine Capabilities (Ep. 4) | Data Exposed