自調整的數據庫引擎(Self-tuning Database Engine)
長期以來，微軟都致力于自調整(Self-Tuning)的SQL Server數據庫引擎，用以降低產品的總擁有成本。從SQL Server 2005開始，SQL Server就是動態管理內存使用，并且調整內存使用時，不需要重啟數據庫引擎。
所以它也不提供內存分配的微調項。各個組件的內存分配，完全由數據庫引擎自動管理，不能手動分配。但是這貨還是提供了一些配置項，能夠影響數據庫引擎如何使用內存。
是否使用這些配置項來替代默認值，取決于操作系統版本，SQL Server版本，可用物理內存和處理器架構等。
SQL Server是怎么分配內存的
SQL Server本身設計就會盡可能多的使用內存。正常情況下，它不會釋放已經分配的內存，除非OS引發并設定Low Memory資源通知標記(Resource Notification Flag)。
SQL Server 2005的SQLOS中添加了一個專用線程用于監控OS發出的內存通知(Memory Notification)(這也是自調整功能之一)。
OS中有兩種類型的內存通知：
Memory High：SQL Server可以增加Working Set使用量并使用更多內存
Memory Low：OS有內存壓力，SQL Server釋放一些內存給OS
如果兩種內存通知OS都沒設定，則表明內存使用穩定，SQL Server將繼續在現有的進程空間內運行。但是這個功能在Windows 2003和SQL Server 2005之前是沒有的。
內存壓力分類，根據Memory PRessure - Classified：
SQL Server可以使用多少內存，取決于:
32位VAS的限制
Windows在VAS中運行每一個進程。32位的進程最多只可尋址到4GB內存，而這4GB內存又分為內核模式(Kernel Mode)空間和用戶模式(User Mode)空間。默認，windows會各分配2GB。
內核模式主要用于OS，用戶模式用于當前執行的應用程序進程(例如SQL Server)。
1. 用戶模式VAS分配和VirtualAlloc
SQL Server保留的2GB用戶模式VAS，當出現物理內存分配時才會提交。它是通過VirtualAlloc這個Windows API。
32位的SQL Server或者Windows,調用VirtualAlloc返回一個32位的指針，這就是為什么SQL Server只能使用到2GB用戶模式VAS的原因。
通過VirtualAlloc分配的內存并不一定是實際物理內存，當分配的內存被提交時，才會是RAM的內存。提交內存時，windows要確認SQL Server及其它應用程序進程提交的內存總量<=(RAM+分頁文件)。
需要注意是VirtualAlloc分配的內存是可分頁內存，意味著OS出現內存壓力時，它們會被分頁(page out)到磁盤上。
2. 非緩存池分配(MemToLeave)
SQL Server占用的大部分內存分配給了緩存池，用于緩存數據和查詢計劃。當需要大于8KB的連續頁時，會通過多頁分配器分配非緩存池，如LinkedServer,線程堆棧，CLR,備份緩存等。
為了確保有足夠的非緩存池內存，32位SQL Server在啟動時就會保留部分VAS。保留的部分也叫做MemToLeave,大小=MaxWorkerThread*0.5MB+256,其中MaxWorkerThread=(ProcessorCount-4)+256.
默認情況下MemToLeave=256*0.5+256=384MB,所以緩存區的大小約為(2GB-384MB)=1664MB。
3. VAS調整(VAS Tuning)
在有4GB內存的服務器上，可以使用VAS調整使得用戶模式VAS占到3GB,內核模式VAS減少為1GB.
需要注意的是內核模式內存的減少，使得系統PTEs(Page Table Entires)減少，造成系統不穩定，同時SQL Server可以尋址到的內存也變少了。
Windows 2008上實現VAS Tuning,使用BCDEdit /set IncreaseUserVa [value](value取2048到3072間的值)。
4. AWE(Address windowing extension)
在多于4GB RAM的服務器上，可以使用AWE讓SQL Server使用內存。使用AWE需先啟用PAE，在windows 2008上使用BCDEdit /set PAE ForceEnable啟用。
然后SQL Server開啟"AWE Enabled”,服務賬號需要具有鎖定內存頁的權限(Lock Pages In Memory,在組策略分配這個用戶權限)。
AWE使內存管理的指針由32擴展到36位，所以最能尋址64GB內存。并且分配內存時，不使用VirtualAlloc而使用AllocateUserPhysicalPages函數。此API通過PTE直接分配物理內存。
AWE的內存只能被緩沖池(Buffer Pool)使用，并且是被鎖定和不可分頁的，所以最好使用設定“'max server memory”來限制一下量。
5. -g啟動參數
32位平臺上可以使用SQL Server的-g啟動參數指定MemToLeave內存量，從而提高MemToLeave的內存分配量。但同時這也會減少緩存池的分配量。
使用64位的SQL Server
64位平臺的VAS理論上限可達16EB=16,000,000TB,實際上X64限制在8TB,IA64為7TB。使用超過4GB RAM時，SQL Server不用進行額外配置。
SQL Server使用的內存只能通過VAS提交，所以所有內存都是非鎖定的和可分頁的。這樣當OS有內存壓力時，這些內存可能會被分頁到磁盤(hard page out)。
VAS如此充足，MemToLeave的分配理論已經不再適用，同理-g啟動參數也沒有意義。
過程緩存(Procedure cache)也會存得更多，這可能會帶來過程緩存過量的問題。
64位SQL Server的內存配置選項
1. 最小/大服務器內存
SQL Server提供了兩個實際級別的，限制緩存池大小的配置項：min server memory/max server memory.需要注意的是從SQL Server 2000到2008 R2，這兩個配置只對緩存池(Buffer pool)有效。
在啟用了“鎖定內存頁”時，兩都的差值意味著：當有外部內存壓力時，SQL Server可以調整的范圍。
設定最大值時，沒有一個通用的值。初始化配置的基本原則：服務器內存<=16GB時，OS保留1GB,每4GB RAM保留1GB;>=16GB時，OS保留1GB,每8GB RAM保留1GB.
例如：32GB的服務器，最大值=32-1-4=27GB。然后確保性能計數器Memory/Available Mbytes介于150~300之間，逐漸調整max server memory。
2. 鎖定內存頁(lock pages in memory)
64位SQLOS默認使用VirtualAlloc分配所有的內存，此API分配的內存是非鎖定和可分頁的。當OS有內存壓力時標記MemoryLow, SQL Server會釋放內存直到”最小服務器內存”。
如果它釋放的速度不夠快或者釋放的量不滿足于OS，則這些內存會被分頁到分頁文件。對于使用大內存的SQL Server，WorkingSet分頁對性能影響是非常嚴重的。
啟用鎖定內存頁，使得SQL Server分配緩存池內存時使用AWE API AllocateUserPhysicalPages。此函數分配的內存是鎖定的和不可分頁的。
而緩存池占用著SQL Server大部分內存，所以啟用鎖定內存頁會很大程度上避免WorkingSet分頁。AWE Enabled配置項在64位SQL Server是無效的空操作。
啟用鎖定內存頁后，任務管理器的SQLServr.exe顯示的是非緩存池內存用量。需要使用SQL Server:Memory Manager/Total Server Memory查看總的內存用量。
啟用鎖定內存頁是SQL Server 2005/2008/2008 R2企業版和2008 R2標準版的功能。在2008 SP1_CU2和2005 SP3_CU4更新后，也可以通過啟用跟蹤標記845來其它版本啟用鎖定內存頁。
3. LPA(Large Page Alloction)
在X64系統上，大頁分配是指使用2MB的大小分配內存頁，默認內存頁是4KB。啟用LPA需要滿足條件:a). SQL Server 企業版 b).服務器RAM>=8GB c).啟用鎖定內存頁
X64系統上，啟用LPS(Large Page Support)和跟蹤標記834，SQL Server將使用大頁分配緩存池內存，并且SQL Server的啟動時間顯著增長。
要嚴格測試性能受益情況，并盡量在SQL Server專用服務器上啟用。
診斷內存壓力
通過性能計數器和DMV來判斷系統是否有在在內存壓力。需要謹記的一條：通過一兩個性能計數器，是不能確定任何系統壓力的，要全面的分析。
SQL Server:Buffer Manager下的性能計數器：
1. Buffer Cache Hit Ratio
建議值是OLTP>=95%,OLAP>=90.這個計數器本身并不能說明SQL Server有內存壓力，>=95%只是說明了SQL Server按設計的那樣執行了數據頁的預讀預取。
2. Page Life Expectancy
以秒為單位，代表高速緩存的頁過期并所占空間被重用的時間。
3. Free Pages
SQL Server緩沖池中的空閑頁數量。當Page Life Expectancy計數器持續下降，Free Pages接近0，Free List Stalls持續大小0，則是表示明顯的內存壓力。
4. Free List Stalls/Sec
每秒請求等待緩沖池中空閑頁的次數。
5. Lazy Writes/sec
每秒被LazyWriter進程刷新的緩沖池數據頁數量。發生Lazy Writes/sec的同時PLE和Free Pages較低,又發生Free List Stalls，則說明缺少RAM。
SQL Server:Memory Manager下的性能計數器：
1. Total Server Memory (KB) 和Target Server Memory (KB)
前者表示SQL Server已經占用的內存量，后者表示SQL Server想要占用的內存量。后者大于前者時，證明SQL Server需要更多可用內存，也是內存壓力標志之一。
2. Memory Grants Outstanding
成功獲得workspace內存的進程總數。值太低表示有大量的用戶活動或者負載過重，如果同時Memory Grants Pending值偏高，則也是內存壓力的標志之一。
3. Memory Grants Pending
正在等待被授予workspace內存的進程總數。
內存相關的DMVs
sys.dm_exec_query_memory_grants,sys.dm_os_memory_cache_counters,sys.dm_os_sys_memory,sys.dm_os_memory_clerks
常見的內存相關的問題
1. SQL Server內存泄露的誤區
SQL Server看起來部會吃掉服務器盡可能多的內存，這不是內存泄露。擴展存儲過程或者鏈接服務器驅動的內存泄露，可能會導致SQL Server無限制地去獲取內存。
2. 分頁問題
從SQL Server 2005 SP2后，當SQL Server進程的WirkingSet被收縮并分頁到磁盤上時，錯誤日志中會寫入"a significant part of SQL Server process memory has been paged out."
可能的原因有：
1.設定了不正確的最大服務器內存，并且未啟用鎖定內存頁
2.Windows執行非緩沖的IO操作占用了大量的系統調整緩存，如拷貝文件
3.硬件驅動問題導致的內存過量使用或內存泄露
參考KB918483并找到進程工作集被收縮的根本原因并解決之。對于SQL Server而言啟用鎖定內存頁，是取后也是解決此問題的唯一手段。
3. 啟用了鎖定內存頁但未限制最大服務器內存導致OS不穩定
啟用了鎖定內存頁但未限制最大服務器內存或者設定了過高的值，SQL Server會占盡可用內存，從而導致OS缺少內存而不穩定甚至崩潰。
啟用了鎖定內存頁，一定要限制最大服務器內存，留下合適的內存量給OS使用。
4. 應用程序域標記為卸載導致內存壓力(App Domain is marked for unload due to memory pressure)
這是SQLCLR相關的錯誤，通常在32位SQL Server上發生，也可能在設定了最大服務器內存，限制了SQLCLR的可用VAS的64位SQL Server上發生。
一般是由于SQLCLR程序集低效的內存使用方式和SQLCLR可用的VAS受限引起。
32位上發生此錯誤，建議升級到64位，以使用更多的用戶模式VAS。但是如果因為SQLCLR程序集使用大內存對象(如DataSet)導致，升級可能也解決不了問題，
把SQLCLR代碼做成獨立的控制臺或者WinForm程序并正確配置其內存使用。
如果SQLCLR存儲整個執行的狀態，但是代碼訪問安全性(Code access Security)定義為UNSAFE，則卸載會丟失狀態信息導致更嚴重的問題。
解決此問題最好是升級到64位，臨時的解決方案是使用-g啟動參數增加MemToLeave內存給SQLCLR使用。
5. 701錯誤和FAILED_VIRTUAL_RESERVE
當SQL Server分配一段連續的VAS區域失敗時，就會報此錯誤并輸出請求分配的大小。
通常這個錯誤只出現在MemToLeave受限的32位系統上，因為各種需要分配大于8KB的操作類型，如設定了過大備份緩存，xml,SQLCLR,空間數據類型和鏈接服務器等。
解決此問題最好是升級到64位，臨時的解決方案是使用-g啟動參數增加MemToLeave內存。
6. 過渡分配的虛擬機
今時今日，SQL Server虛擬化已經很常見了。虛擬化的管理器提供的高級功能“內存過載(Memory Overcommit)”允許虛擬機的內存總量超過宿主服務器實際RA
新聞熱點
疑難解答
