SQL Server resource Management memory management chapter (top)

The most important resource for SQL Server is memory, disk, and CPU, where memory is the top priority, because SQL Server puts all the data it needs to access (as long as there is enough memory) into the cache for performance requirements. This article describes the memory management architecture of SQL Server.

SQL Server, as an application running on Windows, must accept Windows Resource management and use the Windows API to request and dispatch various resources. However, because Windows's resource management system is designed to meet the needs of most applications, this is not a good fit for SQL Server, a database application that locates at the enterprise level and supports multiuser and high concurrency, and SQL Server has developed its own set of resource management systems --sqlos (SQL operating system). In other words, SQL Server resource management is divided into two tiers, the first layer is on Windows, the Windows API to request resources. The second tier is on SQL Server, using Sqlos to decide how to use the resources requested from Windows.

One, operating system-level SQL Server memory management

Because SQL Server's memory is requested through the Windows API, if Windows itself lacks memory, SQL Server is naturally affected by the lack of memory requested. Therefore, to do SQL Server memory detection, the first step is to look at the system level of memory to ensure that the system itself is not lack of memory, this step is simple but necessary. This first introduces some of the memory management concepts of Windows and then describes how to check the memory of your system.

1. Some memory terms for Windows

Virtual address space: the maximum address space that an application can request to access. For a 32-bit server, the address space is 2 32, which is 4GB, but this 4GB is not all used for SQL Server, the default is the user-state 2GB, kernel mentality 2GB, so that for the 32-bit system SQL Server only 2GB memory available for use. However, the configuration of the system can be adjusted by setting the/3GB Boot.int parameter, so that the user state is 3GB and the core state is 1GB. or open AWE (address space extension) to extend the addressing space to 64GB, but this setting is flawed and is analyzed below.

Physical memory (physical memory): that is, what is usually said about the size of the computer.

Reserved memory (reserved address): One of the ways in which an application accesses memories, reserving a memory address space for future use (the memory used by data pages in SQL Server is applied in this way). The reserved address space, cannot be accessed by other programs, or there will be an error message that the access is out of bounds.

Committed memory (Commit RAM): the reserved (reserve) memory page is formally committed (commit) to use.

Shared Memory: Memory that is visible to more than one process.

Private Bytes: The non-shared part of the address space submitted by a process.

Working Set: The portion of the process's address space that resides in physical memory.

Page Fault (pages access error): Page Fault occurs in the virtual address space but does not exist in the working set. This is in two different cases, the first is the target page on the hard disk, this clock access will bring hard disk read and write, this is known as HDD Fault. Another is the target page in physical memory, but is not under the working set of the process, Windows only needs to redirect a bit, become soft Fault. Because soft hard does not bring the drive to read and write, the performance of the system has little impact, so the administrator is concerned about Fault.

System working the working Set for Set:windows Systems.

2. Memory detection in Windows

You can use the Performance Monitor of Windows to detect memory usage in Windows, and you can see this article, "Using Performance Monitor to monitor system performance/health," using Performance Monitor. In detecting memory, the more important counters are the following:

Counters that analyze the overall memory usage of Windows systems:

Memory:available MBytes: The number of free physical memory in the system.

Memory:pages/sec: The number of pages per second that have been read from or written to the hard page due to the occurrence of an HDD. This counter equals the sum of memory:pages input/sec and Memory:pages output/sec.
Counters that analyze the memory usage of the Windows system itself:

Memory:cache Bytes: The working Set of the system, which is the number of physical memory used by Windows systems.

Counters for memory usage per process:

Process:private Bytes: Non-shared portion of the address space submitted by the process.

Process:working Set: The portion of the process's address space that is stored in physical memory.

From these counters, we can see if there is free memory in the system, which process uses the most memory, and whether there is a mutation in memory usage when the problem occurs. This provides a precondition for the next analysis of SQL Server usage.

II. memory management within SQL Server

1, Memory use classification

Classification by use

1) Database Cache (data page). Pages in SQL Server are stored in 8KB for one page. When SQL Server needs to use a page, it reads the page into memory, which is cached in memory when it is exhausted. SQL Server does not remove pages from memory without the memory pressure. If SQL Server senses the pressure of memory, the unused pages are removed from memory for the longest time to empty the memory.

2) various types of consumer (functional components)

Connection information for connection

General: A hodgepodge of groups. Compilation, normalization of statements, metadata for each lock data structure, transaction context, table and index, etc.

Query plan: The execution plan for statements and stored procedures. Similar to the database cache, SQL Server caches the execution plan for future use, reducing compilation time.

Optimizer: The memory consumed during the generation of the execution plan.

Utilities: Memory consumed by Special operations like BCP, Log Manager, Backup, and more.

3) Thread Memory: memory that is consumed by the data structure and related information for each thread in the process, each thread needs 0.5MB of memory.

4) memory consumed by third-party code: In the process of SQL Server, some code that is not SQL Server itself is run. For example: User-defined CLR or extended Stored procedure Code.

Classification By Application Method

1) reserve a large amount of memory in advance, and then a piece of commit when used. The Database page is applied in this way.

2) The memory that is applied directly by the commit method becomes the stolen mode. Other than the database page, the memory is basically applied in this way.

Sort by the size of the requested memory

1) apply less than or equal to 8KB for one unit of memory, which is called buffer Pool

2) Request more than 8KB of memory for one unit, these memory is called multi-page (or MemToLeave)

SQL Server applies the database page in a reserved-first-commit manner, and the data page is applied in 8KB.

For memory requests in consumer, generally the stolen method is applied, and most of the execution plan size is less than 8KB, a few particularly complex stored procedure execution plan will exceed 8KB, the default connection packet is 4KB, Unless the client is specifically set to more than 8KB (not recommended)

Memory requests for third-party code are generally applied in stolen manner, such as in the CLR, which may be applied in reserved/commit manner.

Each thread's memory is applied in 0.5MB, which is naturally placed in the MemToLeave.

The reason that SQL Server's memory classification is so large is that SQL Server, especially the 32-bit SQL Server, does not have the same size for different kinds of memory, and there are limits on the types of memory such as commit, stolen, and MemToLeave. As a result, there is still free memory in the system, but SQL Server does not apply for it.

2, the size limit of each part of memory

1) 32-bit Windows

When SQL Server starts, the size of the MemToLeave area is pre-allocated. The default size is 256mb+256 (maximum number of threads configured by SQL Server) * 0.5MB=384MB, so the maximum value in buffer pool is 2gb-384mb=1.664g. If AWE technology is used, the extended address space of the system can be reached at 64GB, but the MemToLeave memory is 384mb,buffer because the awe-extended address can only be requested in Reserved/commit mode. The maximum memory for stolen in the pool is 1.664G, and the remaining memory can be used for database page pages.

2) 64-bit Windows

32-bit SQL Server. Due to the 64-bit operating system, the kernel mentality no longer occupies the virtual address space of the 32-bit process, so the size of the MemToLeave is 384mb,buffer pool can reach 3.664G. If AWE is also turned on, this 3.664GB can be used for all stolen in the buffer pool, and the remaining memory can be used for the database page pages. This is rare, however, where 64-bit operating systems are used to install 32-bit-_-.

64-bit SQL Server. All the memory is applied indefinitely and there is a need to apply.

3. Analysis of SQL Server memory usage

In general there are two ways, the first is to analyze the system memory situation using performance counters to analyze, the second is to use the dynamic management view (DMV, only for SQL Server2005 and 2008)

1) SQL Server performance counters

Sqlserver:memory manager:total server memory (KB): SQL Server buffer committed. It is not the total memory used by SQL Server, but the size in the buffer pool.

Sqlserver:memory manager:target server memory (KB): The amount of RAM that the server can use for SQL Server. Typically, the amount of memory that SQL Server can access and the smaller value in the max Server memory value in SQL Server's sp_configure configuration is calculated.

Sqlserver:memory manger:memory Grants Pending: The total number of processes waiting for memory authorization. If the value is not 0, it indicates that there are currently user memory requests due to memory pressure being delayed, which means a serious memory bottleneck.

Sqlserver:buffer manager:buffer Cache hit Ratio: The percentage of data found in the buffer that does not need to be taken from the hard disk. After the SQL Server has been running for a period of time, the rate change should be small, and should be above 98%, if under 95%, indicates a memory shortage problem.

Sqlserver:buffer Manager:lazy Writes/sec: The number of buffers written per second by the Lazy editor (lazy writer). When SQL Server senses memory pressure, it cleans up the longest unused data page and execution plan from the buffer pool, and this action is lazy Writer.
Page life expectancy: The number of seconds to stay in the buffer pool after the page is not referenced. With no pressure on the memory, the page stays in the buffer pool, and pages life expectancy is maintained at a higher value, and page life expectancy drops if there is memory pressure. So if page life expectancy is not maintained on a value, it represents a memory bottleneck for SQL Server.
Sqlserver:buffer manager:database Pages: Is the size of the Database cache.
Sqlserver:buffer Manager:free Pages:sql The size of the idle available in the server.

Sqlserver:buffer Manager:stolen Pages:buffer The size of the Stolen in the pool.

Sqlserver:buffer manager:total The total size of the Pages:buffer pool (equals database Pages+free Pages+stolen Pages). This value is multiplied by 8KB and should be equal to the value of memory Manager:total Server memory.

From the above counters we will be able to understand the memory usage of SQL Server, combined with the above-mentioned system-level counters can probably see if there is a memory bottleneck.

2) Memory dynamic management view

After SQL Server 2005, the memory management of SQL Server is managed in a way that is clerk. All SQL Server memory requests or releases are required through their Clerk,sql server and through the coordination of these clerk to meet different needs. By querying these DMV, you can get more detailed memory usage than with performance counters.

We can detect the memory usage of SQL Server Clerk by using the following query statement.

Use Sys.dm_os_memory_clerks to view memory usage

2. SELECT type, type of--clerk
4. sum (virtual_memory_reserved_kb) as vm_reserved_kb, --reserved Memory
6. sum (virtual_memory_committed_kb) as vm_committed_kb, --Committed memory
8. sum (awe_allocated_kb) as awe_allocated_kb, -memory used when AWE is turned on
10. sum (shared_memory_reserved_kb) as sm_reserved_kb, --shared reserved memory
12. sum (shared_memory_committed_kb) as sm_committed_kb, --Shared commit memory
14. sum (single_pages_kb) as sinlgepage_kb, --memory of stolen in Buffer pool
16. sum (multi_pages_kb) as multipage_kb --MemToLeave memory
18. From Sys.dm_os_memory_clerks
20. GROUP by type
22. ORDER by type

From the above query statement, we can figure out the memory size mentioned earlier

1. reserved/Commit = sum (virtual_memory_reserved_kb)/ sum (virtual_memory_committed_kb)
2. Stolen = sum (single_pages_kb) + sum (multi_pages_kb)
3. Buffer Pool = sum (virtual_memory_committed_kb) + sum (single_pages_kb)
4. MemToLeave = sum (multi_pages_kb)

Through the above we can know the overall SQL Server and the use of parts of memory, if I want to know what data in the cache of the data page in the end of the cache, which data belongs to which table of the database? What statements are executed in the execution plan? This can also be viewed through the DMV.

View the in-memory data page cache which database is which table of data

2. DECLARE @name nvarchar (+)
4. declare @cmd nvarchar (1000)
6. DECLARE dbnames cursor for
8. Select name from master.dbo.sysdatabases
10. Open Dbnames
12. Fetch Next from dbnames to @name
14. While @ @fetch_status = 0
16. Begin
18. Set @cmd = ' Select b.database_id, Db=db_name (b.database_id), P.object_id,p.index_id,buffer_count=count (*) from '
20. --Here the OBJECT_ID represents the object number in SQL Server, index_id is the index number, Buffer_count represents the number of pages
22. + @name + '. Sys.allocation_units A, '
24. + @name + '. Sys.dm_os_buffer_descriptors b, ' + @name + '. Sys.partitions p
26. where a.allocation_unit_id = b.allocation_unit_id
28. and a.container_id = p.hobt_id
30. and b.database_id = db_id ("'+ @name + ')
32. Group by b.database_id,p.object_id, p.index_id
34. Order by b.database_id, Buffer_count desc '
36. EXEC (@cmd)
38. Fetch Next from dbnames to @name
40. End
42. Close Dbnames
44. Deallocate dbnames
46. Go

--based on the @object_id above to find out which database is which table

1. SELECT S.name as Table_schema, O.name as table_name --table_schema.table_name table is used
2. From sys.sysobjects as o INNER JOIN
3. Sys.schemas as s on o.uid = s.schema_id
4. WHERE (o.id = @object_id)

--Find out the name of the index based on the @object_id and @index_id taken out above

1. SELECT ID, indid, name as index_name --Index_name is the name of the index
2. From sys.sysindexes
3. WHERE (id = @object_id) and (indid = @index_id)

--based on the table name Table_schema.table_name and the name of the index index_name, you can also find out which fields the index is based on.

1. EXEC sp_helpindex ' table_schema.table_name '

View the in-memory cache execution plan, and the statement that corresponds to the execution plan:

--The output may be large, please use it carefully

1. SELECT usecounts, Refcounts, Size_in_bytes, Cacheobjtype, ObjType, text
2. From Sys.dm_exec_cached_plans CP Cross APPLY sys.dm_exec_sql_text (plan_handle)
3. ORDER by objtype DESC

So much has been written about the fact that most of the talk is still part of the data collection, and the corresponding solution has not yet been mentioned ... Because the article is too long, the specific solution will be explained in the next article, the next one will be from the database Page, stolen and multi-page three parts of the specific bottlenecks to explain.

Original link: http://www.cnblogs.com/caspnet/archive/2011/02/21/1959539.html

SQL Server resource Management memory management chapter (top)