使用DMV(Dynamic Management View)監控記憶體

當資料庫有問題時第一個動作就是要先尋找問題點在哪裡。在SQL 2005之後的版本都有DMV，使用DMV(Dynamic Management View)可以幫助我們輕易的獲得資料庫的資訊讓我們可以更容易的管理資料庫。

管理SQL SERVER第一件事情就是要取得作業系統所擁有資源，如記憶體、CPU、硬碟(DISK)等，在瞭解作業系統所擁有資源後才可以精準地分析所擁有資源是否足夠，再來就是觀察每一個資料使用資源的情況，在這裡我使用DMV取得作業系統可用的資源，接著在取得每一個資料庫正在使用的記憶體資訊以及每一個SQL 指令所需要的記憶體資訊。

1.      使用dm_os_sys_info取得作業系統的CPU與記憶體。

--取得作業系統的cpu和記憶體的資訊

SELECT

cpu_count AS [邏輯CPU數],

hyperthread_ratio AS [邏輯和實體處理器數目的比率],

cpu_count/hyperthread_ratio AS [實體CPU數],

physical_memory_in_bytes/(1024*1024) AS [實體記憶體MB]

FROM sys.dm_os_sys_info;

執行結果：

2.      取得每一個資料庫所用到的記憶體。

--每個資料庫用到的記憶體

--sys.dm_os_buffer_descriptors :SQL Server 在buffer pool中所有data pages的資訊

--一個row就是一個page，一個page為kb。

SELECT

CASE database_id WHEN 32767 THEN 'ResourceDb'

ELSE db_name(database_id)    END AS [資料庫名稱],

CAST(COUNT(*) * 8.0 / (1024.0) AS  DECIMAL(16,2)) AS [記憶體使用量(MB)]

FROM sys.dm_os_buffer_descriptors

GROUP BY database_id

ORDER BY 2 DESC

執行結果：

3.      每個SQL指令所用到的記憶體

--每個SQL指令所用到的記憶體

SELECT DB_NAME() [資料庫名稱]

, a.required_memory_kb/1024.0 AS [執行此查詢所需的最小記憶體MB]

, a.max_used_memory_kb/1024.0 AS [到目前為止使用的最大實體記憶體MB]

, a.granted_memory_kb/1024.0  AS [實際授與的記憶體總數MB]

, a.used_memory_kb/1024.0 AS [目前使用的實體記憶體MB]

, a.query_cost [估計的查詢成本]

, a.dop [CPU平行處理程度]

, SQLCMD.[text] AS [SQL指令]

FROM sys.dm_exec_query_memory_grants AS a

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS SQLCMD

ORDER BY a.requested_memory_kb DESC ;

執行結果：