MSSQL 2008 R2 MSSQLSERVER_701：資源集區 'internal' 中的系統記憶體不足，無法執行此查詢－MISTECH 技術手抄本

MS SQL Server 2008 R2 在 Windows Server 2008 R2 SP1 上運行一段時日，近來常發現 MS SQL Server 2008 R2 常因 MSSQLSERVER_701：資源集區 'internal' 中的系統記憶體不足，無法執行此查詢 而自動停止 SQLSERVERAGENT 服務，導致 MSSQL Server 自動停止。

Google 一下有人回應：

使用 SQL Server 組態管理員調整 SQL Server 啟動的參數，於原本的參數之後，再加入如下的參數：

;-g384

調整完畢，要重新啟動 SQL Server 服務。參考：http://technet.microsoft.com/zh-tw/library/ms190737.aspx 說明

在啟動參數最後加入 ;-g384 ，再觀察看看。

另外，當您執行 Database Engine 的多個執行個體於同一台主機時，可以使用 max server memory 來控制每個 MSSQL SERVER 的最大記憶體使用量

操作的步驟如下：

1. 在 [物件總管] 中，以滑鼠右鍵按一下伺服器，然後選取 [屬性]。
2. 按一下 [記憶體] 節點。
3. 在 [伺服器記憶體選項] 底下，輸入 [最小伺服器記憶體] 和 [最大伺服器記憶體] 所要的數量。使用預設值可允許 SQL Server 根據可用的系統資源來動態變更它的記憶體需求。 [最小伺服器記憶體] 的預設值是 0，[最大伺服器記憶體] 的預設值是 2147483647 MB。

建議實體記憶體與配置給 MSSQL Server 的記憶體大小如下：

Physical RAM         MaxServerMem Setting
2GB                            1,500
4GB                            3,200
6GB                            4,800
8GB                            6,400
12GB                          10,000
16GB                          13,500
24GB                          21,500
32GB                          29,000
48GB                          44,000
64GB                          60,000
72GB                          68,000
96GB                          92,000
128GB                        12,4000

Physical RAM            Target Avail RAM in Task Manager
< 4GB                               512MB – 1GB
4-32GB                              1GB – 2GB
32-128GB                            2GB – 4GB
> 128GB                              > 4GB

以下為實務上的解決方法：

1. 實體記憶容量若總共有8G，則請調整MAX SERVER MEMORY為6 GB，請保留 2GB 給作業系統使用，您可視實際情況再進行調整。請您進行下記憶體的設定確認。
   a. 開啟 Management Studio。
   b. 開啟一個查詢視窗。
   c. 輸入並執行下列的語法：

USE master;
GO

EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
EXEC sp_configure;

EXEC sys.sp_configure N'max server memory (MB)', N'6144'
GO

RECONFIGURE WITH OVERRIDE
GO

d. 再次輸入下列的語法確認 [max server memory (MB)] 是否已變成 6144。

EXEC sp_configure N'max server memory (MB)';
GO

e. 將sp_configure的 [顯示進階選項] 設定關閉。

USE master;
GO
EXEC sp_configure 'show advanced option', '0';
RECONFIGURE;

2)為了避免 SQL Server Process 被強迫釋放，請您依據以下的作法進行設定[鎖定記憶體分頁] 權限：

a.您的SQL Server 2008 R2 是Standard版本，必須加上SQL Server啟動參數requires trace flag 845, 也就是-T845，設定lock page in Memory才會生效。
Support for Locked Pages on SQL Server 2008 R2 Standard Edition x64, 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

b. 調整 SQL Server Services Account 擁有 Lock Page In Memory 權限：
b-1. 請檢查組態管理員的 SQL Server 和 SQL Server Agent 的啟動帳戶是否為網域帳戶，且此帳戶擁有本機管理者權組的權限。
b-2. 確認SQL Server 和 SQL Server Agent 啟動帳戶擁有 [鎖定記憶體分頁] 權限。

c. 在 "本機安全性原則"，如何啟用 [鎖定記憶體分頁選項]：
c-1. 在 [開始] 功能表上，按一下 [執行]。在 [開啟舊檔] 方塊中，輸入 gpedit.msc。此時會開啟 [群組原則] 對話方塊。
c-2. 在 [群組原則] 主控台中，依序展開 [電腦設定] 和 [Windows 設定]。
c-3. 展開 [安全性設定]，然後展開 [本機原則]。
c-4. 選取 [使用者權限指派] 資料夾。這些原則會顯示在詳細資料窗格中。
c-5. 在窗格中連按兩下 [鎖定記憶體分頁]。

c-6. 在 [本機安全性原則設定] 對話方塊中按一下 [新增]。
c-7. 在 [選擇使用者或群組] 對話方塊中加入一個具有執行 sqlservr.exe 權限的帳戶。
c-8. 在命令提示字元下，執行 “gpupdate /force” 或登出您的帳戶再登入本機。
c-9. 重起 SQL Server 服務或重開機器。

(Option)另外，建議您更新至SQL Server 2008 R2 Service Pack 1來避免已知問題。
"Non-yielding Scheduler" error and SQL Server 2008 or SQL Server 2008 R2 stops responding intermittently in Windows Server 2008 or in Windows Server 2008 R2
http://support.microsoft.com/kb/2491214

MicrosoftR SQL ServerR 2008 R2 Service Pack 1 下載
http://www.microsoft.com/zh-tw/download/details.aspx?id=26727

建議您可以直接更新到SQL 2008 R2 Service Pack 2來避免更多已知問題
MicrosoftR SQL ServerR 2008 R2 Service Pack 2

http://www.microsoft.com/zh-tw/download/details.aspx?id=30437
http://www.microsoft.com/en-us/download/details.aspx?id=30437

參考文件：

http://technet.microsoft.com/zh-tw/library/aa337311%28v=sql.105%29.aspx

http://technet.microsoft.com/zh-tw/library/ms190737.aspx

http://technet.microsoft.com/zh-tw/library/ms178067.aspx