In SQL Server has a three-level structure. At the bottom are memory nodes, which are the lowest-level allocators for SQL Server memory. The second level consists of memory clerks, which are used to access the memory nodes, and cache stores, which are used for caching. The top level contains memory objects, which provide a smaller degree of granularity than the memory clerks allow directly. Only clerks can access memory nodes to allocate memory, so every component that needs to allocate substantial amounts of memory needs to create its own memory clerk when the SQL Server service starts.
Memory Nodes Memory nodes map directly onto NUMA node, and you can view details about these nodes on your server using the sys.dm_os_memory_nodes DMV. You will always have at least one memory node, which has a memory_node_id of 0, and you may have several if your CPU architecture supports NUMA. Each memory node has its own memory clerks and caches, which are distributed evenly across all the nodes (although some objects will only be found in node 0). SQL Server’s total usage is calculated using the sum of all the nodes.
Memory Clerks Whenever a memory consumer within SQL Server wants to allocate memory, it needs to go through a memory clerk, rather than going straight to a memory node. There are generic memory clerks like MEMORYCLERK_SQLGENERAL, but any component that needs to allocate significant amounts will have been written to create and use its own memory clerk. The buffer pool for instance has its own memory clerk (MEMORYCLERK_SQLBUFFERPOOL), as do query plans (MEMORYCLERK_SQLQUERYPLAN), which makes troubleshooting much easier because you can view the memory allocations made by each clerk and see who has what. You can view details about all the memory clerks using the sys.dm_os_memory_clerks DMV.
SELECT [type], memory_node_id, pages_kb, virtual_memory_reserved_kb, virtual_memory_committed_kb, awe_allocated_kb FROM sys.dm_os_memory_clerks ORDER BY virtual_memory_reserved_kb DESC
Buffer Pool The buffer pool contains and manages SQL Server’s data cache. Information on its contents can be found in the sys.dm_os_buffer_descriptors DMV. For example, the following query returns the amount of data cache usage per database, in MB:
SELECT count(*)*8/1024 AS 'Cached Size (MB)' ,CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE db_name(database_id) END AS 'Database'
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id),database_id
ORDER BY 'Cached Size (MB)' DESC
Monitoring SQL Server’s buffer pool is a great way to look out for memory pressure, and Performance Monitor provides numerous counters to help you do this for quick insight, including the following:
➤ MSSQL$:Memory Manager\Total Server Memory (KB) — Indicates the current size of the buffer pool ➤ MSSQL$:Memory Manager\Target Server Memory (KB) — Indicates the ideal size for the buffer pool. Total and Target should be almost the same on a server with no memory pressure that has been running for a while. If Total is significantly less than Target, then either the workload hasn’t been sufficient for SQL Server to grow any further or SQL Server cannot grow the buffer pool due to memory pressure, in which case you can investigate further.
➤ MSSQL$:Buffer Manager\Page Life Expectancy — Indicates the amount of time, in seconds, that SQL Server expects a page that has been loaded into the buffer pool to remain in cache. Under memory pressure, data pages are flushed from cache far more frequently. Microsoft recommends a minimum of 300 seconds for a good PLE; this threshold continues to be debated within the SQL Server community, but one thing everyone agrees on is that less than 300 seconds is bad. In systems with plenty of physical memory, this will easily reach thousands of seconds.
The Memory: Page Faults/sec counter in Performance Monitor includes both hard and soft page faults; therefore, if you want to monitor just the performance that is sapping hard page faults, you need to look at Memory: Page Reads/sec to get the number of times the disk was accessed to resolve hard page faults, and then compare it to Memory: Pages Input/sec to calculate the average number of pages being read in each disk access.
Plan Cache Execution plans can be time consuming and resource intensive to create; therefore, it makes sense that if SQL Server has already found a good way to execute a piece of code, it should try to reuse it for subsequent requests. The plan cache (also referred to as the procedure cache) is used to cache all the execution plans in case they can be reused. You can view the contents of the plan cache and determine its current size by using the sys.dm_exec_cached_plans DMV or by running DBCC MEMORYSTATUS and looking for the “Procedure Cache” section, where you’ll find the number of plans in cache and the cache size, in 8KB pages.
NOTE DBCC MEMORYSTATUS provides a lot of useful information about SQL Server’s memory state but you’ll find that DMVs provide far more flexibility with the output, so try to get used to finding the same information from DMVs whenever possible. The following DMVs are a good place to start:
➤ sys.dm_os_memory_nodes
➤ sys.dm_os_memory_clerks
➤ sys.dm_os_memory_objects
➤ sys.dm_os_memory_cache_counters
➤ sys.dm_os_memory_pools
The maximum size for the plan cache is calculated by SQL Server as follows:
➤ 75% of server memory from 0–4GB +
➤ 10% of server memory from 4GB–64GB +
➤ 5% of server memory > 64GB Therefore, a system with 32GB of RAM would have a maximum plan cache of 3GB + 2.8GB = 5.8GB.
No comments:
Post a Comment