SQL Server resource Management memory management chapter (bottom)

In the previous article, the memory management of SQL Server resource management (above), introduced some theoretical knowledge of SQL Server memory management, this article uses this knowledge to solve some common problems in the real world.

First, the data page cache pressure tuning

As we said in the previous section, the corresponding speed is the fastest if the user accesses data pages that are in memory. However, in reality, the size of the database is larger than the physical memory, SQL Server can not be the user needs all the data is in memory, when the user needs the data is not in memory, will occur paging action from the hard disk to read the required data, the occasional paging will not affect the overall SQL Server performance, but if the paging action often occurs, it can severely affect overall SQL Server performance.

When we tune the data page cache, the first step is to determine if there is a pressure on the data page cache, and the second step is to determine where the pressure on the page cache pages is caused, and can be divided into external pressure and internal pressure.

1, whether there is data page cache pressure

Determine if there is data page cache pressure, mainly from the following memory performance counters and sys.sysprocesses to confirm.

The value of SQL Server:buffer manager-lazy writes/sec often occurs.

The SQL Server:buffer manager-page life expectancy often changes constantly and never rises up.

The value of SQL Server:buffer manager-page reads/sec is often not 0.

The Async_io_completion value can be seen from the wait_type of the sys.sysprocesses system view, which means "waiting for I/O operations to complete," which usually means that the hard disk reads and writes, It may also be said that this is the speed of the drive is too slow, as long as the speed of the hard drive can solve the problem. It is true that a fast hard drive can make SQL Server more responsive, but if the value of the three counters above is constant, then the problem with the hard disk is not the main issue, it is simply insufficient memory (because of) the hard disk read and write (the result), the root cause or in memory.

From the above analysis, you can confirm the existence of data page cache pressure in the system, now to analyze the source of this pressure, external pressure or internal pressure.

2, the source of pressure

1) External pressure

The value of SQL Server:buffer manager-total Server memory has become smaller. If it gets smaller, it means that the memory that SQL Server can use is compressed by the system or by an external program. This is the external pressure.

2) Internal pressure

The value of SQL Server:buffer manager-total server memory does not vary, but is essentially equal to the size of the SQL Server:buffer manager-target server memory. This is the memory requirement of the SQL Server data page already equal to the amount of memory the system can provide. Description is the internal pressure of the database.

3. Solutions

1) External pressure

Most situations where external pressure occurs are due to the fact that other server software is running on the system, and it robs the memory of SQL Server when it needs memory. So the solution is to run SQL Server on a dedicated server. There is also a situation that can lead to external pressure, that is, the operating system in memory-intensive operations (such as backup), the solution is to put these operators to SQL Server when the pressure is low (such as the early hours of 1, 2 points).

2) Internal pressure

A, to find the most Read data page statement, it is tuned. Find out that these statements can be queried by sys.dm_exec_query_status dynamic views and sys.dm_exec_sql_text dynamic functions.

--up to 100 statements physically read pages

2. SELECT TOP
6. Qs.total_physical_reads,qs.execution_count,
10. Qs.total_physical_reads/qs.execution_count as Avg_io,
14. Qt.text, Db_name (qt.dbid) as dbname, Qt.objectid
18. From Sys.dm_exec_query_stats QS
22. Cross apply Sys.dm_exec_sql_text (Qs.sql_handle) as qt
26. ORDER by qs.total_physical_reads desc
30. GO

--up to 100 statements on the logical Read page

2. SELECT TOP
6. Qs.total_logical_reads,qs.execution_count,
10. Qs.total_logical_reads/qs.execution_count as Avg_io,
14. Qt.text, Db_name (qt.dbid) as dbname
18. From Sys.dm_exec_query_stats QS
22. Cross apply Sys.dm_exec_sql_text (Qs.sql_handle) as qt
26. ORDER by qs.total_logical_reads desc
30. GO

Find these statements and then tune them in a way that can be tuned by the statement.

b, if you think the statement has no room for tuning, or as quickly as possible to improve the performance of the server can only increase the physical memory.

Ii. pressure tuning of the stolen memory in Buffer pool

1, through the Memory clerk analysis

Because the stolen memory in the buffer pool is applied by SQL Server itself, it can be viewed in the dynamic management view of the memory clerk. By analyzing the size of each clerk, it is essential to determine the source of stolen memory pressure. A common memory clerk that uses stolen.

A) CACHESTORE_SQLCP: the place to cache the execution plan of the dynamic TSQL statement. This is usually related to the programmer's code, and if the programmer is accustomed to using dynamic TSQL statements, this part of the in-memory cache execution plan will be very large. The workaround is to use the TSQL of the stored procedure or argument.

b) memory used by the lock structure in the Objectstore_lock_manager:sql server. If the blocking in SQL Server is serious, the memory usage of this part of memory will be very large. The solution is to solve the blocking problem.

2, through the sys.sysprocesses inside the waittype field analysis

1) CMEMTHREAD (0X00B9)

When multiple users request memory or free memory from the same cache, only one connection can succeed at a time, and other connections must wait. This is a relatively small situation, mainly in which the concurrency is very high in the system, and is usually compiled 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 transmits a statement or calls a stored procedure that is overly complex, the memory required by SQL Server to compile it is very large. SQL Server sets the upper limit for compiling memory in order to prevent excessive memory from being used for compilation actions. When too many complex statements are compiled at the same time, the memory required for compiling may reach this limit, and some statements will be in the state of waiting for the memory to compile, as well as the waittype.

The solution is: as much as possible using stored procedures or parameterized TSQL statements, simplify each need to compile the statement complexity, divided into several stored procedures, it is not possible to consider periodically running DBCC FREEPROCCACHE statement to manually clear the cache of the execution plan, to ensure the amount of memory in the stolen.

Three, multi-page memory pressure tuning

Because the 32-bit SQL Server allocates the multi-page size and is smaller at startup, the default is 384MB, so multi-page memory pressure is more prone to 32-bit SQL Server. The pressure in this section may be caused by the following three scenarios.

1, the Program connection database when the network Packet size, if set to 8KB or higher, and the connection is very large. For 32-bit SQL Server, this portion of the memory usage will quickly reach the upper limit. The workaround is to change the network Packet size set in the program to the default 4KB, or to the 64-bit SQL Server so that the size of the multi-page is not limited.

2, the programmer uses many complex TSQL statement or the stored procedure, its execution plan exceeds 8KB, this will occupy multi-page space. Because the size of this part in the 32-bit SQL Server is small, it will soon be filled, and because the buffer pool is very large without pressure, it will not trigger the execution plan in the lazy writer,mullti-page will not be cleaned up. At this point, if the user needs to apply for multi-page memory must wait. This is reflected in the Sys.sysprocessed waittype field, which is equal to sos_reservedmemblocklist. Solution: The statement adjusts its execution plan to within 8KB, and if not, consider running the DBCC FREEPROCCACHE statement on a regular basis to clean up the execution plan manually, or to upgrade to 64-bit SQL Server.

This is a very messy piece of writing, so let's see.

Original link: http://www.cnblogs.com/caspnet/archive/2011/02/26/1965795.html

SQL Server resource management in memory Management chapter (bottom)