SQL Server 锁定/挂起问题

Question

我在 Windows Server 2008 R2 上使用 SQL Server 2008，全部加速。

我偶尔会遇到 SQL Server 挂起的问题，并且我们的实时服务器上的 CPU 使用率为 100%。发生这种情况时，SQL Sever 上的所有等待时间似乎都给了 SOS_SCHEDULER_YIELD。

这是导致挂起的存储过程。我添加了“WITH (NOLOCK)”，试图解决似乎是锁定的问题。

ALTER PROCEDURE [dbo].[MostPopularRead]
AS
BEGIN
SET NOCOUNT ON;

SELECT 
    c.ForeignId , ct.ContentSource as ContentSource
    , sum(ch.HitCount * hw.Weight) as Popularity
    , (sum(ch.HitCount * hw.Weight) * 100) / @Total as Percent
    , @Total as TotalHits
from 
    ContentHit ch WITH (NOLOCK)
    join [Content] c WITH (NOLOCK) on ch.ContentId = c.ContentId
    join HitWeight hw WITH (NOLOCK) on ch.HitWeightId = hw.HitWeightId
    join ContentType ct WITH (NOLOCK) on c.ContentTypeId = ct.ContentTypeId
where 
    ch.CreatedDate between @Then and @Now
group by
    c.ForeignId , ct.ContentSource
order by
    sum(ch.HitCount * hw.HitWeightMultiplier) desc
END

存储的过程从表“ContentHit”中读取，该表跟踪网站上的内容何时被单击（它被频繁点击 - 每分钟 4 到 20 次点击）。所以很明显这张表就是问题的根源。有一个存储过程被调用来将命中轨道添加到 ContentHit 表中，它非常简单，它只是根据传入的参数构建一个字符串，其中涉及从一些查找表中进行一些选择，然后是主要插入

BEGIN TRAN
insert into [ContentHit] 
    (ContentId, HitCount, HitWeightId, ContentHitComment)
values
    (@ContentId, isnull(@HitCount,1), isnull(@HitWeightId,1), @ContentHitComment)
COMMIT TRAN

： ContentHit 表在其 ID 列上有一个聚集索引，并且我在 CreatedDate 上添加了另一个索引，因为该索引在选择中使用。

当我分析问题时，我看到存储过程执行了整整 30 秒，然后发生 SQL 超时异常。如果它有所不同的话，使用它的 Web 应用程序是 ASP.NET，并且我使用 Subsonic (3) 来执行这些存储过程。

有人可以告诉我如何最好地解决这个问题吗？我不关心读取脏数据...

编辑： MostPopularRead 存储过程的调用频率非常低 - 它在站点的主页上调用，但结果会缓存一天。我看到的事件模式是当我清除缓存时，主站点收到多个请求，并且它们都到达存储过程，因为它尚未被缓存。然后SQL Server达到最大，只能通过重新启动SQL Server进程来解决。当我这样做时，通常 proc 会正常执行（大约 200 毫秒）并将数据放回到缓存中。

编辑2： 我检查了执行计划，查询看起来很合理。正如我之前所说，当它运行时，只需要大约 200 毫秒即可执行。我已将 MAXDOP 1 添加到 select 语句中，以强制它仅使用一个 CPU 核心，但我仍然看到该问题。当我查看等待时间时，我发现 XE_DISPATCHER_WAIT、ONDEMAND_TASK_QUEUE、BROKER_TRANSMITTER、KSOURCE_WAKEUP 和 BROKER_EVENTHANDLER 占用了大量等待时间。

编辑3： 我之前认为这与我们的 ORM Subsonic 有关，但切换到 ADO.NET 后，错误仍然存​​在。

Answer 1

删除 NOLOCK 提示。

在 SSMS 中打开查询，运行 SET STATISTICSIO ON 并在过程中运行查询。让它完成并在此处发布 IO 统计消息。然后发布表定义及其上定义的所有索引。然后有人将能够回复您所需的正确索引。

与所有 SQL 性能问题一样，如果没有完整的模式定义，查询文本在很大程度上是无关紧要的。

估计覆盖索引为：

create index ContentHitCreatedDate 
   on ContentHit (CreatedDate) 
   include (HitCount, ContentId,  HitWeightId);

更新

XE_DISPATCHER_WAIT、ONDEMAND_TASK_QUEUE、BROKER_TRANSMITTER、KSOURCE_WAKEUP和 BROKER_EVENTHANDLER：您可以安全地忽略所有这些等待。它们之所以出现，是因为它们代表已停放并等待分派 XEvent、Service Broker 或内部 SQL 线程池工作项的线程。由于他们大部分时间都花在停车和等待上，因此他们的等待时间不切实际。忽略他们。

Answer 2

如果您认为 ContentHit 是问题的根源，您可以添加 覆盖索引

CREATE INDEX IX_CONTENTHIT_CONTENTID_HITWEIGHTID_HITCOUNT 
  ON dbo.ContentHit (ContentID, HitWeightID, HitCount)

查看查询计划（如果您想确定查询中的瓶颈）。

Answer 3

默认情况下，sql server 使用所有核心/CPU 进行所有查询（最大 DoP 设置>高级属性，DoP= 并行度），即使只有一个核心实际上正在等待某些 I/O，这也可能导致 100% CPU。
如果你搜索网络或这个网站，你会发现资源解释得比我更好（比如监控你的 I/o，尽管你看到了 CPU 限制的问题）。
在一台服务器上，我们无法通过锁定所有资源 (CPU) 的错误查询来更改应用程序，但通过将 DoP 设置为核心数量的一半，我们设法避免服务器“停止”。在我们的例子中，并行性较低对查询的影响可以忽略不计。

--
多姆

Answer 4

感谢所有发帖者，我得到了一些很棒的 SQL Server 性能调整技巧。

最终我们用完了时间来解决这个谜团——我们找到了一种更有效的方法来收集这些信息并将其缓存在数据库中，所以这为我们解决了问题。

Answer 5

问题可能是并发性，而不是锁定。 SOS_SCHEDULER_YIELD 当任务自愿让出调度程序以供其他任务执行时发生。在此等待期间，任务正在等待其量程被更新。

[MostPopularRead] SP 多久调用一次以及执行需要多长时间？
查询中的聚合可能相当消耗 CPU 资源，尤其是在存在大量数据和/或无效索引的情况下。因此，您最终可能会面临较高的 CPU 压力 - 基本上，对 CPU 时间的需求过高。

我会考虑以下内容：

1. 检查 CPU 100% 繁忙时正在执行哪些其他查询？查看 sys.dm_os_waiting_tasks、sys.dm_os_tasks、sys.dm_exec_requests。

2. 查看[MostPopularRead]的查询计划，尝试优化查询。很多时候，无效的查询是性能问题的根本原因，而查询优化比其他性能改进技术要简单得多。

3. 如果查询计划是并行的，并且查询经常被多个客户端同时调用，则使用 MAXDOP=1 提示强制使用单线程计划可能会有所帮助（并行计划的大量使用通常由 SOS_SCHEDULER_YIELD 和 CXPACKET 等待指示）。< /p>

另外，请查看本文： 通过等待统计信息进行性能调整。它很好地总结了不同的等待类型及其对性能的影响。

PS 在查询之前使用 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 比向每个表添加 (nolock) 更容易。