Sql-Server
SQL Server 的 Buffer Cache Size 穩步下降
我們一直遇到 SQL Server 的問題，其中緩衝區記憶體大小隨著時間的推移而減小，以至於我們必須重新啟動服務以釋放記憶體。它變得足夠低，影響性能。
伺服器本身有 32GB 的 RAM。我們將 SQL Server 上的最大記憶體設置為 28GB。在撰寫本文時，緩衝區記憶體為 5.5GB。
SQL Server 的版本是
Microsoft SQL Server 2014 - 12.0.2269.0 (X64) Jun 10 2015 03:35:45 版權所有 (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 (Build 9600:)
的輸出
sys.dm_os_process_memory是：
- total_physical_memory_kb 33539548
- available_physical_memory_kb 926132
- total_page_file_kb 41427432
- available_page_file_kb 5588408
- system_cache_kb 977004
- kernel_paged_pool_kb 171908
- kernel_nonpaged_pool_kb 166596
- system_high_memory_signal_state 1
- system_low_memory_signal_state 0
- system_memory_state_desc 可用物理記憶體高
我們有一個名為 Idera 的工具，它能夠隨著時間的推移提供記憶體的視覺化表示。下面是超過 3 週的緩衝區記憶體大小。
我不知道下一張圖片有多有用，但這就是我對記憶體使用感到困惑的原因。
重新啟動後，黃色區域完全消失，只分配了 SQL 並使用了 SQL。我查看了許多視圖，但我無法確定是什麼在使用這些額外的記憶體。如果您有任何建議或需要更多資訊，請告訴我。
提前致謝！
伺服器上執行的服務如下。
此伺服器僅用於 SQL Server。現在查看任務管理器 sqlservr.exe 使用 30,264,240 K，其次是 explorer.exe，使用 64,704 K
dbcc 記憶體狀態：
Process/System Counts Value ---------------------------------------- -------------------- Available Physical Memory 959946752 Available Virtual Memory 140610025795584 Available Paging File 5776338944 Working Set 31031177216 Percent of Committed Memory in WS 100 Page Faults 3832630734 System physical memory high 1 System physical memory low 0 Process physical memory low 0 Process virtual memory low 0 (10 row(s) affected) Memory Manager KB ---------------------------------------- ----------- VM Reserved 96048512 VM Committed 5687320 Locked Pages Allocated 0 Large Pages Allocated 0 Emergency Memory 1024 Emergency Memory In Use 16 Target Committed 6149464 Current Committed 5687320 Pages Allocated 4418952 Pages Reserved 1016 Pages Free 59096 Pages In Use 2680688 Page Alloc Potential 8025776 NUMA Growth Phase 2 Last OOM Factor 0 Last OS Error 0 (16 row(s) affected) Memory node Id = 0 KB ---------------------------------------- ----------- VM Reserved 96007536 VM Committed 2857816 Locked Pages Allocated 0 Pages Allocated 2052424 Pages Free 39536 Target Committed 3074720 Current Committed 2857816 Foreign Committed 4180 Away Committed 0 Taken Away Committed 0 (10 row(s) affected) Memory node Id = 1 KB ---------------------------------------- ----------- VM Reserved 40912 VM Committed 2829484 Locked Pages Allocated 0 Pages Allocated 2366528 Pages Free 19560 Target Committed 3074720 Current Committed 2829488 Foreign Committed 0 Away Committed 0 Taken Away Committed 0 (10 row(s) affected) Memory node Id = 64 KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 20 Locked Pages Allocated 0 (3 row(s) affected) MEMORYCLERK_SQLGENERAL (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 21128 (6 row(s) affected) MEMORYCLERK_SQLGENERAL (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 1024 (6 row(s) affected) MEMORYCLERK_SQLGENERAL (Total) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 22152 (6 row(s) affected) MEMORYCLERK_SQLBUFFERPOOL (node 0) KB ---------------------------------------- ----------- VM Reserved 3171872 VM Committed 683300 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 1178824 (6 row(s) affected) MEMORYCLERK_SQLBUFFERPOOL (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 1773520 (6 row(s) affected) MEMORYCLERK_SQLBUFFERPOOL (Total) KB ---------------------------------------- ----------- VM Reserved 3171872 VM Committed 683300 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 2952344 (6 row(s) affected) MEMORYCLERK_SQLQUERYEXEC (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 256 (6 row(s) affected) MEMORYCLERK_SQLOPTIMIZER (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 2904 (6 row(s) affected) MEMORYCLERK_QUERYDISKSTORE (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 2616 (6 row(s) affected) MEMORYCLERK_SQLUTILITIES (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 205120 (6 row(s) affected) MEMORYCLERK_SQLUTILITIES (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 128520 (6 row(s) affected) MEMORYCLERK_SQLUTILITIES (Total) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 333640 (6 row(s) affected) MEMORYCLERK_SQLSTORENG (node 0) KB ---------------------------------------- ----------- VM Reserved 50048 VM Committed 50048 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 16024 (6 row(s) affected) MEMORYCLERK_SQLSTORENG (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 15184 (6 row(s) affected) MEMORYCLERK_SQLSTORENG (Total) KB ---------------------------------------- ----------- VM Reserved 50048 VM Committed 50048 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 31208 (6 row(s) affected) MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 2720 (6 row(s) affected) MEMORYCLERK_SQLCONNECTIONPOOL (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 1576 (6 row(s) affected) MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 4296 (6 row(s) affected) MEMORYCLERK_SQLCLR (node 0) KB ---------------------------------------- ----------- VM Reserved 6304320 VM Committed 57688 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 43312 (6 row(s) affected) MEMORYCLERK_SQLSERVICEBROKER (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 1288 (6 row(s) affected) MEMORYCLERK_SQLSERVICEBROKER (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 16 (6 row(s) affected) MEMORYCLERK_SQLSERVICEBROKER (Total) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 1304 (6 row(s) affected) MEMORYCLERK_SQLHTTP (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 8 (6 row(s) affected) MEMORYCLERK_SNI (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 144 (6 row(s) affected) MEMORYCLERK_SNI (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 136 (6 row(s) affected) MEMORYCLERK_SNI (node 64) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 16 (6 row(s) affected) MEMORYCLERK_SNI (Total) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 296 (6 row(s) affected) MEMORYCLERK_FULLTEXT (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 416 (6 row(s) affected) MEMORYCLERK_SQLXP (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 16 (6 row(s) affected) MEMORYCLERK_BHF (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 280 (6 row(s) affected) MEMORYCLERK_BHF (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 208 (6 row(s) affected) MEMORYCLERK_BHF (Total) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 488 (6 row(s) affected) MEMORYCLERK_SQLQERESERVATIONS (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 1024 (6 row(s) affected) MEMORYCLERK_XE_BUFFER (node 0) KB ---------------------------------------- ----------- VM Reserved 4416 VM Committed 4416 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 0 (6 row(s) affected) MEMORYCLERK_XE_BUFFER (node 1) KB ---------------------------------------- ----------- VM Reserved 2496 VM Committed 2496 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 0 (6 row(s) affected) MEMORYCLERK_XE_BUFFER (Total) KB ---------------------------------------- ----------- VM Reserved 6912 VM Committed 6912 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 0 (6 row(s) affected) MEMORYCLERK_TRACE_EVTNOTIF (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 128 (6 row(s) affected) MEMORYCLERK_TRACE_EVTNOTIF (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 128 (6 row(s) affected) MEMORYCLERK_TRACE_EVTNOTIF (Total) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 256 (6 row(s) affected) MEMORYCLERK_HOST (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 1008 (6 row(s) affected) MEMORYCLERK_SOSNODE (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 153912 (6 row(s) affected) MEMORYCLERK_SOSNODE (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 131352 (6 row(s) affected) MEMORYCLERK_SOSNODE (node 64) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 2496 (6 row(s) affected) MEMORYCLERK_SOSNODE (Total) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 287760 (6 row(s) affected) MEMORYCLERK_SOSOS (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 192 (6 row(s) affected) MEMORYCLERK_SOSMEMMANAGER (node 0) KB ---------------------------------------- ----------- VM Reserved 277648 VM Committed 277184 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 0 (6 row(s) affected) MEMORYCLERK_FULLTEXT_SHMEM (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 384 SM Committed 384 Pages Allocated 0 (6 row(s) affected) MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB ---------------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 352 (6 row(s) affected) MEMORYCLERK_FILETABLE (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 16 (6 row(s) affected) MEMORYCLERK_XE (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 3576 (6 row(s) affected) MEMORYCLERK_SQLLOGPOOL (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 4224 (6 row(s) affected) MEMORYCLERK_LWC (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 128 (6 row(s) affected)用於連結伺服器的驅動程序如下
程序工作集 31GB，這是目前在 RAM 中的程序送出的虛擬記憶體的一部分。所以 SQL 程序使用 31GB 的 RAM。
SQLOS VM Committed 只有5687MB，Locked Pages Allocated 為0。所以SQLOS 只能佔6GB 的記憶體使用。
所以這個過程中的某些東西正在使用 25GB 的記憶體，而不是 SQL Server。這裡的典型罪魁禍首是連結伺服器驅動程序，它在程序中分配記憶體，而 SQL Server 記憶體管理員和池不跟踪該記憶體。
您提到了與 Oracle 的連結伺服器。您使用的是什麼 OleDB 驅動程序和版本？使用任何其他連結伺服器驅動程序？
最終，您需要找出 SQL Server 程序中發生記憶體洩漏的原因並修復或消除它。在短期內，您可以定期反彈 SQL Server 程序。
消除在 SQL Server 中載入連結伺服器驅動程序的一個好方法是使用 SSIS。為了從遠端源讀取數據，SSIS 數據流目的地使您能夠在一個短暫的過程中隔離第 3 方數據訪問組件，並且仍然像連結伺服器一樣從它們進行查詢。