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) Third PartyCodeMemory consumption: 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 a memory unit larger than 8 KB. These memories are called multi-page (or memtoleave)

SQL Server applies for database cache in the form of first reserved and then commit, and the data pages are all 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 (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. SQL Server Memory Usage Analysis
Generally, there are two methods: the first is to use performance counters for analysis of system memory conditions, and the second is to use dynamic management views (DMV, only applicable to SQL server2005 and 2008)
1) SQL server performance counters
Sqlserver: Memory Manager: Total server memory (Kb): 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 Memory (Kb): The amount of memory that the server can use 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 buffers written by lazy writer every 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.
Sqlserver: Buffer Manager: 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 (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