2011年11月2日 星期三

SQL Server - x86與x64的架構下 - 最佳記憶體設定

        在SQL Server的版本中,有分為32位元與64位元,其中最大的差別就是記憶體的部份,但就我的觀察,大家也常常疏忽記憶體的設定規則,尤其是64位元的記憶體設定,所以造成系統效能不佳等情況,所以我整理了下列這一篇來跟大家說明如何進行32位元與64位元的記憶體設定。

1、鎖定記憶體分頁 (Lock Pages in Memory)
這個應該算是最多人疏忽的一部份,雖然在官方的文件說明可以不需設定,但由於如果啟動帳號的權限不足時,仍然可能造成無法取得更高的記憶體,就我遇到的情況是系統有32G,但是SQL Server只有吃到19G,可用記憶體還有10G左右,也沒有限制記憶體的使用量,但是SQL Server的記憶體就一直吃不上去,後來經確認主要是這個問題所造成,所以還是請大家千萬不要忘記。

設定方式:
  • 在 [開始] 功能表上,按一下 [執行]。在 [開啟舊檔] 方塊中,輸入 gpedit.msc
  • 此時會開啟 [群組原則] 對話方塊。
  • 在 [群組原則] 主控台中,依序展開 [電腦設定] 和 [Windows 設定]。
  • 展開 [安全性設定],然後展開 [本機原則]。
  • 選取 [使用者權限指派] 資料夾。
  • 這些原則會顯示在詳細資料窗格中。
  • 在窗格中連按兩下 [鎖定記憶體分頁]。
  • 在 [本機安全性原則設定] 對話方塊中按一下 [新增]。
  • 在 [選擇使用者或群組] 對話方塊中加入一個具有執行 sqlservr.exe 權限的帳戶。
  • 在命令提示字元下,執行 gpupdate /force 或登出您的帳戶再登入本機。
  • 重新啟動SQL Server Service或主機。



參考連結:


2、AWE設定 (Address Windowing Extensions)
在32位元的架構設制下,記憶體最大只能對應到4G,而應用程式最多只能使用2G,雖然可以透過 /3GB 的參數,讓應用程式取得3G的記憶體,但對於資料庫軟體這種吃記憶體的應用程式說,這樣還是不夠的,尤其是當你的記憶體超過4G的時候,可以透過AWE的啟用,讓SQL Server取得更多的記憶體。

但是在64位元的情況下,由於沒有記憶體定址的問題,所以其實這個功能可以不需要啟用,但是如果啟用會不會有影響呢?其實也是不會,主是這個觀念還是要跟大家說清楚。

設定方式:
EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'awe enabled', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO



參考連結:


3、SQL Server可用記憶體限制設定
不管是那一種資料庫,基本上都是屬於非常吃記憶體的應用程式,所以如果你沒有設定可用的範圍值的時候,那會變成資料庫與作業系統一直持續不斷的調整記憶體的使用量,造成記憶體的使用不佳,但是應該設定多少才是合適的,大家可以參考下列的對應值進行,藉以保留最佳的記憶體。

設定方式:
EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'3200'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO


實體記憶體Max Server Memory
2GB1500
4GB3200
6GB4800
8GB6400
12GB10000
16GB13500
24GB21500
32GB29000
48GB44000
64GB60000
72GB68000
96GB92000
128GB124000


參考連結:

關鍵字:SQL ServerAWEAddress Windowing ExtensionsMemory Architecture鎖定記憶體分頁Lock Pages in Memoryx86x64

23 則留言:

  1. 好專業喔!不愧是在雲端上的~~~~~~~~~大師阿~~

    回覆刪除
  2. 大師...一不小心google就帶我來這兒了....拜一下..bigbear

    回覆刪除
  3. 樓上的大哥謝謝你的來訪,再請你多多參考了,寫的不好的地方,要記得偷偷的告訴我哦,謝謝了。

    回覆刪除
  4. 如果是SQL server cluster 有兩個node,instance在哪個node就重新啟動該台主機嗎?還是兩個node都必須reboot?

    回覆刪除
    回覆
    1. 你的問題不太清楚,你為何要重新啟動,而且有何特定的原因手動切換嗎?再描述的清楚點,我再儘速的回覆你。 ^^

      刪除
    2. 大哥你好,是這樣的我問的是關於記憶體設限,設了12G上限,依然吃到14G多,如果是SQL cluster使用了"鎖定記憶體分頁 (Lock Pages in Memory)"這個方法,必須要重新啟動SQL Server Service或主機,那麼是兩台主機都必須要重新啟動,還是擁有SQL server service的node單台啟動就生效?不知我這樣的描述清不清楚@@

      刪除
    3. 你必須兩台主機都要重啟,你可以先重啟的Passive節點的機器後,切換身份(Active -> Passive)之後,再將原本的Active機器重啟即可。

      刪除
  5. 感謝大哥的解答,這樣的作法應該只有移轉時的downtime,不會因為sql server service重啟而這期間兩台都無法使用,應該是這個意思吧?!

    回覆刪除
    回覆
    1. 沒錯,這樣的作法,的確可以減少正式機的downtime.

      刪除
  6. 請問一下,如果設定最大記憶體完成後,需要把服務重啟嗎?

    回覆刪除
    回覆
    1. 如果你只是把調整最大記憶體設定,是不需要重啟服務的。

      參考網址:
      SQL Server 2008 R2 memory settings
      http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/b2e6dc8b-52fb-4280-91c6-a8ec1e899e67/

      Server Memory Options
      http://msdn.microsoft.com/en-us/library/aa196734(v=sql.80).aspx

      刪除
  7. 剛剛在google找一些資料, 就找到這裡, 這裡寫的都令人很容易明白, 很感謝你!

    回覆刪除
    回覆
    1. 謝謝你的回覆,也希望我的文章可以幫助到更多的人。 ^^

      刪除
  8. SQL Server2005(64位元)屬性裡有個設定"提高SQL Server優先權",
    請問甚麼狀況下需勾選呢?

    回覆刪除
    回覆
    1. 通常在SQL Server上都不會安裝其他的應用軟體,如果有的話也建議可以搬移到不同的機器上,基本上也不太建議此設定值,你可以參考下列的文章說明,也希望對你有幫助。

      Priority boost details – and why it’s not recommended
      http://blogs.msdn.com/b/arvindsh/archive/2010/01/27/priority-boost-details-and-why-it-s-not-recommended.aspx

      刪除
  9. 謝謝您的文章,獲益良多。但因我們的SQL是在VM上,爬了一下GOOGLE上的文,發現似乎並不適合設"Lock Pages in Memory"?您的看法如何呢?

    回覆刪除
    回覆
    1. 針對我提供的文章與官網的建議,皆是建議設定此值,不知你是否有看到那些文章或是說明不適合的部份,再請分站一同討論。

      刪除
  10. 有個問題請教
    sql2012 中 作為設定鏡像,此時主體的server 可完全執行RW DB
    那麼鏡像的Server 是否可提供 Read only 的查詢哩!!!

    回覆刪除
    回覆
    1. 在我的另一篇文章中有提到,你可以透過快照的方式進行,但是仍會有些注意事項,網址如下。

      參考文章:
      http://caryhsu.blogspot.tw/2011/03/sql-server-load-balancing.html

      刪除
  11. 謝謝您的分享~
    想請問一個問題~
    按照步驟設定去修改記憶體都會出現下述錯誤導致無法成功設定
    "指定的相似性遮罩與此系統上的 CPU 遮罩不相符"
    能請您提供協助嗎? 謝謝!!

    回覆刪除
    回覆
    1. 請問一下,你是否有設定到其他的設定值,如affinity mask等,再請詳細確認伺服器的設定值。

      刪除
  12. 我是新手,請問要如何進入命令模式,以便輸入指令,謝謝

    回覆刪除
    回覆
    1. 開始 -> 搜尋 輸入cmd
      把指令輸入上去就可以了

      刪除