SqlServer内存使用情况

SELECT TOP 500

[session_id],

[request_id],

[start_time] AS '开始时间',

getdate() as 当前时间,

[status] AS '状态',

[command] AS '命令',

dest.[text] AS 'sql语句',

DB_NAME([database_id]) AS '数据库名',

[blocking_session_id] AS '正在阻塞其他会话的会话ID',

[wait_type] AS '等待资源类型',

[wait_time] AS '等待时间',

[wait_resource] AS '等待的资源',

[reads] AS '物理读次数',

[writes] AS '写次数',

[logical_reads] AS '逻辑读次数',

[row_count] AS '返回结果行数'

FROM sys.[dm_exec_requests] AS der

CROSS APPLY

sys.[dm_exec_sql_text](der.[sql_handle]) AS dest

ORDER BY 开始时间

-- 查询SqlServer总体的内存使用情况

select type

, sum(virtual_memory_reserved_kb) VM_Reserved

, sum(virtual_memory_committed_kb) VM_Commited

, sum(awe_allocated_kb) AWE_Allocated

, sum(shared_memory_reserved_kb) Shared_Reserved

, sum(shared_memory_committed_kb) Shared_Commited

--, sum(single_pages_kb) --SQL2005、2008

--, sum(multi_pages_kb) --SQL2005、2008

from sys.dm_os_memory_clerks

group by type

order by type

-- 查询当前数据库缓存的所有数据页面，哪些数据表，缓存的数据页面数量 -- 从这些信息可以看出，系统经常要访问的都是哪些表，有多大？

SELECT
    p.object_id,
    object_name = object_name ( p.object_id ),
    p.index_id,
    buffer_pages = count(*) 
FROM
    sys.allocation_units a,
    sys.dm_os_buffer_descriptors b,
    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 () 
GROUP BY
    p.object_id,
    p.index_id 
ORDER BY
    buffer_pages DESC

-- 查询缓存的各类执行计划，及分别占了多少内存 -- 可以对比动态查询与参数化SQL（预定义语句）的缓存量

SELECT
    cacheobjtype,
    objtype,
    sum(
    cast( size_in_bytes AS BIGINT ))/ 1024 AS size_in_kb,
    count( bucketid ) AS cache_count 
FROM
    sys.dm_exec_cached_plans 
GROUP BY
    cacheobjtype,
    objtype 
ORDER BY
    cacheobjtype,
    objtype

-- 查询缓存中具体的执行计划，及对应的SQL

-- 将此结果按照数据表或SQL进行统计，可以作为基线，调整索引时考虑 -- 查询结果会很大，注意将结果集输出到表或文件中

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;
GO