{"id":41639,"date":"2022-07-07T10:00:00","date_gmt":"2022-07-07T17:00:00","guid":{"rendered":""},"modified":"2024-04-19T10:34:20","modified_gmt":"2024-04-19T17:34:20","slug":"improve-scalability-with-buffer-pool-parallel-scan-in-sql-server-2022","status":"publish","type":"post","link":"https:\/\/cm-edgetun.pages.dev\/en-us\/sql-server\/blog\/2022\/07\/07\/improve-scalability-with-buffer-pool-parallel-scan-in-sql-server-2022\/","title":{"rendered":"Improve scalability with Buffer Pool Parallel Scan in SQL Server 2022"},"content":{"rendered":"\n

This blog post is part of the SQL Server 2022 blog series<\/a>.<\/em><\/p>\n\n\n\n

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

The buffer pool and buffer pool scans<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

What scenarios are affected?<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

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\u2019s 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.<\/p>\n\n\n\n

What makes these buffer pool scan operations appear to be slower than expected?<\/h2>\n\n\n\n

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\u2019t necessarily matter about the size of the operation, this impact can be seen even when creating a new empty database.<\/p>\n\n\n\n

This may seem counterintuitive that we would have this experience on larger machines, but it\u2019s due to the serial scan process and the larger amounts of memory SQL Server has to address in regard to the buffer pool.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

SQL Server 2022 Buffer Pool Parallel Scan benefits<\/h2>\n\n\n\n