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或主機。

參考連結：

• 如何：啟用鎖定記憶體分頁選項 (Windows)http://technet.microsoft.com/zh-tw/library/ms190730(SQL.90).aspx
• Do I have to assign the Lock Pages in Memory privilege for Local System? http://blogs.msdn.com/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx
• Support for Locked Pages on SQL Server 2005 Standard Edition 64-bit systems and on SQL Server 2008 Standard Edition 64-bit systems
  http://support.microsoft.com/kb/970070/en-us/

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


參考連結：

• 使用 AWE
  http://technet.microsoft.com/zh-tw/library/ms175581.aspx
• 針對 4 GB 以上的實體記憶體啟用記憶體支援
  http://technet.microsoft.com/zh-tw/library/ms179301.aspx
• 針對 SQL Server 啟用 AWE 記憶體
  http://technet.microsoft.com/zh-tw/library/ms190673.aspx

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
2GB	1500
4GB	3200
6GB	4800
8GB	6400
12GB	10000
16GB	13500
24GB	21500
32GB	29000
48GB	44000
64GB	60000
72GB	68000
96GB	92000
128GB	124000

參考連結：

• How to determine the appropriate page file size for 64-bit versions of Windows Server 2003 or Windows XP
  http://support.microsoft.com/kb/889654/en-us
• How to reduce paging of buffer pool memory in the 64-bit version of SQL Server http://support.microsoft.com/kb/918483/en-us
• Memory Architecture
  http://msdn.microsoft.com/en-us/library/ms187499.aspx
• 動態記憶體管理
  http://msdn.microsoft.com/zh-tw/library/ms178145.aspx
• SQL Server and Large Pages Explained….
  http://blogs.msdn.com/b/psssql/archive/2009/06/05/sql-server-and-large-pages-explained.aspx
• Tuning options for SQL Server 2005 that is running in high performance workloads
  http://support.microsoft.com/kb/920093
• How to reduce paging of buffer pool memory in the 64-bit version of SQL Server
  http://support.microsoft.com/kb/918483/en-us/
• Memory Limits for Windows Releases
  http://msdn.microsoft.com/en-us/library/windows/desktop/aa366778(v=vs.85).aspx#physical_memory_limits_windows_server_2003_sp2
• Suggested Max Memory Settings for SQL Server 2005/2008
  http://www.sqlservercentral.com/blogs/glennberry/archive/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008.aspx

關鍵字：SQL Server、AWE、Address Windowing Extensions、Memory Architecture、鎖定記憶體分頁、Lock Pages in Memory、x86、x64