主页 / dba / 问题 / 143445
Accepted
Sir Swears-a-lot
Asked: 2016-07-11 15:05:10 +0800 CST

SQL、SQLCLR 对象和有效的内存利用率

  • 772

我们最近将我们的 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 |
+---------------------------+-----------------------+----------+----------------------------+-----------------------------+

  1. 看来我有 11Gb 的“可用内存”。这真的可以免费使用吗？为什么 SQL 不使用它？

  2. 在我看来，我的 ERP 系统只使用了大约 10Gb 或大约 1/3 的可用内存。（感觉不对。）如何鼓励我的应用程序更有效地使用内存

  3. MEMORYCLERK_SQLCLR 已保留 6.03Gb 内存。这是 CLR 的正常行为吗？他们什么时候保留内存？它们何时被编译/注册/执行？他们曾经发布它吗？这是在“空闲内存”中吗？（由斯鲁茨基回答）

  4. Re: 大量执行计划会刷新缓存有帮助吗？

  5. 我可以使用任何功能来影响上述行为吗？还是我只需要接受这就是应用程序的工作方式。

  6. 我如何解释服务器上实际持有或使用内存的内容。

其他人要求的查询

这些：

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 | 
+--------------------+--------------+------------------------------------------+-------------------------+-------+---------+--------------------+-----------------+---------------+-----------+----------+---------------------+-------------------------+---------------------------+--------------------+
sql-server sql-server-2012

1 个回答

  1. Best Answer

    在等待听到我在对该问题的评论中发布的几个问题的回复时，我至少会重申我的一个问题：“您当前的统计数据如何让您怀疑 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应该是累积分配的字段，无论已释放什么）。

    尝试回答您的三个问题：

    1. 什么是“持有”我 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。如果没有帮助，您可以将其交还给他们。如果它确实有帮助，不应该需要它;-)。

    • 8

相关问题