SQL、SQLCLR 對象和有效的記憶體使用率
我們最近將我們的 ERP 系統從 IBM Universe 轉換為 SQL Server。應用程序性能通常是可以容忍的，但偶爾會降級到糟糕。
我們在具有 32 Gb RAM 和 SQL Server 2012 的 VMWare 上的 Win Server 2012 上執行數據庫。SQL Max Memory 設置為 27Gb。db 伺服器僅託管此數據庫，不執行任何其他功能。總數據庫大小約為 110Gb。該應用程序有它自己的專用伺服器。
供應商已廣泛使用 CLR 來移植程式碼（超過 36,000 個標量函式）。我了解單個 CLR 在應用程序 OLTP 上下文中執行正常，但由於逐行而不是基於集合的操作而嘗試執行批量作業時，不能很好地擴展。很好……很酷……繼續前進。
我執行了Brent Ozar 的腳本，該腳本將高可用記憶體標識為需要查看的內容，以及每個查詢的大量執行計劃。供應商建議向伺服器添加更多記憶體，但這讓我很惱火，因為應用程序似乎沒有使用現在的記憶體。
我感興趣的是整體的 SQL 性能和行為。我看到一系列症狀表明某些事情不正確，但我無法確定。就像伺服器拒絕執行一樣。它決心走路。
非常粗略地說，在我看來，大約 10Gb 的記憶體被數據庫用於記憶體，大約 11GB 是免費的，大約 3.5Gb 用於計劃記憶體，其餘的我無法解釋。而且我有點不確定一些定義，例如免費、保留和被盜等。它們是否重複計算？
活動監視器顯示：
當我執行此查詢時：
-- what's happening inside my buffer pool? SELECT counter_name, instance_name, mb = cntr_value/1024.0 FROM sys.dm_os_performance_counters WHERE (counter_name = N'Cursor memory usage' and instance_name <> N'_Total') OR (instance_name = N'' AND counter_name IN (N'Connection Memory (KB)', N'Granted Workspace Memory (KB)', N'Lock Memory (KB)', N'Optimizer Memory (KB)', N'Stolen Server Memory (KB)', N'Log Pool Memory (KB)', N'Free Memory (KB)') ) ORDER BY mb DESC;我得到：
+--------------------------------+---------------------+----------+ | Counter_name | instance_name | mb | +--------------------------------+---------------------+----------+ | Free Memory (KB) | | 11,732 | | Stolen Server Memory (KB) | | 5,426 | | Lock Memory (KB) | | 59 | | Log Pool Memory (KB) | | 4 | | Optimizer Memory (KB) | | 2 | | Connection Memory (KB) | | 2 | | Cursor memory usage | TSQL Global Cursor | 1 | | Cursor memory usage | TSQL Local Cursor | 0 | | Cursor memory usage | API Cursor | - | | Granted Workspace Memory (KB) | | - | +--------------------------------+---------------------+----------+當我執行此查詢時：
-- which db's are using memory and how much. SELECT (CASE WHEN ([database_id] = 32767) THEN N'Resource Database' ELSE DB_NAME ([database_id]) END) AS [DatabaseName], COUNT (*) * 8 / 1024 AS [MBUsed], SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty] FROM sys.dm_os_buffer_descriptors GROUP BY [database_id];我得到：
+-------------------+----------+---------+ | DatabaseName | MBUsed | MBEmpty | +-------------------+----------+---------+ | ERP | 10,764 | 626 | | master | 2 | - | | model | - | - | | msdb | 11 | 3 | | Resource Database | 16 | 5 | | tempdb | 41 | 13 | +-------------------+----------+---------+當我執行此查詢時：
SELECT TOP (12) Type, Name, pages_kb, Virtual_Memory_reserved_kb, Virtual_Memory_committed_kb FROM sys.dm_os_memory_clerks ORDER BY pages_kb DESC;我得到：
+---------------------------+-----------------------+----------+----------------------------+-----------------------------+ | Type | Name | pages_kb | Virtual_Memory_reserved_kb | Virtual_Memory_committed_kb | +---------------------------+-----------------------+----------+----------------------------+-----------------------------+ | MEMORYCLERK_SQLBUFFERPOOL | Default | 11224968 | 12999744 | 640296 | | CACHESTORE_SQLCP | SQL Plans | 3519552 | 0 | 0 | | CACHESTORE_CLRPROC | ClrProcCache | 110232 | 0 | 0 | | CACHESTORE_OBJCP | Object Plans | 100776 | 0 | 0 | | USERSTORE_DBMETADATA | ERP_Live | 93856 | 0 | 0 | | USERSTORE_SCHEMAMGR | SchemaMgr Store | 87544 | 0 | 0 | | CACHESTORE_PHDR | Bound Trees | 73464 | 0 | 0 | | MEMORYCLERK_SOSNODE | SOS_Node | 62456 | 0 | 0 | | OBJECTSTORE_LOCK_MANAGER | Lock Manager : Node 0 | 60792 | 131072 | 131072 | | MEMORYCLERK_SQLCLR | Default | 40992 | 6327292 | 429408 | | MEMORYCLERK_SQLSTORENG | Default | 28472 | 9472 | 9472 | | MEMORYCLERK_SQLQUERYEXEC | Default | 20904 | 0 | 0 | +---------------------------+-----------------------+----------+----------------------------+-----------------------------+
- 看來我有 11Gb 的“可用記憶體”。這真的可以免費使用嗎？為什麼 SQL 不使用它？
- 在我看來，我的 ERP 系統只使用了大約 10Gb 或大約 1/3 的可用記憶體。（感覺不對。）如何鼓勵我的應用程序更有效地使用記憶體
- MEMORYCLERK_SQLCLR 已保留 6.03Gb 記憶體。這是 CLR 的正常行為嗎？他們什麼時候保留記憶體？它們何時被編譯/註冊/執行？他們曾經發布它嗎？這是在“空閒記憶體”中嗎？（由斯魯茨基回答）
- Re: 大量執行計劃會刷新記憶體有幫助嗎？
- 我可以使用任何功能來影響上述行為嗎？還是我只需要接受這就是應用程序的工作方式。
- 我如何解釋伺服器上實際持有或使用記憶體的內容。
其他人要求的查詢
這些：
SELECT type, SUM(pages_kb)/1024 AS [Memory utilized in MB], SUM(awe_allocated_kb)/1024 AS [Memory allocated though Windows API] FROM sys.dm_os_memory_clerks GROUP BY type ORDER BY [Memory utilized in MB] DESC; SELECT * FROM sys.dm_os_process_memory;返回：
+----------------------------------+-----------------------+-------------------------------------+ | type | Memory utilized in MB | Memory allocated though Windows API | +----------------------------------+-----------------------+-------------------------------------+ | MEMORYCLERK_SQLBUFFERPOOL | 4417 | 0 | | CACHESTORE_SQLCP | 3437 | 0 | | CACHESTORE_CLRPROC | 120 | 0 | | USERSTORE_DBMETADATA | 100 | 0 | | CACHESTORE_OBJCP | 99 | 0 | | USERSTORE_SCHEMAMGR | 76 | 0 | | CACHESTORE_PHDR | 72 | 0 | | MEMORYCLERK_SOSNODE | 64 | 0 | | OBJECTSTORE_LOCK_MANAGER | 59 | 0 | | MEMORYCLERK_SQLCLR | 38 | 0 | | MEMORYCLERK_SQLSTORENG | 26 | 0 | | MEMORYCLERK_SQLQUERYEXEC | 14 | 0 | | MEMORYCLERK_SQLGENERAL | 10 | 0 | | OBJECTSTORE_SNI_PACKET | 9 | 0 | | CACHESTORE_SYSTEMROWSET | 8 | 0 | | USERSTORE_TOKENPERM | 7 | 0 | | MEMORYCLERK_XE | 6 | 0 | | MEMORYCLERK_SQLLOGPOOL | 4 | 0 | | CACHESTORE_SEHOBTCOLUMNATTRIBUTE | 3 | 0 | | MEMORYCLERK_SQLOPTIMIZER | 2 | 0 | | MEMORYCLERK_SQLQERESERVATIONS | 2 | 0 | | MEMORYCLERK_SQLCONNECTIONPOOL | 1 | 0 | | OBJECTSTORE_LBSS | 1 | 0 | | CACHESTORE_STACKFRAMES | 0 | 0 | | MEMORYCLERK_SQLHTTP | 0 | 0 | +----------------------------------+-----------------------+-------------------------------------+ +---------------------------+---------------------------+----------------------------+--------------------------------+-----------------------------------+------------------------------------+------------------------------------+------------------+-------------------------------+---------------------------+-----------------------------+----------------------------+ | physical_memory_in_use_kb | large_page_allocations_kb | locked_page_allocations_kb | total_virtual_address_space_kb | virtual_address_space_reserved_kb | virtual_address_space_committed_kb | virtual_address_space_available_kb | page_fault_count | memory_utilization_percentage | available_commit_limit_kb | process_physical_memory_low | process_virtual_memory_low | +---------------------------+---------------------------+----------------------------+--------------------------------+-----------------------------------+------------------------------------+------------------------------------+------------------+-------------------------------+---------------------------+-----------------------------+----------------------------+ | 28571952 | 0 | 0 | 137438953344 | 77358808 | 28786620 | 137361594536 | 1014012259 | 99 | 3734268 | 0 | 0 | +---------------------------+---------------------------+----------------------------+--------------------------------+-----------------------------------+------------------------------------+------------------------------------+------------------+-------------------------------+---------------------------+-----------------------------+----------------------------+這：
SELECT COUNT(*) AS [NumCachedObjects], CONVERT(BIGINT, SUM(CONVERT(BIGINT, size_in_bytes)) / 1024.0) AS [CachedKBytes], ISNULL(cacheobjtype, '<-- Totally Total') AS [CacheObjType], ISNULL(objtype, '<-- TOTAL') AS [bytes] FROM sys.dm_exec_cached_plans GROUP BY cacheobjtype, objtype WITH ROLLUP;回報：
+------------------+--------------+-------------------+-----------+ | NumCachedObjects | CachedKBytes | CacheObjType | bytes | +------------------+--------------+-------------------+-----------+ | 3882 | 62112 | CLR Compiled Func | Proc | | 3882 | 62112 | CLR Compiled Func | <-- TOTAL | | 3 | 24 | CLR Compiled Proc | Proc | | 3 | 24 | CLR Compiled Proc | <-- TOTAL | | 50 | 4168 | Compiled Plan | Adhoc | | 26911 | 3416232 | Compiled Plan | Prepared | | 101 | 99584 | Compiled Plan | Proc | | 5 | 1656 | Compiled Plan | Trigger | | 27067 | 3521640 | Compiled Plan | <-- TOTAL | | 17 | 136 | Extended Proc | Proc | | 17 | 136 | Extended Proc | <-- TOTAL | | 16 | 536 | Parse Tree | Check | | 3 | 24 | Parse Tree | Default | | 313 | 20632 | Parse Tree | UsrTab | | 535 | 52520 | Parse Tree | View | | 867 | 73712 | Parse Tree | <-- TOTAL | | 31836 | 3657624 | <-- Totally Total | <-- TOTAL | +------------------+--------------+-------------------+-----------+這：
SELECT * FROM sys.dm_clr_appdomains;回報：
+--------------------+--------------+------------------------------------------+-------------------------+-------+---------+--------------------+-----------------+---------------+-----------+----------+---------------------+-------------------------+---------------------------+--------------------+ | appdomain_address | appdomain_id | appdomain_name | creation_time | db_id | user_id | state | strong_refcount | weak_refcount | cost | value | compatibility_level | total_processor_time_ms | total_allocated_memory_kb | survived_memory_kb | +--------------------+--------------+------------------------------------------+-------------------------+-------+---------+--------------------+-----------------+---------------+-----------+----------+---------------------+-------------------------+---------------------------+--------------------+ | 0x00000003DECEC200 | 16 | ERP .CLRExtensionUser[runtime].111 | 2016-07-13 10:51:23.370 | 5 | 5 | E_APPDOMAIN_SHARED | 1 | 3236 | 130810392 | 11534336 | 110 | 15 | 112020591 | 206 | +--------------------+--------------+------------------------------------------+-------------------------+-------+---------+--------------------+-----------------+---------------+-----------+----------+---------------------+-------------------------+---------------------------+--------------------+
在等待聽到我在對該問題的評論中發布的幾個問題的回复時，我至少會重申我的一個問題：“您目前的統計數據如何讓您懷疑 SQLCLR 的使用與性能問題有任何關係？”
從我看到的輸出來看，SQLCLR 佔用的記憶體很少。它有 110 MB 的物理記憶體用於
ClrProcCache. 好的。這僅略高於Object Plans3.36 GB 的佔用空間，是 3.36 GB 佔用空間的一小部分SQL Plans。是的，MEMORYCLERK_SQLCLR保留了大約 6.03 GB（不是 6.3 - 需要取值kb並應用value.0 / 1024 / 1024），但那是 a) 虛擬記憶體，而不是物理記憶體，並且 b) 僅低於緩衝池保留的 12.40 GB 虛擬記憶體的一半. 如果您滾動到該Virtual_Memory_committed_kb欄位，您將看到它MEMORYCLERK_SQLCLR僅在使用 419.34 MB 的虛擬記憶體。要檢查目前 SQLCLR 記憶體使用情況，您應該能夠執行：
SELECT * FROM sys.dm_clr_appdomains;並查看該欄位
survived_memory_kb（而不是total_allocated_memory_kb應該是累積分配的欄位，無論已釋放什麼）。嘗試回答您的三個問題：
- >
什麼是“持有”我 11.46 GB 的“空閒”記憶體？
為什麼你懷疑有什麼東西在“持有”呢？您已為 SQL Server 提供 27 GB 的物理 RAM 以供使用。它會在需要時使用它想要的東西。 2. >
在我看來，我的 ERP 系統只使用了大約 10Gb 或大約 1/3 的記憶體
我認為這個值是一個誤判。您說雖然伺服器有 32 GB 的物理 RAM，但您只為 SQL Server 分配了 27 GB。如果 10 GB 是實際總量，那將相當於大約 37%。但這不是實際總數。如果您查看
pages_kb最終查詢的欄位（反對sys.dm_os_memory_clerks），您需要將所有這些行相加，得出：15,424,008 kb。然後SELECT 15424008.0 / 1024 / 1024;我們在 27 GB 中使用了 14.71 GB 的 RAM。如果我們將提供給 SQL Server 的 27 GB RAM 減去 11.46 GB 的“可用”記憶體，則剩下 15.54 GB 應該“使用”。我們看到正在使用 14.71 GB，但這是基於執行TOP (12)查詢以獲取使用的記憶體量。我懷疑 0.83 GB 的差異隱藏在過濾的行中，因此刪除TOP (12)會給我們一個更接近 15.54 GB 的數字。在這種情況下，“使用”的記憶體量約為允許的物理 RAM 的 58%。 3. >MEMORYCLERK_SQLCLR 已保留 6.3Gb 記憶體。
不完全是。保留了 6.03 GB 的虛擬記憶體，而不是物理 RAM。此外，如上所述，這是保留的，而不是送出的虛擬記憶體。
這是 CLR 的正常行為嗎？
我不完全確定“正常”，但我確實看到 SQLCLR 更喜歡虛擬記憶體來儲存大型集合。
他們什麼時候保留記憶體？它們何時被編譯/註冊/執行？
您正在查看的應該是執行時記憶體。由於它是reserved，我猜在某個時間點，一項操作需要那麼多記憶體，因此預留的大小會增加以適應它。但是您的查詢還顯示，目前，這 6.03 GB 中只有 419.34 MB 被使用。
他們曾經發布它嗎？
至少在服務重新啟動時。但可能比這更早。我已經看到它在保留空間上保留了很長時間，但我不會花太多時間檢查它是否/何時被釋放。
如果您擔心垃圾收集沒有執行，或者沒有像您希望的那樣頻繁執行，您可以通過創建一個包含呼叫
GC該類的單個函式的簡單程序集來手動呼叫它。如果您將其載入到與其他程序集相同的數據庫中並確保它具有相同的所有者（即 ; 的AUTHORIZATION子句CREATE ASSEMBLY驗證SELECT * FROM sys.assemblies;並確保principal_id匹配），那麼它將使用相同的 AppDomain。這是在“空閒記憶體”中嗎？
否。“可用”記憶體是指SQL Server 通過“最大伺服器記憶體”允許使用的未使用物理RAM 量。保留的 6.03 GB虛擬記憶體位於交換文件/頁面文件中。 4. >
刷新記憶體有幫助嗎？
那麼，你打算怎麼做呢？如果您的意思是執行
DBCC FREESYSTEMCACHE('ALL');，那麼它應該解除安裝所有 AppDomain，儘管我不確定虛擬記憶體是否總是被釋放。我認為至少嘗試一次以查看實際效果沒有任何害處。不過，我當然不會養成這種習慣，因為系統會產生重新創建 AppDomain、載入程序集（或程序集）以及它儲存在ClrProcCache. 5. >我可以使用任何功能來影響上述行為嗎？
不是我知道的。而且我不認為您會希望 SQL Server 用完所有可用記憶體，因為這不會為查詢處理留下任何東西。
還是我只需要接受這就是應用程序的工作方式。
我認為你不需要也不應該接受緩慢就是這樣的事實。正如您所說，您已經用純 T-SQL 替換了幾個 SQLCLR UDF，並獲得了巨大的改進。這告訴我他們錯誤地和不恰當地使用 SQLCLR。如果他們找到或製作了生成這些 UDF 的工具（否則您如何獲得 36,000 個！？！），那麼它們是否“最佳”是值得懷疑的，即使是單獨使用。 6. >
我如何解釋伺服器上實際持有或使用記憶體的內容。
您可以使用任務管理器（“詳細資訊”選項卡）和資源監視器（“記憶體”選項卡）查看此內容。查找名為“Working Set”的列，它是使用的物理RAM 量，包括共享和非共享/私有。
如果由於在 Activity Monitor 中看到與 CLR 相關的等待類型而懷疑 SQLCLR 存在性能問題，請參閱我的以下 DBA.StackExchange 答案：SQL Server Management Studio (SSMS) 中的 Activity Monitor 中的 SQLCLR 等待類型是什麼？
關於問題中的以下評論：
我了解單個 CLR 在應用程序 OLTP 上下文中執行正常，但由於逐行而不是基於集合的操作而嘗試執行批量作業時，不能很好地擴展。
我不認為這是一個非常準確的理解;-)。UDF 的可伸縮性問題並不是 SQLCLR 獨有的。事實上，SQLCLR 標量函式可以做一些 T-SQL UDF 不能做的事情： 參與並行計劃（如果
IsDeterministic設置為true）；T-SQL UDF 強制執行串列計劃。儘管如此，對於可以在 T-SQL 中完成的大多數操作，內聯操作（不是在 UDF 或多語句 TVF 中抽象——T-SQL 內聯 TVF 很好）執行得最好。查看記憶體計劃的數量以及哪些類型正在使用什麼，我們可以看到大多數記憶體計劃是“準備好的”——其中將近 27,000 個——這表明它們很可能使用 ORM（例如實體框架，休眠/nHiberate 等）。這看起來確實是一個很高的數字，但是您對此無能為力，因為它是 ORM 的“野獸本性”（開發人員很少看到的一個重大缺點，但是嘿，您可以通過更多記憶體，對吧？）。我們還看到幾乎有 4000 個 SQLCLR UDF（我想知道這 36k UDF 中是否有“死程式碼”？）。這些是可以改進的地方（不幸的是，由供應商，而不是您），但並不表示記憶體有任何問題。
查看 的輸出，
sys.dm_clr_appdomains我們可以看到 AppDomain，我相信它是在獲得該輸出之前幾個小時左右創建的，它使用的 CPU 非常少，但已分配（以一種或另一種方式）累積總計112,020,591 字節 (106.83 MB)。但是，仍然只分配了 206 Kb，因此這些 SQLCLR 對像沒有佔用它們的記憶體。我有超過 60 個“AppDomain XXXXX 由於記憶體壓力被標記為解除安裝”。自 2016 年 1 月以來的消息。在這里和那裡出現奇怪的情況，並且在糟糕的一天連續 3-5 天。起初，這些發生在我們 ETL 提取執行的半夜。但最近這些都在一天中傳播。
6 個月內大約有 60 條“AppDomain Unloaded due to memory pressure”消息並不完美，但也遠非壞事。這是平均每 3 天 1 次。記憶體不足會導致這種情況每天發生很多次。在執行時查詢處理需要記憶體的繁重活動期間發生是有意義的。這讓我想起了 10 GB 的“空閒記憶體”僅在不執行 ETL（或其他活動增加的時間）時才“免費”的想法。
通過在 ETL 期間執行一些測試來查看有多少可用記憶體、分配給 cached_objects 的總 KB 以及每種類型有多少記憶體計劃，您可能會更清楚地了解這種情況。事實上，當性能“下降到可怕”時執行這些測試將給出可能記憶體不足的最佳指示。
目前，我沒有看到任何證據表明這是記憶體限制問題。更糟糕的是應用程序體系結構和功能的誤用（即 SQLCLR）。很可能是由於對 SQL Server 和 SQLCLR 沒有更好的理解，供應商做了幾件正常人都不會做的事情（例如 36k 標量函式！）。
供應商建議向伺服器添加更多記憶體，但這讓我很惱火，因為應用程序似乎沒有使用現在的記憶體。
這個建議也讓我很惱火，但出於不同的原因：他們要求你花錢做一個絕對的猜測。他們不知道更多的記憶是否會有所幫助。如果您甚至擁有目前 10 GB 可用記憶體的一半，並且“使用 CLR 需要幾分鐘的重複查詢體驗減少到幾秒鐘甚至亞秒級的響應時間，而沒有它們”，那麼記憶體怎麼會是問題呢？如果我在這裡不正確，也許他們可以為您提供查詢和/或證據來支持這與 RAM 相關的理論。但是你已經有了相當有力的反證，即重寫查詢需要“幾秒鐘甚至亞秒級的響應時間，沒有它們”。因此，也許供應商應該堅持他們的建議並為您購買 RAM。如果沒有幫助，您可以將其交還給他們。如果它確實有幫助，不應該需要它;-)。