如何从性能的角度更好地使用 CLR 函数（在每个数据库中重复或具有通用功能）？

这似乎是这个问题的重复：

为其他数据库中的内部存储过程设置一个中央 CLR 存储过程/函数存储库以供使用？

但是，我认为这两个答案中的任何一个都不够充分，因为它们没有提到这个问题的一些更重要的方面。

通常，对于 SQLCLR 对象来说，哪个位置更好，这里没有明显的选择，因为使用 SQLCLR 代码所做的事情可能会施加约束。有一些用途需要将程序集放在每个单独的数据库中，还有一种用途需要将程序集放在集中式数据库中。这完全取决于代码在做什么的几个不同方面。因此，我们需要看看这些方面是什么，以确定是否有选择开始，如果有，利弊是什么。

SQLCLR 特定的功能方面

• 用户定义类型（UDTs）： UDTs不能跨数据库引用；它们不能用 3 部分名称（即 DatabaseName.SchemaName.UserDefinedTypeName）声明。如果正在使用任何 UDT，则需要将程序集添加到将使用 UDT 的每个数据库中。但是，如果正在使用其他 SQLCLR 对象，那么假设可以选择将这些对象放置在集中式数据库或每个客户/应用程序数据库中，那么您始终可以将 UDT 放置在一个组件中，该组件被放置在每个客户中/application DB 和另一个包含函数/存储过程/用户定义的聚合/触发器的程序集。

• 安全：

  • 有多少个数据库受到影响： CLR 代码是否在执行任何需要将程序集标记PERMISSION_SET为EXTERNAL_ACCESSor 或的操作UNSAFE？如果是这样，您是否导入了任何在您控制之外签名且无法退出的 DLL？通常，这些是不受支持的 .NET Framework 库或第 3 方 DLL。当您无法控制需要标记为EXTERNAL_ACCESS或的程序集的签名时UNSAFE，您可能会被迫将包含程序集的数据库设置为TRUSTWORTHY ON。由于将数据库设置为TRUSTWORTHY ON是一种安全风险，最好尽量减少需要执行此操作的数据库数量，在这种情况下，将代码放入集中式数据库似乎是一种更好的方法。如果您已经有一个用于其他代码的中央数据库，并且希望真正最大限度地减少这种类型的安全风险，那么您可以为该代码创建第二个中央数据库。

    如果您确实可以控制 DLL 的签名，那么您绝对应该master基于 DLL 在数据库中创建证书或非对称密钥，然后基于该证书或非对称密钥创建登录，然后分配或对该登录名的EXTERNAL ACCESS ASSEMBLY权限UNSAFE ASSEMBLY。那里的那几个步骤（唯一要创建的是证书或密钥和登录名）将允许使用相同私钥签名的任何程序集设置为EXTERNAL_ACCESS或者UNSAFE（取决于授予登录名的权限），否不管它被加载到什么数据库中。如果您能够做到这一点，那么您可以将程序集设置为EXTERNAL_ACCESS或者UNSAFE在所有客户/应用程序数据库中，没有比将相同代码放入集中式数据库中更多的安全风险^**。

  • 不同客户端/应用程序需要不同的权限：如果出于任何原因，某些客户端/应用程序可能需要与PERMISSION_SET其他客户端/应用程序不同，则需要将程序集加载到每个客户端/应用程序数据库中。这将允许您使用一些数据库，SAFE而其他人正在使用EXTERNAL_ACCESS. 这超出了对象级权限所能做的事情。通过设置具有执行文件系统功能SAFE的代码的程序集，您可以保证代码无法工作，即使有人确实找到了绕过您的常规安全性的方法并且仍然可以EXECUTE使用 SQLCLR 存储过程。

• AppDomains：这方面涉及内存/资源利用和分离。就考虑因素而言，这可能是影响最大的领域，但也可能是最不了解的领域。因此，让我们首先看看 T-SQL 对象如何处理相同的中央数据库与每个客户端/应用程序数据库问题。

  T-SQL 函数和存储过程在执行时，将它们的执行计划存储在内存中的计划缓存中（嗯，不是内联 TVF）。仅从内存利用率的角度考虑，使用集中式数据库具有存储单个计划而不是每个客户端/应用程序数据库一个计划的优势，尤其是在有 100 个或更多数据库的情况下。但是，拥有一个缓存计划会引发一个问题，即它是否是后续执行的最佳计划。有可能，由于在如此多的客户端/应用程序数据库中执行它的方式可能存在广泛的变化，一个单一的计划对某些人来说是很好的，但对其他人来说却是相当可怕的。如果您不希望指定的性能受到影响WITH RECOMPILE，然后将其部署到每个客户端/应用程序数据库将允许更个性化的优化。简而言之：中央数据库用于计划缓存的内存较少，但性能可能更差；单独的数据库为计划缓存提供更多内存，但潜在的性能问题更少。

  对于 SQLCLR 对象，每种方法都存在相同的计划缓存优缺点。但是现在我们正在处理应用程序域，还有其他需要考虑的后果。应用程序域是 .NET 用于在其中运行代码的内存空间/沙箱。每个应用程序域都是它自己独立的沙箱。在 SQL Server 中，每个数据库和程序集所有者组合都会创建应用程序域。因此，同一用户拥有的同一数据库中的多个程序集将共享一个应用程序域，但另一个用户拥有的同一数据库中的程序集将具有不同的应用程序域，而其他数据库中的程序集将位于它们自己的应用程序域中。考虑到这一点：

  • 部署到单个客户端/应用程序数据库时，内存消耗会以更快的速度增加，因为正在使用的程序集被加载到应用程序域中（但它们在第一次使用之前不会加载）。应用程序域还包含所有变量、资源句柄等（直到这些东西被标记为垃圾收集和GC 认为月亮和星星完美对齐，并将其作为运行的标志）。因此，在一个数据库中使用一个为变量等保留一定数量内存的 AppDomain 的 2 MB 程序集与将同一个程序集加载到现在为 200 MB 的 100 个数据库中（从技术上讲，DLL 的某些部分）可能完全不同它在多个实例的内存中共享，但我不确定如何衡量）加上为变量等保留的空间量的 100 倍。

    一个相关的问题是，如果您使用正则表达式并使用 RegEx 选项Compiled将表达式编译为中间语言 (MSIL)。这确实加快了重复使用的表达式的速度，但是一旦编译了表达式，它就不能被垃圾收集，并且会留在 AppDomain 中，直到它重新启动。如果有一个常用的 RegEx 函数正在使用该Compiled选项，则如果将程序集加载到每个 DB 中，则用于存储它的内存将在每个 DB 中重复。在这种情况下，将此代码放在集中式数据库中可能是有意义的。

  • 使用集中式数据库时，资源限制可能是一个问题。根据您使用的类，您可能会在不知不觉中造成资源瓶颈。例如：

    • 当使用静态 RegEx 方法而不是实例方法时，您使用的正则表达式会被缓存。但默认缓存大小只有 15 个表达式。如果从大量客户端或应用程序中发送各种各样的表达式，那么表达式不会在缓存中停留很长时间。因此，如果这是考虑将程序集加载到每个数据库中的唯一原因，那么您可以只增加缓存大小。有关详细信息，请参阅RegEx.CacheSize的 MSDN 页面。

    • 同样，如果进行WebRequests，则可以对特定 URI 进行默认的最大活动连接数。而该默认值仅为 2。如果您向同一个 URI 发出更多请求（如果它是静态位置并且您为此代码使用集中式数据库，则非常容易做到），那么任何超过该最大值的请求都将简单地排队等待要关闭的当前连接（即阻塞）。因此，您必须将程序集加载到每个客户端/应用程序数据库中，或者增加每个 URI 的连接数限制。您可以通过设置ServicePointManager.DefaultConnectionLimit为当前应用程序域中的所有URI设置默认最大值（这可以在每次启动应用程序域时设置一次，例如在静态类构造函数中），或者可以通过创建 HttpWebRequest 然后设置其.ServicePoint.ConnectionLimit属性来基于每个 URI 进行设置（这需要每次实例化 WebRequest 时都会执行此操作，因为对象具有最大生存时间，并且一旦垃圾收集，ConnectionLimit 将恢复为该ServicePointManager.DefaultConnectionLimit值，如上所述，当创建新实例时）。

  • 如果您使用静态变量来缓存某些值（共享内存——很少见但仍有可能），那么您需要决定共享这些值的范围应该是什么。如果您希望共享包含在每个客户端/应用程序数据库中，则将程序集加载到每个客户端/应用程序数据库中。但是，如果您想在所有数据库中共享这些值，则将程序集放入一个共享的集中式数据库中。

一般功能方面

• 数据库访问：代码是否引用了任何特定于数据库的对象？请记住，使用进程内/Context Connection = true;连接执行 SQL 最初将在“当前”数据库设置为对象存在的数据库的情况下执行，而不一定是调用对象的位置。因此，在客户/应用程序数据库中运行并调用集中式数据库中的对象的代码将无法仅使用两部分名称来引用对象。但是，您仍然可以为此类代码使用集中式数据库，只要您有@DatabaseName(use: [SqlFacet(MaxSize = 128)] SqlString DatabaseName) 的输入参数，然后将其传递DB_NAME()给它。然后您可以DatabaseName.Value在 SQLCLR 代码中使用USE语句或连接到动态 SQL 以创建适当的完全限定的对象名称（即 3 部分名称）。

  如果您只引用基于系统的对象（即sys.databases），这可能不是决定因素，无论您在哪个数据库中都返回相同的行。如果您正在建立外部连接，这也不应该是一个问题，因为您已经是传递连接字符串的数据库名称，或者您将登录到默认数据库以进行连接登录。

• 排序规则差异：如果集中式数据库和客户端/应用程序数据库之间的排序规则相同，那么这不是决定这两种模型的决定因素。但是，如果您的系统要支持不同的排序规则，那么您需要了解您的代码正在做什么，因为它可能会受到排序规则的影响. 如果您发送的字符串将与其他字符串进行比较，那么即使没有产生错误，行为也可能不是预期的。用于比较局部变量和字符串文字的排序规则将是对象（即存储过程或函数）所在的默认排序规则。如果此排序规则与调用该对象时的“当前”数据库的排序规则不同（如果传入文字或变量），或者与传入的字段不同，那么该比较的方式可能会存在多种差异已经完成了。因此，如果支持各种排序规则，那么当代码部署到每个客户端/应用程序数据库时，基于字符串的操作可能会更加稳定/一致。

分心

以下是此问题以及此答案顶部链接的重复问题中给出的原因，用于选择一种方法或另一种方法，但我认为在决定哪种方法更适合时并不真正相关：

• 集中式数据库更容易支持：怎么会？代码在源代码管理中应该只存在一次。并且假设加载到每个客户端/应用程序数据库中的代码是相同的，那么在任何一种情况下，故障排除都应该大致相同。如果有的话，将 SQLCLR 代码放在一个公共数据库中，与客户端/应用程序数据库分开会增加一层跨数据库调用的复杂性，这在技术上可以被视为不集中此（或其他）代码的原因。
• 集中式数据库在部署时更容易：如果是这种情况，那么我会说需要修复他们的部署过程。如果您正在使用复制客户端/应用程序数据库以具有相同架构和不同数据的模型，那么在部署架构更改时您已经面临这个问题。部署 SQLCLR 也只是 DDL 语句。如果尝试从 DLL 加载程序集，部署 SQLCLR 代码可能会更加困难，但没有理由这样做。只需确保有一个独立的 SQL 脚本，它执行 aCREATE ASSEMBLY或ALTER ASSEMBLY使用十六进制字节（即FROM 0x4D5F000002C...）。
• 单个客户端/应用程序数据库更适合备份/恢复：这里的论点是，如果出现问题并且您需要恢复，那么恢复客户端/应用程序数据库中包含的所有内容是最容易的。我想说，如果您需要进行恢复，那么恢复 2 个数据库（客户端/应用程序数据库和通用数据库）几乎是相同的工作，恢复 101 个数据库（100 个客户端/应用程序数据库和 1 个通用数据库）仍然很漂亮几乎相同。从可靠性的角度来看，您的备份过程是可靠的并且可以信任以正确备份客户端/应用程序数据库和公共数据库，或者您需要修复您的备份过程;-)。
• 单个客户端/应用程序数据库更容易测试变体：在某种程度上，如果您需要在不更改所有引用的情况下测试一个版本的代码，这是正确的。虽然通常这应该通过在完全不同的环境中进行测试来完成。但是，例如，如果有一个客户要对某些代码更改进行 beta 测试，那么可以通过使用@AaronBertrand 在他对这个问题的回答中建议的模型非常容易地缓解这种情况（它主要关注T-SQL 对象，而不是专门的 SQLCLR 代码）：在中央数据库中创建函数还是在每个数据库中重复？. 该答案中讨论的模型是在大多数/所有情况下使用集中式数据库，并且在每个客户端/应用程序数据库中创建同义词，以便本地代码可以引用本地名称。然后，如果需要变体，可以将代码放置在特定的客户端/应用程序数据库中，然后删除同义词。在这种情况下，本地 T-SQL 代码仍将引用相同的本地名称并且不知道差异。就个人而言，我真的很喜欢这种方法，除了需要创建/删除同义词之外，我想不出它有任何特别的缺点，这似乎并不是为获得的灵活性付出高昂的代价。

尔格：对于这个问题中描述的特殊情况（即标量函数，没有外部资源，没有数据库对象访问，没有资源限制），看起来使用你的单一配置数据库就可以了。

^**如果您发现自己在想“但设置为 EXTERNAL_ACCESS 或 UNSAFE的程序集是安全风险，因为它们允许您执行此操作”：我并不是说如果您使用设置为 EXTERNAL_ACCESS 或 UNSAFE 的程序集根本没有风险基于证书/非对称密钥的方法。我要说的是，在这种配置中，无论存在什么风险，将程序集放在集中式数据库中与每个客户端/应用程序数据库中都没有什么不同。这是因为设置为 EXTERNAL_ACCESS 或 UNSAFE 的程序集可能导致的任何潜在安全问题都未本地化到这些程序集所在的数据库（与设置TRUSTWORTHY为不同ON）。任何安全问题都是系统范围的。但是，当将数据库设置为TRUSTWORTHY ON，那么您有额外的每个数据库的安全问题。