SQL Server resource management-memory management (part I)

For SQL Server, the most important resources are memory, Disk, and CPU. The memory is the most important, because SQL Server only needs enough memory to access all the data to meet performance requirements) put it in the cache. This article introduces the memory management system of SQL Server.

As an application running on Windows, SQL Server must accept Windows resource management and use Windows APIs to apply for and schedule various types of resources. However, because Windows's resource management system is designed to meet the needs of most applications, this is not suitable for SQL Server, a database application that is located at the enterprise level and supports multiple users and high concurrency, for this reason, SQL Server has developed its own resource management system-SQLOS (SQL operating system ). That is to say, the Resource Management of SQL Server is divided into two layers. The first layer is on Windows, and resources are requested through Windows APIs. The second layer is SQL Server, which uses SQLOS to determine how to use the resources applied from Windows.

I. Operating System-level SQL Server Memory Management

Because SQL server Memory is applied through the Windows API, if Windows itself lacks memory, SQL Server performance is naturally affected because the memory cannot be applied. Therefore, to check the memory of SQL Server, the first step is to check the memory at the system level to ensure that the system itself does not lack memory. This step is simple but necessary. Here we will first introduce some memory management concepts of Windows, and then introduce how to check the system memory.

1. Some memory terms in Windows

Virtual Address Space): The maximum Address Space that an application can request access. For 32-bit servers, the address addressing space is 2 to the power of 32, that is, 4 GB, but this 4 GB is not for SQL Server, by default, the user State is 2 GB and the core State is 2 GB. Therefore, for 32-bit system SQL Server, only 2 GB of memory is available. However, you can set the/3 GB boot.int parameter to adjust the system configuration so that the user State is 3 GB and the core State is 1 GB. Or enable the AWE address space extension) to extend the addressing space to 64 GB. However, this setting is flawed and will be analyzed below.

Physical Memory): the Memory size of a computer.

Reserved Memory Reserved address): One of the ways in which an application can access the Memory. Reserve is Reserved first, this method is used to keep the memory used on the data page of SQL Server in the future ). The reserved address space cannot be accessed by other programs. Otherwise, an error message is displayed.

Committed Memory submitted Memory): The Memory Page of Reserve will be retained) is officially submitted to Commit.

Shared Memory): Memory visible to more than one process.

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

Working Set: The address space of a process that is stored in the physical memory.

Page Fault Page access error): Page Fault occurs when the virtual address space is accessed but does not exist in the Working Set. There are two situations: the first is that the target page is on the Hard disk. this access will lead to Hard disk read/write, which is called Hard Fault. The other is that the target page is in the physical memory, but it is not under the Working Set of the process. In Windows, you only need to redirect it to Soft Fault. Since Soft Hard does not bring about Hard disk read/write, it has little impact on the system performance, so the Administrator is concerned with Hard Fault.

System Working Set: Windows Working Set.

2. Windows Memory Detection

You can use the Performance Monitor of Windows to check the memory usage of Windows. For details about how to use the Performance Monitor, refer to this Article "Using Performance Monitor to monitor system performance/running status". In the memory detection, the important counters are as follows:

Counters used to analyze the overall memory usage in Windows:

Memory: Available MBytes: Number of idle physical Memory in the system.

Memory: Pages/Sec: Number of Pages read or written from or written to the Hard disk per second due to the occurrence of Hard Page. This counter is equal to the sum of Memory: Pages Input/Sec and Memory: Pages Output/Sec.
Counters used to analyze the memory usage of Windows:

Memory: Cache Bytes: the system's Working Set, that is, the number of physical Memory used by Windows.

Counters for memory usage of each process:

Process: Private Bytes: the non-shared part of the address space submitted by the Process.

Process: Working Set: the part of the address space of the Process stored in the physical memory.

From these counters, we can see whether there is idle memory in the system, which process uses the most memory, and whether there is a sudden change in memory usage when a problem occurs. This provides a prerequisite for analyzing SQL Server usage.

2. Memory Management in SQL Server

1. memory usage Classification

Categories by Purpose

1) Database cache data page ). All pages in SQL Server are stored in 8 KB as one page. When SQL Server needs to use a page, it will read the page to the memory and cache it in the memory after use. When there is no memory pressure, SQL Server will not delete the page from the memory. If the SQL Server feels the memory pressure, it will delete the pages that have not been used for the longest time from the memory to empty the memory.

2) Various Consumer functional components)

Connection information

General: a collection of hodgedge. Statement compilation, normalization, each lock data structure, transaction context, table and index metadata, etc.

Query Plan: Execution Plan of statements and stored procedures. Similar to Database cache, SQL Server caches execution plans for future use, reducing Compilation Time.

Optimizer: memory consumed during execution plan generation.

Utilities: memory consumed by special operations such as BCP, Log Manager, and Backup.

3) thread memory: stores the memory consumed by the data structure and related information of each thread in the process. Each thread requires MB of memory.

4) memory consumed by third-party code: some non-SQL Server code is run in the SQL Server process. For example, the User-Defined CLR or Extended Stored Procedure code.

Categories by Application Method

1) Reserve a large piece of memory in advance, and then use a Commit. Database Page is applied in this way.

2) directly apply for memory in Commit mode and use it in Stolen mode. In addition to the Database Page, other memory is basically applied in this way.

Categories by memory size

1) apply for memory units smaller than or equal to 8 KB. These memories are called Buffer pools.

2) apply for memory larger than 8 KB, which is called Multi-Page or MemToLeave)

SQL Server applies for the Database Page in the form of Reserved first and then Commit, and the data Page is applied in 8 KB.

Memory Applications in Consumer are generally applied in Stolen mode, and most execution plans are smaller than 8 KB. The execution plans of a few particularly complex stored procedures will exceed 8 KB, the default connection data packet is 4 kb, unless the client is specially set to exceed 8 KB is not recommended)

Third-party CODE memory applications are generally applied in Stolen mode. For example, the CLR may apply in Reserved/Commit mode.

Each thread's memory is applied in MB format, and is naturally stored in MemToLeave.

The reason why I spent so much time talking about the memory classification of SQL Server is that SQL Server, especially the 32-bit SQL Server, has different application sizes for different types of memory, memory of the Commit, Stolen, and MemTOLeave types is limited. Therefore, there will be idle memory in the system, but SQL Server will not apply for it.

2. Memory size limit for each part

1) 32-bit Windows

When SQL Server is started, the size of the MemToLeave region is pre-allocated. The default size is 256 MB + the maximum number of threads allowed by the 256SQL Server configuration) * 0.5 MB = 384 MB, so the maximum value in the Buffer Pool is 2 GB-384 MB = 1.664 GB. If AWE technology is used, the extended address space of the system can reach 64 GB. However, since the address extended by AWE can only be applied in Reserved/Commit mode, the memory of MemToLeave is still 384 MB, the maximum memory of Stolen in the Buffer Pool is 1.664 GB, and the remaining memory can be used for the Database Page.

2) 64-bit Windows

32-bit SQL Server. Because the 64-bit operating system does not occupy the virtual address space of 32-bit processes, the size of MemToLeave is still 384 MB, And the Buffer Pool can reach 3.664 GB. If AWE is enabled, all 3.664GB can be used for Stolen in the Buffer Pool, and the remaining memory can be used on the Database Page. However, this situation is rare, where to use a 64-bit operating system to install 32-bit oh -_-.

64-bit SQL Server. All memory applications are unlimited and can be applied as needed.

3. SQL Server Memory Usage Analysis

There are two methods in general. The first method is to use performance counters for analysis of system memory conditions, and the second is to use dynamic management view DMV, which is only applicable to SQL Server2005 and 2008)

1) SQL Server performance counters

SQLServer: Memory Manager: Total Server MemoryKB): Memory submitted by the SQL Server Buffer. It is not the total memory used by SQL Server, but the size in the Buffer Pool.

SQLServer: Memory Manager: Target Server MemoryKB): Memory size available for SQL Server. Generally, it is calculated based on the Memory size that can be accessed by SQL Server and the smaller value in the Max Server Memory value in the sp_Configure configuration of SQL Server.

SQLServer: Memory Manger: Memory Grants Pending: Total number of processes waiting for Memory authorization. If the value is not 0, the current memory application is delayed due to memory pressure, which means a serious memory bottleneck.

SQLServer: Buffer Manager: Buffer Cache Hit Ratio: Percentage of data found in the Buffer rather than from the hard disk. After running SQL Server for a period of time, the ratio changes very little and should all be above 98%. If it is below 95%, it indicates there is a problem of insufficient memory.

SQLServer: Buffer Manager: Lazy Writes/Sec: Number of Buffer Writes by the inert editor Lazy writer per second. When the SQL Server feels the memory pressure, it will clear the unused data pages and execution plans from the buffer pool. Lazy Writer is used to perform this operation.
Page Life Expectancy: the number of seconds in the buffer pool after the Page is not referenced. When there is no memory pressure, the Page will remain in the buffer pool, and the Page Life Expectancy will remain at a relatively high value. If there is memory pressure, the Page Life Expectancy will decrease. Therefore, if Page Life Expectancy cannot be maintained on a single value, it indicates that SQLServer has a memory bottleneck.
SQLServer: Buffer Manager: Database Pages: the size of the Database Cache.
SQLServer: Buffer Manager: Free Pages: the available size of SQL Server.

SQLServer: Buffer Manager: Stolen Pages: size of the Stolen in the Buffer Pool.

SQLServer: Buffer Manager: Total Pages: the Total size of the Buffer Pool is equal to Database Pages + Free Pages + Stolen Pages ). This value is multiplied by 8 KB and should be equal to the value of Memory Manager: Total Server Memory.

From the counters above, we can understand the memory usage of SQL Server. Based on the counters at the system layer, we can see whether there is a memory bottleneck.

2) memory dynamic management view

After SQL Server 2005, the Memory management of SQL Server is managed in a unified manner using Memory Clerk. All SQL Server Memory Applications or releases must be implemented through their Clerk, and SQL Server can meet different requirements through the coordination of these Clerk. By querying these dmusic videos, you can obtain more detailed memory usage than using performance counters.

We can use the following query statement to check the memory usage of SQL Server's Clerk.

Use sys. dm_ OS _memory_clerks to view memory usage

 
 

  
  
SELECT type, -- Clerk type
  
  
Sum (virtual_memory_reserved_kb) as vm_Reserved_kb, -- reserved memory
  
  
Sum (virtual_memory_committed_kb) as vm_Committed_kb, -- Memory submitted
  
  
Sum (awe_allocated_kb) as awe_Allocated_kb, -- memory used after AWE is enabled
  
  
Sum (shared_memory_reserved_kb) as sm_Reserved_kb, -- shared reserved memory
  
  
Sum (shared_memory_committed_kb) as sm_Committed_kb, -- shared commit memory
  
  
Sum (single_pages_kb) as SinlgePage_kb, -- Memory of Stolen in Buffer Pool
  
  
Sum (multi_pages_kb) as MultiPage_kb -- Memory of MemToLeave
  
  
FROM sys. dm_ OS _memory_clerks
  
  
Group by type
  
  
Order by type
 
 

From the preceding query statement, we can calculate the memory size mentioned above.

 
 

  
  
Reserved/Commit = sum(virtual_memory_reserved_kb) / sum(virtual_memory_committed_kb)  
  
  
Stolen = sum(single_pages_kb) + sum(multi_pages_kb)  
  
  
Buffer Pool = sum(virtual_memory_committed_kb) + sum(single_pages_kb)  
  
  
MemToLeave = sum(multi_pages_kb) 
 
 

Through the above introduction, we can know the overall usage of SQL Server and the memory of each part. If I want to know what data is cached in the cache of the data page, which database does the data belong? What statements are cached in the execution plan? This can also be viewed through DMV.

View the data of the database and table cached on the data page in the memory

 
 

  
  
Declare @ name nvarchar (100)
  
  
Declare @ cmd nvarchar (1000)
  
  
Declare dbnames cursor
  
  
Select name from master. dbo. sysdatabases
  
  
Open dbnames
  
  
Fetch next from dbnames into @ name
  
  
While @ fetch_status = 0
  
  
Begin
  
  
Set @ cmd = 'select B. database_id, db = db_name (B. database_id), p. object_id, p. index_id, buffer_count = count (*) from'
  
  
-- Object_id indicates the object number in SQL Server, index_id indicates the index number, and buffer_count indicates the number of pages.
  
  
+ @ Name + '. sys. allocation_units ,'
  
  
+ @ Name + '. sys. dm_ OS _buffer_descriptors B,' + @ name + '. sys. partitions p
  
  
Where a. allocation_unit_id = B. allocation_unit_id
  
  
And a. container_id = p. hobt_id
  
  
And B. database_id = db_id (''' + @ name + ''')
  
  
Group by B. database_id, p. object_id, p. index_id
  
  
Order by B. database_id, buffer_count desc'
  
  
Exec (@ cmd)
  
  
Fetch next from dbnames into @ name
  
  
End
  
  
Close dbnames
  
  
Deallocate dbnames
  
  
Go
 
 

-- Find the database table based on the obtained @ object_id

 
 

  
  
SELECT s. name AS table_schema, o. name as table_name -- The table_schema.table_name table is used.
  
  
FROM sys. sysobjects AS o INNER JOIN
  
  
Sys. schemas AS s ON o. uid = s. schema_id
  
  
WHERE (o. id = @ object_id)
 
 

-- Locate the index name based on the obtained @ object_id and @ index_id

 
 

  
  
SELECT id, indid, name as index_name -- index_name is the index name.
  
  
FROM sys. sysindexes
  
  
WHERE (id = @ object_id) AND (indid = @ index_id)
 
 

-- Based on the table name table_schema.table_name obtained above and the index name index_name, you can also find out the fields on which the index is created.

 
 

  
  
EXEC sp_helpindex 'table_schema.table_name' 
 
 

View the execution plan cached in the memory and the statement corresponding to the execution plan:

-- The output may be large. Please use it with caution.

 
 

  
  
SELECT    usecounts, refcounts, size_in_bytes, cacheobjtype, objtype, text   
  
  
FROM    sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(plan_handle)   
  
  
ORDER BY objtype DESC 
 
 

After writing so many articles, I found that most of them are about data collection. The corresponding solutions have not been mentioned yet... Because the article is too long, the specific solution will be explained in the next article. The next article will explain the specific bottlenecks of Database Page, Stolen and Multi-Page.

Edit recommendations]