Select * From SYS. dm_ OS _memory_clerks where [type] = 'memoryclerk _ sqlbufferpool'
Select * From SYS. dm_ OS _ring_buffers
-- Total memory consumed by the buffer pool
Select sum (multi_pages_kb + awe_allocated_kb + virtual_memory_committed_kb + bytes) from SYS. dm_ OS _memory_clerks where [type] = 'memoryclerk _ sqlbufferpool'
-- Query the internal components allocated memory in the buffer zone
Select [type], sum (single_pages_kb) as memory_allocated_kb
From SYS. dm_ OS _memory_clerks
Where multi_pages_kb! = 0
Group by [type]
-- Query the internal components of memory allocated outside the buffer zone
Select [type], sum (multi_pages_kb) as memory_allocated_kb
From SYS. dm_ OS _memory_clerks
Where multi_pages_kb! = 0
Group by [type]
-- Read/write 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 wait lock
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