SQL Server performance optimization: memory management of resource management (Part II)

In the previous article, performance tuning of SQL Server: memory management of resource management (I) introduces some theoretical knowledge about memory management of SQL Server, this article uses this knowledge to solve some common problems in reality.

 

I. Optimization of data page cache pressure

As we mentioned in the previous article, if the data pages accessed by users are cached in the memory, the corresponding speed is the fastest. However, in reality, the database size is greater than the physical memory, and SQL Server cannot cache all the data you need in the memory. When the data you need is not in the memory, paging will occur to read the required data from the hard disk. Occasionally Paging will not affect the performance of SQL Server as a whole, however, if Paging occurs frequently, the overall performance of SQL Server will be seriously affected.

When we optimize the data page cache, the first step is to determine whether there is any data page cache pressure, and the second step is to determine where the data page cache pressure is caused, it can be divided into external pressure and internal pressure.

1. Is there any data page cache pressure?

Determine whether there is any data page cache pressure, mainly from some of the following memory performance counters and sys. sysProcesses to confirm.

SQL Server: Buffer Manager-Lazy Writes/Sec values often occur.

SQL Server: the Buffer Manager-Page Life Expectancy is constantly changing and cannot be upgraded.

SQL Server: the value of Buffer Manager-Page Reads/Sec is usually not 0.

From sys. the ASYNC_IO_COMPLETION value can be seen in the wait_type System View of sysprocesses. This value indicates "waiting for the completion of I/O operations", which usually indicates that hard disk read/write occurs due to insufficient memory, some may also say that this is caused by the slow speed of the hard disk. You only need to replace the hard disk with a fast speed to solve this problem. Changing to a fast hard disk can improve the SQL Server's response speed. However, if the values of the three counters above are frequent, the hard disk issue is not a major problem, it is only caused by insufficient memory (because). The root cause is the memory.

From the above analysis, we can confirm that there is data page cache pressure in the system. Now we will analyze the source of this pressure, whether it is external pressure or internal pressure.

 

2. Sources of pressure

1) External Pressure

SQL Server: whether the value of Buffer Manager-Total Server Memory is smaller. If it gets smaller, it means that the memory that SQL Server can use is compressed by the system or external programs. This is the external pressure.

2) Internal Pressure

The value of SQL Server: Buffer Manager-Total Server Memory does not change, but it is basically the same as that of SQL Server: Buffer Manager-Target Server Memory. This means that the memory requirement of the data page of SQL Server is equal to the memory size that the system can provide. It indicates the internal pressure of the database.

 

3. Solution

1) External Pressure

In most cases, external pressure occurs because other Server software is running in the system, and the memory of SQL Server is snatched when it requires memory. Therefore, the solution is to run SQL Server on a dedicated Server. There is another situation that will lead to external pressure, that is, the operating system is occupying a large amount of memory (such as backup ), the solution is to reduce these operations to SQL Server running pressure (such as at and ).

2) Internal Pressure

A. Find the statement that reads the most data pages and optimize it. You can use the sys. dm_exec_query_status dynamic view to query the association between these statements and the sys. dm_exec_ SQL _text dynamic function.

-- A maximum of 100 statements can be read from a page.
Select top 100
Qs.total_physical_reads,qs.exe cution_count,
Qs. total_physical_reads/qs.exe cution_count as avg_io,
Qt. text, db_name (qt. dbid) as dbname, qt. objectid
FROM sys. dm_exec_query_stats qs
Cross apply sys. dm_exec_ SQL _text (qs. SQL _handle) as qt
Order by qs. total_physical_reads desc
GO

-- A maximum of 100 statements can be read logically from a page.
Select top 100
Qs.total_logical_reads,qs.exe cution_count,
Qs. total_logical_reads/qs.exe cution_count as avg_io,
Qt. text, db_name (qt. dbid) as dbname
FROM sys. dm_exec_query_stats qs
Cross apply sys. dm_exec_ SQL _text (qs. SQL _handle) as qt
Order by qs. total_logical_reads desc
GO

Find these statements and then use the statement optimization method to tune them.

B. If you think there is no space for tuning the statement, or you can increase the physical memory as quickly as you increase the server performance.

 

Ii. pressure tuning of Stolen Memory in the Buffer Pool

1. Memory Clerk Analysis

Because the Stolen Memory in the Buffer Pool is applied by the SQL Server, you can view it in the dynamic management view of Memory Clerk. By analyzing the size of each Clerk, we can basically determine the source of Stolen memory pressure. It is common to use Memory Clerk with a lot of Memory in Stolen.

A) CACHESTORE_SQLCP: the place where dynamic TSQL statement execution plans are cached. This is usually related to the programmer's code. If the programmer is used to using dynamic TSQL statements, the execution plans cached in the memory will be very large. The solution is to use TSQL for stored procedures or parameters.

B) OBJECTSTORE_LOCK_MANAGER: memory used by the Lock Structure in SQL Server. If the blocking in SQL Server is serious, the memory usage of this part of the memory will be very large. The solution is to solve the blocking problem.

 

2. Analyze the waittype field in sys. sysprocesses.

1) CMEMTHREAD (0X00B9)

When multiple users apply for memory or release the memory in the same cache area, only one connection operation can be successful at a certain time, and other connections must wait. This case is rare, mainly in systems with high concurrency, and is usually used to compile dynamic TSQL statements. The solution is to use stored procedures or parameterized TSQL statements to improve the reuse of execution plans.

2) RESOURCE_SEMAPHORE_QUERY_COMPLIE (0X011A)

When a user sends a statement or calls a stored procedure that is too complex, the memory required for SQL Server to compile it will be very large. To prevent excessive memory from being used for compilation, SQL Server sets the upper limit of the compilation memory. When too many complex statements are being compiled at the same time, the memory required for compilation may reach this upper limit, and some statements will be in the waiting state for memory compilation, that is, waittype.

Solution: use stored procedures or parameterized TSQL statements as much as possible to simplify the complexity of each compiled statement and divide it into several stored procedures, if this is not the case, run the dbcc freeproccache statement on a regular basis to manually clear the execution plan in the cache to ensure the memory volume in the stolen.

 

3. Multi-Page Memory pressure optimization

Because the 32-bit SQL Server allocates the Multi-Page size at startup and is relatively small, the default value is 384 MB, therefore, 32-bit SQL Server is prone to Multi-Page Memory stress. The stress of this Part may be caused by the following three situations.

1. The Network Packet Size when the program connects to the database. If it is set to 8 kb or higher, and the connection is very large. For 32-bit SQL Server, the memory usage of this part will soon reach the upper limit. The solution is to change the Network Packet Size set in the program to the default 4 kb, or upgrade to 64-bit SQL Server, so that the Multi-Page Size is unlimited.

2. programmers use a lot of complex TSQL statements or stored procedures. The execution plan exceeds 8 KB, which occupies the space of Multi-Page. Because the size of this part in 32-bit SQL Server is relatively small, it will soon be filled up, and it will not trigger Lazy Writer because the Buffer Pool is not under pressure, the execution plan in Mullti-Page will not be cleared. If you need to apply for Multi-Page Memory, you must wait. This is reflected in the waittype field of sys. sysprocessed. The value is equal to SOS_RESERVEDMEMBLOCKLIST. Solution: Adjust the statement to keep its execution plan within 8 KB. If not, run the dbcc freeproccache statement on a regular basis to manually clear the execution plan, or upgrade to 64-bit SQL Server.

 

This article is messy. Let's take a look...