Memory Bottleneck Analysis of SQL Server Data Page Buffer

SQL Server caches frequently used data in memory (that is, data page cache) to speed up data access. Because the disk access speed is much lower than the memory, reducing disk access volume is also an important aspect of database optimization.

When there is insufficient memory in the cache area of the data page, slow query and busy disk issues may occur.

Analysis Method: performance counters are mainly used.

View the following performance counters:

1. SQL SERVER: Buffer Manager-Lazy Writes/sec: if the memory is insufficient, Lazy Writer is frequently called to write data to the disk. This value is often not 0.

2. SQL SERVER: Buffer Manager-Page life expectancy: When the memory is insufficient, this counter shows a downward trend or stays at a low value.

3. SQL SERVER: Buffer Manager-Page reads/sec: When the memory is insufficient, you do not need to read the disk when querying data that is frequently used but not cached in the memory, this value is sustained or stuck at a high value.

4. SQL SERVER: Buffer Manager-Stolen pages: Stolen pages are usually used to cache execution plans for reuse. When the memory is insufficient, the SQL Server mechanism first clears the execution plan cache. This value is displayed as a decrease or a low level.

Query the current user task wait:

Copy codeThe Code is as follows:
Select * from sys. sysprocesses

If the memory is insufficient, a large number of ASYNC_IO_COMPLETION wait types are displayed. This is because when memory is insufficient: a. frequent interaction between memory and disk, increased Disk Load B. Data on the disk needs to be read for query and increased Disk Load.

That is to say, at this time the disk also encountered a performance bottleneck, but this is only a "surface", we need to combine multiple performance indicators to identify the root cause is "insufficient memory ".

Determine pressure sources and solutions:

The memory bottleneck related to data page cache is determined through the previous analysis. It is necessary to analyze why this is the case and the solution. It can be divided into the following five aspects:

1. External Pressure

If the OS level or other application services require more memory, windows will compress the memory size of Database Pages. The memory pressure comes from the outside. You can check the following performance counters to determine whether they are under external pressure:

1. SQL Server: Memory Manager-Total Server Memory: the counter value drops.

2. Memory: Available Mbytes: this value is reduced to a lower level.

3. If AWE or Lock page in memory is not used, check Process: Private Bytes-SqlServer and Process: Working Set-SqlServer. The values of both are significantly reduced.

Solution: for non-DB dedicated servers, you must weigh the importance of each application service to allocate memory or increase the memory. Try to make the Server run only SQL Server and become a dedicated DB Server.

2. SQL Server's own pressure on Database Page

When the Total Server Memory has reached the Set Max Server Memory or cannot obtain more Memory from the OS, but the amount of frequently accessed data is much larger than the physical Memory used for data cache capacity, SQL Server is forced to move memory data into and out to complete the current query.

Observe the following performance counters:

1. The values of SQL Server: Memory Manager-Total Server Memory and SQL Server: Memory Manager-Target Server Memory are equal. However, the former is not greater than the latter.

2. There will be a situation described in "analysis method.

Solution: Since SQL Server does not have enough memory to store the Database Page, you can either increase the memory used by SQL Server or reduce the memory used by SQL Server.

Increase: You can add physical memory and enable AWE.

Reduction: through horizontal scaling, two or more servers can load part of the database separately, and statements with a large read volume can be optimized.

3. Stolen Memory pressure in the Buffer Pool

Under normal circumstances, Stolen Memory in the Buffer Pool will not put pressure on Database Pages. Because Database Pages is under pressure, Lazy Writes is triggered, and SQL Server clears the execution plan cache in Stolen Memory.

However, if the user declares too many objects, but does not log out, and occupies too much memory, the Database Pages will be compressed. For example: cursor, custom referenced execution plan, etc.

Solution: a) the user-submitted request cannot be completed due to insufficient memory, with a 701 error. B) The user needs to compress the memory of some clers to complete the user request, latency and slowness.

Query the Single_pages_kb field of sys. dm_ OS _memory_clerks to find out which clerks uses too much memory and analyze the cause, and then solve the problem.

4. Multi-Page pressure

Multi-page and Buffer Pool share the virtual address space of the OS. If multi-page uses too much memory, Datbase pages will be compressed. The multi-page memory usage is generally small and relatively fixed, and may occur in the following situations:

A. 32-bit SQL Server without AWE enabled only has 2 GB address space, and the maximum MemToLeave extended by the-g startup parameter is used.

B. 64-bit SQL Server calls Memory leakage third-party code.

C. Use a "IN" statement with a large number of parameters or a long length

D. Increase the Network Packet Size, which is greater than or equal to 8 KB and has many such connections.

E. A large number of complex XML queries, or third code.

Solution: query the multi_pages_kb field of sys. dm_ OS _memory_clerks to find out which clerk uses too much memory and analyze the cause, and then solve the problem.


Author: Joe. TJ