SQL Server caches frequently used data in memory (that is, the data page cache) to increase the speed of data access. Because disk access is much slower than memory, reducing disk traffic is also an important aspect of database optimization.
When there is not enough memory in the data page buffer, there are problems such as slow query, disk busy, and so on.
Analysis method: The main use of performance counters.
View the following performance counters:
1. SQL server:buffer manager-lazy writes/sec: Low memory frequently calls Lazy writer writes several data to disk, which is often not 0.
2. SQL Server:buffer manager-page Life expectancy: when there is not enough memory, this counter behaves as a downward trend or stays at a lower value.
3. SQL Server:buffer manager-page reads/sec: When memory is low, querying for data that is often used but not cached in memory does not require reading the disk, which is shown to continue to rise or stay at a higher value.
4. SQL Server:buffer Manager-stolen pages:stolen pages are typically used to cache execution plans for reuse. When there is not enough memory, the SQL Server's own mechanism takes precedence over the execution plan cache, and this value behaves as a drop or lower level.
Query the current user task wait:
Copy Code code as follows:
SELECT * FROM sys.sysprocesses
If there is not enough memory, you will see more async_io_completion waiting types. This is due to low memory: a. Frequent interaction between memory and disk, increased disk load B. You need to read the data on the disk to complete the query and the disk load is increased.
This means that the disk also has a performance bottleneck, but this is only "surface", we have to combine multiple performance indicators to identify the root cause is "low memory."
identify pressure sources and solutions:
The memory bottleneck associated with the data page cache is determined by the previous analysis. It is necessary to analyze why this is the case and the solution. Mainly divided into the following 5 aspects:
1. External pressure
If the OS level or other application services require more memory, Windows compresses the amount of memory in database pages. Then the memory pressure comes from the outside. You can view the following performance counters to determine whether external pressure is available:
1. SQL Server:memory manager-total Server Memory: This counter value will drop.
2. Memory:available Mbytes: This value will descend to a lower level.
3. Viewing process:private bytes-sqlserver and process:working set-sqlserver without using AWE or lock page in memory will have a significant drop in both values.
WORKAROUND: If you are not a DB dedicated server, weigh the importance of each application service to allocate memory or increase memory. Try to make the server run only SQL Server and become a DB dedicated server.
2. SQL Server's own use of database page pressure
When total server memory has reached the set max server memory or cannot get more memory from the OS, but the amount of data that is frequently accessed is much greater than the capacity of physical memory for the data cache, SQL Server is forced to move the memory data into and out. Used to complete the current query.
observe the following performance counters:
1. SQL Server:memory manager-total server Memory and SQL Server:memory manager-target server Memory Both values will be equal. But the former will not be greater than the latter.
2. The situation described in the "analytical method" will appear.
WORKAROUND: Since SQL Server does not have enough memory to hold the database Page, it either increases the amount of memory used by SQL Server or reduces the memory it uses.
Add: You can add physical memory, enable AWE and other methods.
Reduction: can be extended horizontally, there are two or more servers to load part of the library separately, optimize the relevant reading volume statements, and so on.
3. Stolen memory pressure in Buffer pool
Normally the stolen memory in the buffer pool will not put pressure on the database pages. Because the database pages are stressed, lazy writes are triggered and SQL Server cleans up the execution plan cache in stolen memory.
However, if the user declares too many objects and does not log out, and consumes too much memory, it compresses the database Pages. such as cursors, custom referenced execution plans, and so on.
Workaround: Typically, a user submits a request that is not able to complete with a memory shortage, 701 error, B, and needs to compress some of the clerk memory to complete the user request, resulting in delayed response and slowness.
By querying the Sys.dm_os_memory_clerks field single_pages_kb, find out which clerk is using too much memory and analyze its causes, and then resolve it.
4. Pressure of multi-page
multi-page and buffer pool share the OS virtual address space, and if multi-page uses too much memory, it compresses datbase pages. multi-page memory usage is generally small and relatively fixed, which may occur:
A. 32-bit SQL Server that does not open AWE has only 2G address space and the upper bound of the MemToLeave with the-G startup parameter extension.
B. 64-bit SQL Server has the third party code for memory leaks.
C. Use a large number of parameters or longer "in" statements
D. Increase the network Packet Size, greater than or equal to 8KB, and more such connections.
E. A large number of complex XML queries, or third code.
Workaround: By querying the Sys.dm_os_memory_clerks field multi_pages_kb, find out which clerk uses too much memory and analyzes its causes, and then resolves it.
Author: joe.tj
