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;