在SQL Server上运行长时间(约10分钟)的存储过程有多危险(性能/资源)

Question

我正在研究在SQL Server上部署一些不占用资源但已知运行时间长的存储过程的想法。通过长跑，我的思维范围在10分钟以内。

它们运行时间长但不占用资源的原因是，它们通过C#SQLCLR代码访问外部系统，而正是这些外部系统的性能导致了性能问题，SQL Server将大部分时间花在等待外部系统的结果上。

我100%意识到，在SQL Server上可能"不建议"做这种类型的事情，像PowerShell这样的东西会更合适，但我希望将问题限制在这样做是否真的对SQL Server的整体性能/资源有害，如果是，危害有多大。

在我的场景中，总体服务器负载不会很高，可能最多有20个其他查询在运行，其中大多数是针对SQL表的正常查询——最多可能有3个用户在运行其中一个慢速查询。

因此，我的问题是：在SQL Server上提供此类查询是否存在任何真正的风险，是否存在与阻塞、连接等相关的问题，我应该注意这些问题？

编辑：

为了便于讨论，假设这是在一个4 CPU，8GB RAM的盒子上运行的。

这样做的动机(从业务角度来看)是，它有助于将SQL用作访问多个异构外部系统的公共抽象层，从而消除了对众多最终用户安装各种本地专有客户端软件的依赖，或对所涉及的各种系统的模糊调用语法的了解。

我真的希望人们不要因为对一个人是否"应该"这样做的哲学观点而投票结束这个问题。能够使用SQL Server实现这一点在经济上是有价值的。但是，如果它实际上在技术上是危险的，那么就不可能描述危险的一些细节吗？

编辑2应主持人的要求，我将提供一些额外的细节，以缩小我的要求范围。

场景：

我的公司环境中有20种不同的系统。这些系统中的每一个都有一个专门的API，该API需要安装软件来访问系统，以及正确语法的专门知识，以便查询系统内的数据。每个系统还有一个API，可以通过C#访问。

由于公司内的每个人都非常熟悉SQL server，因此为所有这些系统提供行业标准的API在经济上是有利的，它既消除了在每个客户端桌面上安装特殊软件的要求，也消除了最终用户学习复杂语法以查询每个不同系统的要求。在这种情况下，常见的API是SQL Server存储过程(实现为C#SQL CLR存储过程，但对调用方完全透明)。这些过程的接口(参数)很简单，并且有很好的文档记录，并且完全将用户与底层系统调用实现和语法的复杂性隔离开来。

对任何给定底层系统的实际调用的执行时间从小于1秒到长达10分钟不等，具体取决于被调用的特定过程和系统。在这个SQL过程中没有进行额外的昂贵处理，长运行时间只是等待远程系统完成查询并返回结果的过程。

典型的结果集大小为1到50000行，平均值可能在1000行左右。非常大的结果集的大小为5MB。

在任何给定的时间，该服务器上最多可能有25个同时执行活动查询，其中至少20个查询将执行"常规"TSQL存储过程，读取驻留在本地数据库中的普通SQL server表，而其中最多5个查询可能是访问外部系统的"特殊"C#SQL CLR查询。

所有查询都是读取的，没有写入，也没有正在执行的事务性多命令查询。

25个最大并发查询x 5 MB最大结果集大小=在任何给定时间内存中最多125 MB的"纯数据"，再加上任何附带的SQL Server"开销"。

一个典型的服务器将运行在一个4 CPU，8GB RAM的盒子运行SQL server 2012。如果我愿意的话，我有极大的余地来大幅增加这个盒子的功率——在这种情况下没有预算限制。

因此，考虑到这种场景，是否有人知道该实现不起作用的任何特定技术原因，或者对可能出现的一些技术限制的合理猜测？

顺便说一句。。。。我不知道是否有人听过Jeff和Joel在SO首次开发时做的StackaoverFlow播客，但这个问题有点让人想起Joel讲述的轶事，他在那里询问在SQL Server中做一些不寻常的事情(出于一个非常具体但有效的原因)，所有的答案基本上都是"你不应该这么做！"：)

那些可能感兴趣的人的参考资料：

https://blog.stackexchange.com/2009/01/podcast-38/

https://stackoverflow.fogbugz.com/default.asp?pg=pgWiki&command=视图&ixWikiPage=29025

SQL Server"；插入之后"；触发器不'；没有看到刚刚插入的行

同样，我当然意识到这个问题非常不寻常，但如果从纯粹的技术角度考虑，我认为它不应该有那么大的争议。

修改：请让我知道这个额外的细节是否足以最大限度地减少误解。我真的希望这个问题能够保持开放，因为它是善意的、合法的，并且涉及我认为非常有趣的SQL Server平台功能的边缘案例。

Answer 1

假设在所有情况下：

• 只能访问外部系统
• 访问是只读的

那么，从概念上讲，SQL Server的稳定性不应该存在特定的固有危险。然而，有几件事需要考虑和/或注意：

• C#API是指您添加为项目引用的DLL，对吗？此第三方DLL需要与您的DLL一起加载到SQL Server中。这就是事情变得棘手的地方。可以放心地假设DLL将通过网络与其他服务器通信，因此它至少需要标记为WITH PERMISSION_SET = EXTERNAL_ACCESS。

  • 理想情况下，您应该使用(希望)用于程序集的相同.pfx文件对第三方DLL进行签名。如果第三方DLL已经签名，我想你可以重新签名
  • 这个第三方DLL是否正确地清理了它的外部资源？开放外部资源而不妥善处理可能会导致问题。GC可能会清理孤立的外部文件和网络句柄，但我相信当类没有更多引用时会发生这种情况，并且我不确定包含SQLCLR方法的主类在卸载应用程序域之前将保持活动这一事实会对其产生什么影响
  • 如果API DLL正在进行HTTP连接，那么它将被限制为可以对特定URI进行多少连接。虽然这可能不是Windows应用程序或控制台应用程序的问题(因为它们都有自己的应用程序域)，但这是SQLCLR中的问题，因为只有一个应用程序域(针对特定程序集)在所有会话(即SPID)之间共享。默认限制是到给定URI的2个连接。如果有3个会话同时命中同一个API调用，则第3个会话将被阻止或获得异常。幸运的是，这可以通过ServicePointManager.DefaultConnectionLimit属性进行配置
  • API DLL是否使用任何标记有主机保护属性(HPA)的方法？当您通过CREATEASSEMBLY加载DLL时，或者如果您的代码调用了使用标有HPA的东西的API方法，您会发现这一点。如果是这样，则需要将API DLL和程序集标记为UNSAFE。这里的风险取决于特定的HPA。例如，通过TimeZoneInfo进行TimeZone转换可能会发生内存泄漏，因此它被标记为MayLeakOnAbort
  • API DLL是否将值存储在静态类变量中？当您在不将程序集设置为UNSAFE的情况下发出CREATE ASSEMBLY时，SQL Server会通知您DLL是否会执行此操作。虽然将程序集设置为UNSAFE将允许它工作，但现在您可能会发现，运行访问静态变量的代码的多个会话将经历"奇怪"的行为，并可能得到不正确的结果。同样的问题是，所有会话都共享一个应用程序域，因此需要UNSAFE才能使用静态变量。为了缓解这个问题，唯一可以做的事情就是在任何使用静态变量的API调用周围放置lock。但是，这并不是一个绝对的解决方案，因为静态变量可能会在不同的API调用中使用。存储的值对于一个特定会话来说可能有意义，但在会话之间是不正确的
  • API DLL是否引用任何不在支持中的库。NET Framework库列表？如果是这样，您还需要将它们加载到SQL Server中。但是，请记住，不能保证您能够加载任何不受支持的内容。NET Framework DLL到SQL Server中。SQL Server只允许使用纯程序集(即仅限托管代码)。无法加载混合的DLL(托管和非托管C++代码)。即使不受支持的Framework DLL是纯的并且现在可以加载，在的未来版本中也可能发生变化。NET。事实上，这种情况以前也发生过。风险范围是使用CLR 4.0版的任何框架更新(这是SQL Server 2012、2014和2016的绑定)
  • API DLL是纯的(仅限托管代码)还是混合的(托管和非托管C++)？如果第三方DLL是混合的，那么你无论如何都无法加载它

• 然而，如果您处理的是Web服务API，那么所关注的问题就会大大减少。

  • 您需要使用EXTERNAL_ACCESS
  • 您需要确保正确地Dispose()所有可以被偶极化的对象
  • 您需要设置ServicePointManager。DefaultConnectionLimit属性。同样，对于任何特定的方法，都有一个在所有会话中共享的应用程序域。DefaultConnectionLimit的默认值是2，这可能还不够，因为同一个URI(对于给定的API调用)将在多个会话中访问

• SQL Server运行状况的一个问题是，SQLCLR代码可能会锁定调度程序，从而在该进程完成之前无法执行任何其他操作。这是由于SQL Server使用了抢占式多任务处理，这需要线程放弃自己才能被搁置。如果您的SQL Server代码执行查询，那么这不是问题，但如果它只是在等待来自外部资源的响应，那么这种可能性是存在的。我个人还没有见过调度程序被SQLCLR进程锁定，但这仍然是可能的，所以尝试使用SQL Server"表现良好"是个好主意。如果可以对API代码进行async调用，则可以使用Timer每隔10或100毫秒(或类似的时间)调用Thread.Sleep(0);，直到外部进程返回。调用Thread.Sleep(0);是SQLCLR代码让SQLOS知道它(即SQLCLR进程)可以被搁置的方式。

  在SQLCLR中执行任何类型的异步工作都需要将程序集标记为UNSAFE。由于前面提到的各种其他原因，您的程序集很可能已经标记为UNSAFE。但即使不是，如果这是将它们标记为UNSAFE的唯一原因，那么这仍然是值得的，尤其是因为这是一个内部项目。

• 一个有助于缓解与UNSAFESQLCLR代码相关的稳定性问题的选项(至少对于主SQL Server进程)是将其隔离在一个单独的SQL Server实例中。例如，您可以运行一个单独的SQL Server express实例，该实例除了处理这些API调用之外什么都不做。只要指定允许SQL Server Express实例使用的最大服务器内存，内存泄漏就不会影响主SQL Server实例，只会影响Express实例。如果Scheduler被长时间运行的外部进程锁定，那么它再次只影响Express实例。

  然后，您只需设置一个从主实例到Express实例的链接服务器，这样用户就可以在主实例上工作，并连接到那里的任何表，等等

• 最后，从可用性的角度来看，考虑将这些SQLCLR对象设置为表值函数，而不是存储过程。整合他们的结果会容易得多。您不仅可以加入，而且如果有人不想要整个结果集，添加WHERE子句比将所有内容转储到临时表(通过INSERT...EXEC)只删除不需要的行更容易。

Answer 2

这本身不是问题。根据您的描述，执行将是可靠的。"可靠"是指尽管这不是最佳实践，但它会起作用，不会随机引发问题。

脑海中浮现出一些问题：

1. 如果SQL Server实例发生故障(故障转移、崩溃、重新启动…)，过程将中途中止。你肯定对此没意见。10分钟是一个很长的时间窗口，所以机会很高
2. 您可以同时执行的此类执行数量可能是有限的。我不知道具体的限制。如果N=3，则不适用
3. 长时间运行的事务会导致日志增长，并可能导致严重的阻塞

这些关注点都不只是因为它们的存在而破坏实例的稳定。这些都是完全正常的事情，只是被10分钟的持续时间放大了。

我不知道为什么有些评论如此激动。10分钟的查询在数据仓库中很常见，这些查询会使服务器承受100%的负载，这比10分钟的等待更糟糕。这里没问题。

如果你不同意，请留言说明原因。当您从SQL Server发出web服务调用时，请说明到底是什么导致出现问题。

Answer 3

为正确的工作使用正确的工具。在您的测试环境中，一切都会很好地工作，但一旦您离开该环境并转到生产环境，您就会被炒鱿鱼。

作为主要数据存储的数据库应该是高度可用的，而那些非资源密集型任务总是会根据并发连接的数量迅速增加。在设计系统时，请记住最终用户。