解决慢查询

Question

我们正在为我们的网站安装一个新的论坛(yaf)。其中一个存储过程非常慢-事实上，它总是在浏览器中超时。如果我在MSSMS中运行它，它需要将近10分钟才能完成。如果花了这么长时间，有没有办法找出这个查询的哪个部分？

查询：

DECLARE @BoardID int 
DECLARE @UserID int
DECLARE @CategoryID int = null
DECLARE @ParentID int = null

SET @BoardID = 1
SET @UserID = 2

    select 
    a.CategoryID, 
    Category        = a.Name, 
    ForumID         = b.ForumID,
    Forum           = b.Name, 
    Description,
    Topics          = [dbo].[yaf_forum_topics](b.ForumID),
    Posts           = [dbo].[yaf_forum_posts](b.ForumID),
    Subforums       = [dbo].[yaf_forum_subforums](b.ForumID, @UserID),
    LastPosted      = t.LastPosted,
    LastMessageID   = t.LastMessageID,
    LastUserID      = t.LastUserID,
    LastUser        = IsNull(t.LastUserName,(select Name from [dbo].[yaf_User] x where x.UserID=t.LastUserID)),
    LastTopicID     = t.TopicID,
    LastTopicName   = t.Topic,
    b.Flags,
    Viewing         = (select count(1) from [dbo].[yaf_Active] x JOIN [dbo].[yaf_User] usr ON x.UserID = usr.UserID where x.ForumID=b.ForumID AND usr.IsActiveExcluded = 0),
    b.RemoteURL,
    x.ReadAccess
from 
    [dbo].[yaf_Category] a
    join [dbo].[yaf_Forum] b on b.CategoryID=a.CategoryID
    join [dbo].[yaf_vaccess] x on x.ForumID=b.ForumID
    left outer join [dbo].[yaf_Topic] t ON t.TopicID = [dbo].[yaf_forum_lasttopic](b.ForumID,@UserID,b.LastTopicID,b.LastPosted)
where 
    a.BoardID = @BoardID and
    ((b.Flags & 2)=0 or x.ReadAccess<>0) and
    (@CategoryID is null or a.CategoryID=@CategoryID) and
    ((@ParentID is null and b.ParentID is null) or b.ParentID=@ParentID) and
    x.UserID = @UserID
order by
    a.SortOrder,
    b.SortOrder

IO统计信息：

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_Active'. Scan count 14, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_User'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_Topic'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_Category'. Scan count 0, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_Forum'. Scan count 0, logical reads 488, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_UserGroup'. Scan count 231, logical reads 693, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_ForumAccess'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_AccessMask'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_UserForum'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

客户端统计信息：

Client Execution Time   11:54:01        
Query Profile Statistics            
  Number of INSERT, DELETE and UPDATE statements    0       0.0000
  Rows affected by INSERT, DELETE, or UPDATE statements 0       0.0000
  Number of SELECT statements   8       8.0000
  Rows returned by SELECT statements    19      19.0000
  Number of transactions    0       0.0000
Network Statistics          
  Number of server roundtrips   3       3.0000
  TDS packets sent from client  3       3.0000
  TDS packets received from server  34      34.0000
  Bytes sent from client    3166        3166.0000
  Bytes received from server    128802      128802.0000
Time Statistics         
  Client processing time    156478      156478.0000
  Total execution time  572009      572009.0000
  Wait time on server replies   415531      415531.0000

Execution Plan

Answer 1

看一看Do you use Column=@Param OR @Param IS NULL in your WHERE clause? Don't, it doesn't perform，了解如何使用sp_executesql重写它，这样性能会更好。

这看起来像是一个函数调用吗？

 left outer join [dbo].[yaf_Topic] t ON t.TopicID = [dbo].[yaf_forum_lasttopic](b.ForumID,@UserID,b.LastTopicID,b.LastPosted)

也许你的函数把事情搞得一团糟，硬编码一些东西，而不是函数调用，看看会不会有很大的不同

Answer 2

有了这些IO统计信息，查询肯定是阻塞的。在SP运行时，检查您的procedure running窗口的session_id ( @@SPID)的sys.dm_exec_requests，然后参见blocking_session_id、wait_type、wait_time和wait_resource。这些应该会引导你找到阻止的原因。

Answer 3

我没看到你的行刑计划有什么不好的地方。

最有可能的情况是，另一个会话锁定了您的查询，或者您的服务器负载过重。