Its very important to know how buffer manager works in the sql server to understand the query processing engine
Buffer management is a key component in achieving this efficiency. The buffer management component consists of two mechanisms:
- The buffer manager to access and update database pages
- buffer cache (also called the buffer pool), to reduce database file I/O.
How Buffer management works in SQL server
- Buffer cache is divided into 8 Kb pages
- Buffer management performs two important functions
- Reading data or index pages from the database disk files into the buffer cache
- Writing modified pages back to disk
- A page remains in the buffer cache until the buffer manager needs the buffer area to read in more data.
- Data is written back to disk only if it is modified. Data in the buffer cache can be modified multiple times before being written back to disk
How is buffer cache is allocated:
When SQL Server starts, it computes the size of virtual address space for the buffer cache based on a number of parameters such as the amount of physical memory on the system, the configured number of maximum server threads, and various startup parameters
The two important counters for buffer management are BPOOL_COMMITTED & BPOOL_COMMIT_TARGET in the catalog view SYS.DM_OS_SYS_INFO
SQL Server reserves this computed amount of its process virtual address space (called the memory target) for the buffer cache, but it acquires (commits) only the required amount of physical memory for the current load
BPOOL_COMMITTED represents :
Number of 8-KB buffers in the buffer pool. This amount represents committed physical memory in the buffer pool. Does not include reserved memory in the buffer pool.
BPOOL_COMMIT_TARGET (No pages SQL server acquired for buffer management but not commited) represents :
Number of 8-KB buffers needed by the buffer pool. The target amount is calculated using a variety of inputs such as the current state of the system, including its load, the memory requested by current processes, the amount of memory installed on the computer, and configuration parameters. If the bpool_commit_target is larger than the bpool_committed value, the buffer pool will try to obtain additional memory. If the bpool_commit_target is smaller than the bpool_committed value, the buffer pool will shrink.
The time from sql server started and the buffer cache acquires memory target is called Ramp up
- single-page read request fills a single buffer page
- Ramp-up is expedited by transforming single-page read requests into aligned eight-page requests
Because the buffer manager uses most of the memory in the SQL Server process, it cooperates with the memory manager to allow other components to use its buffers. The buffer manager interacts primarily with the following components:
- Resource manager to control overall memory usage and, in 32-bit platforms, to control address space usage.
- Database manager and the SQL Server Operating System (SQLOS) for low-level file I/O operations.
- Log manager for write-ahead logging.
Buffer manager features :
- The buffer manager supports Hot Add Memory, which allows users to add physical memory without restarting the server.
- The buffer manager supports large pages on 64-bit platforms. The page size is specific to the version of Windows
- The buffer manager is non-uniform memory access (NUMA) aware
The buffer manager only performs reads and writes to the database. Other file and database operations such as open, close, extend, and shrink are performed by the database manager and file manager components.
Disk I/O operations by the buffer manager have the following characteristics:
- All I/Os are performed asynchronously, which allows the calling thread to continue processing while the I/O operation takes place in the background.
- All I/Os are issued in the calling threads unless the affinity I/O option is in use. The affinity I/O mask option binds SQL Server disk I/O to a specified subset of CPUs. In high-end SQL Server online transactional processing (OLTP) environments, this extension can enhance the performance of SQL Server threads issuing I/Os.
- Multiple page I/Os are accomplished with scatter-gather I/O, which allows data to be transferred into or out of noncontiguous areas of memory. This means that SQL Server can quickly fill or flush the buffer cache while avoiding multiple physical I/O requests.
Long I/O Requests
The buffer manager reports on any I/O request that has been outstanding for at least 15 seconds. This helps the system administrator distinguish between SQL Server problems and I/O subsystem problems. Error message 833 is reported and appears in the SQL Server error log as follows:
SQL Server has encountered %d occurrence(s) of I/O requests taking longer than %d seconds to complete on file [%ls] in database [%ls] (%d). The OS file handle is 0x%p. The offset of the latest long I/O is: %#016I64x.
A long I/O may be either a read or a write; it is not currently indicated in the message. Long-I/O messages are warnings, not errors. They do not indicate problems with SQL Server. The messages are reported to help the system administrator find the cause of poor SQL Server response times more quickly, and to distinguish problems that are outside the control of SQL Server. As such, they do not require any action, but the system administrator should investigate why the I/O request took so long, and whether the time is justifiable.
Causes of Long-I/O Requests
A long-I/O message may indicate that an I/O is permanently blocked and will never complete (known as lost I/O), or merely that it just has not completed yet. It is not possible to tell from the message which scenario is the case, although a lost I/O will often lead to a latch time-out.
Long I/Os often indicate a SQL Server workload that is too intense for the disk subsystem. An inadequate disk subsystem may be indicated when:
- Multiple long I/O messages appear in the error log during a heavy SQL Server workload.
- Perfmon counters show long disk latencies, long disk queues, or no disk idle time.
Long I/Os may also be caused by a component in the I/O path (for example, a driver, controller, or firmware) continually postponing servicing an old I/O request in favor of servicing newer requests that are closer to the current position of the disk head. The common technique of processing requests in priority based upon which ones are closest to the current position of the read/write head is known as “elevator seeking.” This may be difficult to corroborate with the Windows System Monitor (PERFMON.EXE) tool because most I/Os are being serviced promptly. Long I/O requests can be aggravated by workloads that perform large amounts of sequential I/O, such as backup and restore, table scans, sorting, creating indexes, bulk loads, and zeroing out files.
Isolated long I/Os that do not appear related to any of the previous conditions may be caused by a hardware or driver problem. The system event log may contain a related event that helps to diagnose the problem.
3 thoughts on “Buffer management in SQL Server”
Well done! very clear and interesting post. SQL server internals are never clear as Oracle’s.