许多并发的、长时间运行的查询对SQL Server性能的影响

Question

我想知道同时执行多个长时间运行的查询会对SQL Server按时为每个查询提供服务的能力产生何种影响。

[编辑]

我的意图不是含糊不清，更多是假设性的。我们假设这些查询是带有某种谓词的select语句，针对具有数百万行的表进行操作。

- Daniel

---

我在想什么时候会中彩票呢。这一切都有点模糊，你不觉得吗？ - John Sansom

这个问题不够具体，无法回答，需要猜测提问者的意思。对于上述问题的答案显然是“它会对其及时处理每个查询的能力产生负面影响”，但也许提问者还有其他意思... - Rich

我们所讨论的工作负载是什么类型的？是典型的 OLTP 混合工作负载还是重度读取活动，或者是数据分析？无论哪种方式，如果您能提供更多详细信息，我们都可以给出具体指针。 - John Sansom

我认为这是一个非常合理和清晰的问题。它涉及到性能如何随着同时查询数量的增加而扩展。 - redcalx

@the-locster：没有更多的信息，你不能这么说。我们不是在谈论在桌面上运行客户端代码线程，而是在数据库引擎内部的声明性语言语句。 - gbn

Answer 1

CPU

每个请求到达服务器时（即每个“批次”），都将与一个“任务”相关联，参见sys.dm_os_tasks。任务在“调度程序”上排队，粗略地说就是一个CPU核心，参见sys.dm_os_schedulers。每个调度程序都有几个“工作线程”（即线程或纤程，请参见sys.dm_os_workers），空闲的工作线程将从调度程序的队列中接收下一个任务，并“运行”，执行它直到任务完成（即请求已完成）。这种调度机制适用于SQL中的所有内容，包括系统任务、CLR运行代码等等。

可创建的任务数量受可用内存限制。请求（“批次”）与任务不是一对一的关系，因为有些请求一旦启动就会安排执行更多的任务，例如并行查询。系统中的工作线程数是动态的，但受“最大工作线程”配置设置的限制。如果工作线程达到上限，则新的计划任务将在调度程序中排队等待，但直到有空闲工作线程（完成任务）并变为可用之前，它们都不会被执行。当达到这种情况时，称为“工作线程饥饿”，会导致服务器无响应，因为新客户端登录握手需要执行登录任务（服务器似乎拒绝连接），而现有客户端的新请求将排队等待在等待任务后面（服务器需要很长时间才能响应微不足道的请求）。

因此，如果您有大量并行的长时间运行的查询，您将消耗大量的工作线程来执行许多长时间运行的任务。这会减少空闲工作线程池的大小，从而导致可用于服务其他短任务的工作线程较少（例如 OLTP 请求、登录握手等）。服务器似乎无响应，因为任务在调度程序队列中堆积（可以在sys.dm_os_schedulers DMV work_queue_count列中看到）。在极端情况下，您可以有效地使系统缺乏工作线程，使服务器完全无响应，直到某些工作线程空闲为止。

内存

包含并行操作的查询计划通常与大型索引（大型表）的全表扫描相关。扫描索引是通过遍历其叶子页来完成的，而读取大表中的所有叶子页意味着在查询执行期间所有这些页面都必须存在于内存中的某个时候。这反过来会产生对缓冲池中空闲页面的需求，以容纳已扫描的页面。对空闲页面的需求会产生内存压力，导致缓存被通知开始驱逐旧条目，并删除缓冲池中旧的访问数据页面。可以在 sys.dm_os_memory_cache_clock_hands 中观察到缓存通知。可以通过检查良好的 Page Life Expectancy 性能计数器来控制数据页面的清除。

驱逐缓存条目的效果是，下一次需要该条目（编译计划、权限令牌或其他内容）时，它必须从头开始创建，导致消耗更多的 CPU、内存和 IO，即使在长时间运行的查询结束后也可能出现该效果。

现在可能情况是，您的系统安装了如此庞大的RAM，以至于扫描几个大表没有任何影响，您的RAM可以容纳整个数据库并有余地。在这种情况下，就没有问题了。但大多数情况并非如此。

IO

这与上面提到的内存有关。所有为了满足索引扫描而读取的页面都必须被传输到内存中，这意味着长时间运行的查询会消耗（潜在的大量）IO带宽的一部分。此外，从缓冲池中驱逐的所有脏数据页都必须写入磁盘，导致更多的IO。而被驱逐的干净页面很可能在未来某个时候需要重新使用，因此需要更多的IO。

如果扫描生成的IO超过系统的带宽，则IO操作开始在磁盘控制器上排队。这可以通过物理磁盘/平均队列长度性能计数器轻松检查。

争用

最后，最大的问题是锁竞争。如前所述，并行查询几乎总是意味着表扫描。而表扫描会对访问的每一行都进行共享锁定。虽然在正常操作模式下它们确实会在读取记录后释放锁定，但你仍然可以保证你将在表中的每一行上请求S锁定。这几乎可以保证这些扫描将命中由更新锁定X的行。当这种情况发生时，扫描必须停止并等待X锁定被释放，这发生在更新事务最终提交时。结果是即使在表上进行适度的OLTP活动也会阻塞长时间运行的查询。理想情况下，这就是发生的所有事情，结果只是性能差。但是如果长时间运行的查询执行任何花哨的操作，例如获取页面锁定而不是行锁定，则情况可能会迅速变得丑陋。由于这些扫描从索引端到端遍历，并且保证与更新发生冲突，因此这些查询获取的更高粒度锁不再仅与更新锁冲突，而实际上会导致死锁。解释这是如何发生的已超出了本回复的范围。

为了消除争用，当查询合法地进行完整扫描时，最好的选择是使用魔术快照：要么是为报告创建的数据库快照，要么是使用快照隔离级别。请注意，有些人可能会建议使用脏读取，但我还没有找到实际上可以接受的情况。

- Remus Rusanu

Answer 2

我会使用执行计划来优化查询。此外，您需要注意，如果查询时间较长，它们可能会锁定行或表。要回答这个问题，您需要考虑您的SQL服务器可以处理多少内存和CPU功率。在开发环境中进行测试将为您提供关于发生什么情况的良好读数，查询时间越长，占用的资源越多，它们可能成为整个系统的瓶颈。

- Chris Klepeis

Answer 3

很难说。

• 大规模并行查询？
• 成千上万的小查询？
• OLTP还是数据仓库？
• CPU、IO还是内存受限？
• 服务器硬件和设置？MAXDOP、RAID等
• 相同的数据集？（在缓冲池中或大量内存数据翻转）

我们有1亿行的表，子秒级聚合查询在工作时间内多次运行，以及10,000行的表查询需要20秒，但只在凌晨4点运行一次。

- gbn

Answer 4

显然，运行的查询越多，性能就会越慢。

程度取决于数据和查询类型（更新/删除/插入？）。

锁定表的查询可能特别有问题；在适当的情况下使用nolock可以提高性能。

- wefwfwefwe

Answer 5

性能通常会线性恶化，除非您开始遇到重大的I/O问题。

然而，证明在于逐个和并行测试查询，并监视SQL Server统计信息。

- polyglot

---

1个查询和2个查询之间的性能比较通常会呈现非线性关系，也就是说，如果这两个查询都需要访问缓慢的外部存储器，那么执行时间将远远超过原始查询的2倍。在那一点上，磁盘“交换”将会显著地降低这两个查询的速度。从那时起，性能下降可能是线性的。 - redcalx

我从多次计时的经验来说，我们的数据库中最为 I/O 密集的一个查询需要约 2 分钟。如果以并行方式运行，则大约需要 4 分钟。请注意，我在我的陈述中有所限定：“除非您开始遇到重大的 I/O 问题。”我不同意你的观点，只有在出现显著分页问题的情况下，才会需要超过两倍的时间。如果数据库具备足够的内存资源，这种情况可能不会发生。使用 64 位 8GB RAM 服务器，您可能需要非常努力地才能引发分页问题。这取决于您的应用程序。 - polyglot