SQL Server 記憶體使用量說明

         最近常常被問到，假設主機上記憶體總共有16G，並限制SQL Server最大可使用的記憶體只有8G，但是為何從 [工作管理員] 看到 SQL Server的服務 [sqlservr.exe] 總是會使用超過8G的限制，是否設定的限制無效，或是該如何設定與確認，所以我希望透過此篇來告訴大家如何進行確認。

相關文章：
SQL Server - x86與x64的架構下 - 最佳記憶體設定
http://caryhsu.blogspot.com/2011/11/sql-server-x86x64.html

可能的情況會有兩種情況，以下我再逐一說明：
1、第一種是您沒有正確的設定 [鎖定記憶體分頁] 權限，所以就算您有設定 SQL Server的記憶體限制，但此設定還是不會啟用，確認方式，您可以從 SQL Server Error Logs看到下列的訊息時，代表您沒有設定正確，詳細的設定方式，再請參考我另一篇文章 [SQL Server - x86與x64的架構下 - 最佳記憶體設定] 進行設定。

錯誤訊息： 
Cannot use Large Page Extensions:  lock memory privilege was not granted.

2、另外一個最重要的，由於 [max server memory] 主要是管理 SQL Server 中 Buffer Pool的區塊，當您的系統中可能有使用到如 Extended Stored Procedures、COM objects, non-shared DLLs, EXEs, 和MAPI等元件時，就有可能造成超過限制的情況發生，如要真正的確認目前SQL Server中 Buffer Pool的使用情況，您可以透過下列的方式進行確認。

開始 -> 系統管理工具 -> 效能監視器 -> 效能 -> 監視工具 -> 效能監視器 -> 點選 [ + ] 的按鈕。

選擇 [SQLServer：Memory Manger] -> [Target Server Memory (KB)] 與 [SQLServer：Memory Manger] -> [Total Server Memory (KB)]加入後點選 [確定]。

[SQLServer：Memory Manger] -> [Target Server Memory (KB)]：伺服器可用的動態記憶體總數。
 [SQLServer：Memory Manger] -> [Total Server Memory (KB)]：來自緩衝集區的認可記憶體 (以 KB 計)。

加入後，你就可以看到目前的使用量為多少。

官方說明：

You can establish upper and lower limits to the amount of memory (buffer pool) used by the SQL Server database engine with the min server memory and max server memory configuration options. Before you set the min server memory and max server memory options, review the references in the section titled "Memory" in the following Microsoft Knowledge Base article:

Note that the max server memory option only limits the size of the SQL Server buffer pool. The max server memory option does not limit a remaining unreserved memory area that SQL Server leaves for allocations of other components such as extended stored procedures, COM objects, non-shared DLLs, EXEs, and MAPI components. Because of the preceding allocations, it is normal for the SQL Server private bytes to exceed the max server memory configuration. For additional information about allocations from this unreserved memory area, click the article number below to view the article in the Microsoft Knowledge Base:

參考網址：
How to adjust memory usage by using configuration options in SQL Server
http://support.microsoft.com/kb/321363/en-us
Inside SQL Server 2000's Memory Management Facilities
http://msdn.microsoft.com/en-us/library/aa175282(v=sql.80).aspx
SQL Server 的 Memory Manager 物件
http://technet.microsoft.com/zh-tw/library/ms190924.aspx

關鍵字：SQL Server、Memory Manager、max server memory、buffer pool、Task Manager