数据库动态管理视图DMV(2)

SELECT * FROM SYS.dm_os_memory_clerks WHERE [TYPE]='MEMORYCLERK_SQLBUFFERPOOL'

SELECT * FROM SYS.dm_os_ring_buffers
--缓冲池消耗内存总量
SELECT sum(multi_pages_kb+awe_allocated_kb+virtual_memory_committed_kb+shared_memory_committed_kb) FROM SYS.dm_os_memory_clerks WHERE [TYPE]='MEMORYCLERK_SQLBUFFERPOOL'

--查询缓冲区内分配内存的内部组件
SELECT [type],sum(single_pages_kb) as memory_allocated_kb
FROM SYS.dm_os_memory_clerks
where multi_pages_kb!=0
group by [type]
--查询缓冲区外分配内存的内部组件
SELECT [type],sum(multi_pages_kb) as memory_allocated_kb
FROM SYS.dm_os_memory_clerks
where multi_pages_kb!=0
group by [type]

--读写io
SELECT DB_NAME(DATABASE_ID),[FILE_ID],io_stall_read_ms,io_stall_write_ms
FROM SYS.dm_io_virtual_file_stats(NULL,NULL)
ORDER BY io_stall_read_ms+io_stall_write_ms DESC

--io等待锁
SELECT wait_type,waiting_tasks_count,wait_time_ms,signal_wait_time_ms
FROM SYS.dm_os_wait_stats
WHERE wait_type LIKE 'PAGEIOLATCH%' ORDER BY wait_type