SQL Server 性能优化 -- 内存说明 -- cnDBA.cn

1 SQL Server 内存说明

SQL Server 的内存管理器的结构分为三层：

最底层是内存节点（memory node），这是 SOL Server 最底层的内存分配器;
第二层由内存分配员组成，内存分配员负责内存节点的访问以及用于缓存的数据的缓存存储；
最顶层包括内存对象，内存对象的粒度比内存分配员能够直接操作的内存的粒度低一个级别。

只有内存分配员可以访问内存节点并进行内存分配，因此，在 SQL Server 服务启动时，每个需要分配大量内存的组件都要创建属于自己的内存分配员。

内存分配员是使用内存缓存的机制，缓冲池是 SQL Server 中最大的内存使用者。

1.1 内存分配员

每当 SOL Server 中的某个内存消耗组件需要分配内存的时候，这个组件不会直接从内存节点中分配内存，而是通过内存分配员分配内存。尽管有一些通用的内存分配员，例如 MEMORYCLERK SOLGENERAL，但那些需要分配大量内存的组件都会创建并拥有属于自己的内存分配员。

缓冲池的内存分配员是：MEMORYCLERK SQLBUFFERPOOL，查询计划的内存分配员是：MEMORYCLERK SQLQUERYPLAN。由于这些组件拥有自己的内存分配员，大大简化了故障排除，因为可以方便地观察到每个内存分配员分配的内存，并且可以清楚看到每个组件分配的内存情况。

可以通过动态管理视图 sys.dm_os_memory_clerks 查看所有内存分配员的详细信息。

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;

1.2 缓存

SQL Server 采用了三种缓存机制∶ 对象存储（object store）、缓存存储（cache store）和用户存储（user store）。

对象存储用来缓存无状态的同构类型数据，但最常见的缓存机制是缓存存储和用户存储。缓存存储和用户存储非常类似，因为它们都是缓存，这两者之间的主要区别在于用户存储是通过它们自己的存储语义创建的，并且是通过开发框架创建的;而缓存存储实现了对前文所述内存对象的支持，提供了更小粒度的内存分配。

可以通过sys.dm_os_memory_cache_counters视图来查看不同类型的缓存：

SELECT [name],
       [type],
       pages_kb,
       entries_count
FROM sys.dm_os_memory_cache_counters
ORDER BY pages_kb DESC;

1.3 缓冲池（Buffer Pool）

缓冲池包含 SQL Server 的数据缓存，并对数据缓存进行管理。Data Cache是存储数据页（Data Page）的缓冲区，当SQL Server需要读取数据文件（File）中的数据页（Data Page）时，SQL Server会把整个Page都调入内存（内存中的一个Page叫做buffer），Page是数据访问的最小单元。

当用户修改了某个Page上的数据时，SQL Server 会先在内存中修改Buffer，但是不会立即将这个数据叶写回硬盘，而是等到CheckPoint或lazy Writer进程运行时集中处理。当用户读取某个Page后，如果SQL Server没有内存压力，它不会在内存中删除这个Page，因为内存中的数据页始终存放着数据的最新状态，如果有其他用户使用这个Page，SQL Server 不需要从硬盘中读取一次，节省语句执行的时间。理想情况是SQL Server将用户需要访问的所有数据都缓存在内存中，SQL Server 永远不需要去硬盘读取数据，只需要在CheckPoint 或 lazy Write运行时把修改过的页面写回硬盘即可。

可以通过 sys.dm_os_buffer_descriptors视图来查看缓冲池内容的详细信息。

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;

1.4 计划缓存

计划缓存（也称为过程缓存）存储查询语句和存储过程的执行计划，以供重用，而不需要重新编译（Compile），因为编译查询语句产生执行计划是一个非常耗费资源的过程。
生成查询执行计划的过程非常消耗时间和资源，因此，如果 SQL Server 已经为一段代码生成了很好的执行计划，那很直观的想法就是将这个执行计划缓存起来以供后续重用。

可通过动态管理视图 sys.dm_exec_cached_plans 查看计划缓存的内容及其占用的空间。

SELECT COUNT(*) AS 'Number of Plans',
       SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS 'Plan Cache Size (MB)'
FROM sys.dm_exec_cached_plans;

也可以通过执行 DBCC MEMORYSTATUS 命令查看输出的 Procedure Cache 部分的内容查看以上信息，这一部分包含了计划缓存中的执行计划的数目以及缓存占用的 8KB页面的数目。

还有一些与内存相关的视图如下：

sys.dm_os_memory_nodes
sys.dm_os_memory_clerks
sys.dm_os_memory_objects
sys.dm_os_memory_cache_counters
sys.dm_os_mermory_pools

2 查看内存使用情况

2.1 查看Memory clerk分配的内存量

SELECT memory_node_id,
       type,
       pages_kb,
       virtual_memory_reserved_kb,
       virtual_memory_committed_kb,
       shared_memory_reserved_kb,
       shared_memory_committed_kb,
       page_size_in_bytes
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLQERESERVATIONS';

2.2 统计Memory Clerk分配的内存总量

SELECT mc.type,
       mc.name,
       SUM(mc.pages_kb) AS AllocatedPages_KB,
       SUM(mc.virtual_memory_reserved_kb) AS VM_Reserved_KB,
       SUM(mc.virtual_memory_committed_kb) AS VM_Committed_KB,
          --sum(mc.shared_memory_reserved_kb) as ShareMem_Reserved_KB,
          --sum(mc.shared_memory_committed_kb) as ShareMem_Committed_KB, 
       max(mc.page_size_in_bytes) / 1024 AS SinglePageSize_KB
FROM sys.dm_os_memory_clerks mc
GROUP BY mc.type,
         mc.name
ORDER BY AllocatedPages_KB DESC,
         mc.type,
         mc.name;

2.3 查看缓存中的数据页

SELECT DB_NAME(bd.database_id) AS dbname,
       OBJECT_NAME(p.object_id) AS ObjectName,
       i.name AS IndexName,
       COUNT(0) AS BufferCounts,
       SUM(bd.free_space_in_bytes) / 1024 AS TotalFreeSpace_KB,
       CAST(SUM(bd.free_space_in_bytes) / (8 * 1024.0) / COUNT(0) AS DECIMAL(10, 4)) * 100 AS FreeSpaceRatio,
       SUM(CAST(bd.is_modified AS INT)) AS TotalDirtyPages,
       SUM(bd.row_count) AS TotalRowCounts
FROM sys.allocation_units au
     INNER JOIN sys.dm_os_buffer_descriptors bd ON au.allocation_unit_id = bd.allocation_unit_id
     INNER JOIN sys.partitions p ON au.container_id = p.hobt_id
     INNER JOIN sys.indexes i ON p.object_id = i.object_id
                                 AND p.index_id = p.index_id
     INNER JOIN sys.objects o ON p.object_id = o.object_id
WHERE bd.database_id = DB_ID(N'database_name')
      AND o.type <> N'S'
GROUP BY bd.database_id,
         p.object_id,
         i.name
ORDER BY BufferCounts DESC,
         dbname,
         ObjectName;

2.4 查看计划缓存

SELECT cp.objtype,
       cp.cacheobjtype,
       SUM(cp.size_in_bytes) AS TotalSize_B,
       COUNT(cp.bucketid) AS CacheCounts,
       SUM(cp.refcounts) AS TotalRefCounts,
       SUM(cp.usecounts) AS TotalUseCounts
FROM sys.dm_exec_cached_plans cp
GROUP BY cp.objtype,
         cp.cacheobjtype
ORDER BY TotalSize_B DESC;

3 清空缓存

清理数据缓存（Data Cache）中的脏页（dirty pages）和干净页（clean pages）：

CHECKPOINT;
DBCC DROPCLEANBUFFERS;

清空所有的计划缓存（Plan Cache）：

DBCC FREEPROCCACHE;

签到成功

CNDBA社区