代码之家 › 专栏 › 技术社区 › Matt Roberts

SQL Server锁定/挂起问题

subsonic3 locking sql-server-2008 performance sql-server

Matt Roberts · 技术社区 · 15 年前

我偶尔会遇到SQL Server的问题,在我们的实时服务器上CPU使用率为100%。似乎发生这种情况时,SQL服务器上的所有等待时间都被赋予了SOS\u调度器\u 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

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

ContentHit表的ID列上有一个聚集索引,我在CreatedDate上添加了另一个索引,因为它在select中使用。

当我分析这个问题时,我看到存储过程执行了整整30秒,然后发生了SQL超时异常。如果有区别的话,使用它的web应用程序是ASP.NET,我使用亚音速(3)来执行这些存储过程。

编辑2: 我已经检查了执行计划,查询结果看起来很合理。正如我前面所说的,当它运行时,执行只需要大约200毫秒。我已经将maxdop1添加到select语句中,以强制它只使用一个CPU核,但是我仍然看到了这个问题。当我查看等待时间时,我看到XE\u DISPATCHER\u wait、ONDEMAND\u TASK\u QUEUE、BROKER\u TRANSMITTER、KSOURCE\u WAKEUP和BROKER\u EVENTHANDLER占用了大量的等待时间。

编辑3:

5 回复 | 直到 15 年前

Glorfindel Doug L. 4 年前

Remove the NOLOCK hint .

在SSMS中打开查询,运行 SET STATISTICSIO ON

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

估计的覆盖指数为:

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

更新

XE_DISPATCHER_WAIT , ONDEMAND_TASK_QUEUE BROKER_TRANSMITTER , KSOURCE_WAKEUP BROKER_EVENTHANDLER :您可以安全地忽略所有这些等待。它们之所以出现,是因为它们表示停驻并等待分派XEvents、servicebroker或内部SQL线程池工作项的线程。因为他们大部分时间都在停车等待,所以他们的等待时间不切实际。别理他们。

VladV 15 年前

当一个任务自愿为其他任务生成要执行的计划程序时发生。在此等待期间,任务正在等待其量被更新 .

查询中的聚合可能相当占用CPU,特别是在有大量数据和/或无效索引的情况下。所以,你可能会有很高的CPU压力-基本上,对CPU时间的要求太高了。

检查CPU 100%繁忙时执行的其他查询?查看sys.dm\u os\u waiting\u tasks、sys.dm\u os\u tasks、sys.dm\u exec\u requests。
查看[MostPopularRead]的查询计划,尝试优化查询。通常,无效查询是性能问题的根本原因,查询优化比其他性能改进技术简单得多。
如果查询计划是并行的,并且查询通常由多个客户端同时调用,那么强制使用MAXDOP=1提示的单线程计划可能会有所帮助(大量使用并行计划通常由SOS\u SCHEDULER\u YIELD和cxpack waits表示)。

Performance tuning with wait statistics

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

Lieven Keersmaekers 15 年前

如果你相信 ContentHit 要成为问题的根源,您可以添加 Covering Index

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

Query Plan 如果您想确定查询中的瓶颈。

Dom Ribaut 15 年前

在一台服务器上,我们无法使用锁定所有资源(CPU)的错误查询来更改应用程序,但是通过将DoP设置为核心数的一半,我们成功地避免了服务器“停止”。在我们的例子中,对查询不太并行的影响可以忽略不计。

Matt Roberts 14 年前

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