2011年12月27日 星期二

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 ServerMemory Managermax server memorybuffer poolTask Manager

6 則留言:

  1. Cary你實在太厲害了
    你寫的我幾乎都不會...
    學習學習...

    回覆刪除
  2. 同學你太客氣了,我的師父們可是更厲害,改天可以再交流吧!!

    回覆刪除
  3. 可以請問同一個 store procedure 再 manager 裡執行才幾秒就可以完成
    ,為何改用 application 程式透過 ado 來執行就變的超慢, 有時候甚至超過
    1 分鐘以上? 真不知道該如何解決懇請賜教. 謝謝! Sarger

    回覆刪除
    回覆
    1. 你可以先錄個兩邊的執行計畫進行比較,我猜應該是執行計畫的差異,如果比對後仍然相同時,而你的AP與DB又是不同台,那你可能要錄個網路封包確認一下。

      刪除
  4. CaryHsu請問一下為何我工作管理員所看到的SQL SERVER使用的記憶體與效能監視器的不一樣呢?
    https://www.dropbox.com/s/pdvwc11jd91bb3e/SQL%20AWE.png

    回覆刪除
    回覆
    1. 請參考上面說的第二點,還有備註中的英文有說到,再請參考。

      刪除