Buffer Pool--内存总结2
按内存划分:
1.DATABASE CACHE
用于存放数据页面的缓冲区，8KB每页
2.各项组件
A)数据库连接(CONNECTION)
B)通用数据，如果事务上下文，表和索引的元数据
C)执行计划(QUERY PLAN)，语句和存储过程的执行计划
D)查询优化器(Optimizer)
E)Utilities,如BCP,Log Manager,Backup tools,parallel queries and so on
3.线程内存
Each thread use 0.5MB memory to store data structure and relative infomation
4.The third application apply memeory
like link server, sql mail, user defined CLR,Extended stored procedure,dirver ect
Divided by Apply method:
1.Reserved and committed memory, reserved first and then committed.
2.Stolen memory,commited without reserving memory.
SQL SERVER never use AWE for stolen memery
Divided by Page size:
1.Single page memory,the applied memory is equal or less then 8KB
2.Multiple page memory(MemToLeave), the applied memery is bigger than 8KB
the most part of memery in MemToLeave is not charged by SQL SERVER
SQL SERVER使用Memory Clerk来管理SQL SERVER内存的分配和回收，因此可以使用sys.dm_os_memory_clerks 来查看内存使用情况，注意sys.dm_os_memory_clerks反应的内存不包括第三方代码使用的内存。
SELECT M.type,
sum(M.virtual_memory_reserved_kb) AS VirtualMemoryReservedKB,
SUM(M.virtual_memory_committed_kb) AS VirtualMemortCommitedKB,
SUM(M.shared_memory_committed_kb) AS SharedMemroyCommittedKB,
SUM(M.shared_memory_reserved_kb) AS SharedMemroyReservedKB,
SUM(M.multi_pages_kb) AS MultiPagesKB,
SUM(M.single_pages_kb) AS SinglePagesKB,
SUM(M.multi_pages_kb)+SUM(M.single_pages_kb) AS TotalPagesKB
FROM sys.dm_os_memory_clerks M
GROUP BY M.type
ORDER BY TotalPagesKB DESC
查看表中数据在缓冲池中的信息
SELECT name AS TabelName,
index_id AS IndexId,
COUNT(*)AS CachedPageCount,
CAST(COUNT(*)*8.0/1024 AS INT) AS CachedMemoryMB
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT OBJECT_NAME(OBJECT_ID) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT OBJECT_NAME(OBJECT_ID) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name, index_id
ORDER BY CachedMemoryMB DESC;
