Check Memory Information

-- Memory Monitor info
SELECT object_name, counter_name,  cntr_value 
FROM sys.dm_os_performance_counters 
WHERE counter_name IN 
( 'Batch Requests/sec', 'SQL Compilations/sec', 'SQL Re-Compilations/sec', 
'User Connections', 'Page Splits/sec', 'Processes blocked', 
'Memory Grants Pending', 'Checkpoint pages/sec' )
OR ( counter_name = 'Lock Waits/sec' AND instance_name = '_Total' )
UNION
SELECT 'SQLServer:Buffer Manager' AS object_name, 
'Buffer cache hit ratio' AS counter_name,
ROUND( CAST( CV.cntr_value AS float ) / 
CAST( CVB.cntr_value_base AS float ), 4 ) * 100 AS Buffer_Cache_Hit_Ratio
FROM ( SELECT cntr_value AS cntr_value 
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager' 
AND counter_name = 'Buffer cache hit ratio' ) AS CV,
( SELECT cntr_value AS cntr_value_base 
FROM sys.dm_os_performance_counters 
WHERE object_name = 'SQLServer:Buffer Manager' 
AND counter_name = 'Buffer cache hit ratio base' ) AS CVB
UNION ALL
SELECT object_name, counter_name, cntr_value 
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%' 
AND [counter_name] = 'Page life expectancy'
ORDER BY 1, 2
/*
Page Splits/sec    : < 20
Buffer cache hit ratio  : > 90
Buffer命中率，最少要90%以上
Checkpoint pages/sec  : --
Page life expectancy  : > 300
越大越好，代表記憶體夠，不須把暫存資料清掉，一般平均值要 > 300 秒，
代表快取的資料頁在記憶體中保有 5 分鐘以上。
Processes blocked   : 0 is best
User Connections   : --
Lock Waits/sec    : 0 is best
Memory Grants Pending  : 0 is best
越小越好，代表需要記憶體時不必等待，其值最好為 0。
Batch Requests/sec   : the more the better
SQL Compilations/sec  : < 10% of the number of Batch Requests/Sec
SQL Re-Compilations/sec  : < 10% of the number of SQL Compilations
sys.dm_os_sys_memory ：從作業系統傳回記憶體資訊。
sys.dm_os_memory_clerks ：傳回 SQL Server 執行個體目前記憶體的使用狀況。
SQL Server 大量利用記憶體來存放暫存資料、中繼資料、執行計畫快取…等，
當它逐步累積各種結構時，會越吃越大直到我們所設定的「最大伺服器記憶體」量。 
*/

-- 記憶體使用情況, Memory [SQL Use Memory], [OS Free Memory], [OS Total Memory]
SELECT 'SQLServer:Memory manager' AS [ObjectName], 
  'SQL / Free / Total' AS [CounterName], 
  CAST((dopm.physical_memory_in_use_kb/1024) AS varchar)+'MB' AS [UseMemory], 
  CAST((available_physical_memory_kb/1024) AS varchar)+'MB' AS [FreeMemory], 
  CAST((dosm.total_physical_memory_kb/1024) AS varchar)+'MB' AS [TotalMemory] 
FROM sys.dm_os_process_memory AS dopm, sys.dm_os_sys_memory AS dosm

-- 記憶體使用情況, sys.dm_os_memory_clerks , Where memory use in instance
SELECT type,
  sum(single_pages_kb)/1024 [Single記憶體佔用大小(MB)],
  sum(multi_pages_kb)/1024 [Multi記憶體佔用大小(MB)],
  sum(virtual_memory_reserved_kb)/1024 [保留虛擬記憶體的數量(MB)],
  sum(virtual_memory_committed_kb)/1024 [使用虛擬記憶體的數量(MB)]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY 2, 3 DESC

-- 檢查記憶體是否夠用, sys.dm_os_sys_memory , List Memory resources
SELECT total_physical_memory_kb/1024 as [總記憶體(MB)],
  available_physical_memory_kb/1024 as [可用記憶體(MB)],
  total_page_file_kb/1024 as [總分頁檔(MB)],
  available_page_file_kb /1024 as [可用分頁檔(MB)],
  system_cache_kb/1024 as [系統快取記憶體(MB)],
  system_high_memory_signal_state,
  system_low_memory_signal_state,
  system_memory_state_desc 
FROM sys.dm_os_sys_memory 
/*
DBA會希望知道 SQL Server 把記憶體用到哪去了？
一開始若想要綜觀伺服器硬體的記憶體資源，可以查詢 sys.dm_os_sys_memory 動態管理檢視。
它會告訴你該機器的實體與虛擬記憶體的大小、當下可用的量有多少，記憶體是否足夠等資訊。
*/

-- 資料庫佔用記憶大小, sys.dm_os_buffer_descriptors , what data in memory buffer
SELECT ISNull(DB_NAME(database_id),N'資料資料庫') [資料庫],
  Count(*)/128 as [耗用暫存記憶體(MB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY 2 DESC

-- sql buffer memory usage
SELECT OBJECT_NAME(p.object_id) AS [物件名稱],
  p.index_id,
  Count(*)/128 AS [耗用暫存記憶體(MB)],
  Count(*) AS [暫存區數量]
FROM sys.dm_os_buffer_descriptors dobd
JOIN sys.allocation_units au ON au.allocation_unit_id=dobd.allocation_unit_id
JOIN sys.partitions p ON au.container_id=p.hobt_id
WHERE dobd.database_id = DB_ID()
AND  ObjectProperty(P.object_id,'ISSystemTable') = 0
GROUP BY p.object_id, P.index_id
ORDER BY 4 DESC

/*
實體記憶體 自定建議值
2GB  1500MB
4GB  3200MB
6GB  4800MB
8GB  6400MB
12GB  10000MB
16GB  13500MB
24GB  21500MB
32GB  29000MB
48GB  44000MB
64GB  60000MB
72GB  68000MB
96GB  92000MB
128GB  124000MB
*/