代码之家 › 专栏 › 技术社区 › SeanX

升级到Sql Server 2008。现在有一个查询运行得非常慢

sql-server

SeanX · 技术社区 · 16 年前

周末我从SQLServer2000升级到了2008。现在,一个查询运行速度非常慢(大约50行的查询时间超过30秒)。

SELECT     TOP 200 AccData.SurName + ', ' + AccData.FirstNames AS Name, 
    DATEDIFF(day, COALESCE (AccData.DateReceived, AccData.DateOpened,
    AccData.InjuryDate), 
    GETDATE()) AS Duration, AccData.M46No, Clients.ClientName, 
    AccData.HomePhone, AccData.WorkPhone, AccData.InjuryDate, 
    AccData.ClaimID, 
    luClaimStatus.Meaning AS Status, AccData.Claim, 
    vw_LastMedCert.Fitness, vw_LastMedCert.UntilDate
FROM         AccData INNER JOIN
    Clients ON AccData.ClientID = Clients.ID 
    INNER JOIN
        luClaimStatus ON AccData.ClaimStatus = luClaimStatus.ClaimStatus 
    LEFT OUTER JOIN
        vw_LastMedCert ON AccData.Claim = vw_LastMedCert.Claim
WHERE AccData.ClientID>1 and CaseManagerId = :CaseManagerID 
    and (DateClosed is null or  AccData.ClaimStatus ='R')
order by Surname, FirstNames

这个问题与LastMedCert有关

ALTER VIEW [dbo].[vw_LastMedCert] WITH SCHEMABINDING
AS
SELECT     Claim, ClaimId, ReferralID, FromDate, UntilDate, Fitness, DateSeen, 
    DateEntered, PeriodFor
FROM         dbo.Med_cert
WHERE     (ReferralID IN
                      (SELECT     MAX(ReferralID) AS MaxOfReferralID
                        FROM          dbo.Med_cert AS Med_cert_1
                        WHERE      (Fitness IS NOT NULL)
                        GROUP BY Claim))

有什么想法吗?我已经重建了索引并更新了统计数据

执行计划是:

|--Compute Scalar(DEFINE:([Expr1020]=datediff(day,[Expr1024],getdate())))
   |--Nested Loops(Left Outer Join, OUTER REFERENCES:([CmsDB].[dbo].[AccData].[Claim]))
        |--Nested Loops(Inner Join, OUTER REFERENCES:([CmsDB].[dbo].[AccData].[ClientID]))
        |    |--Nested Loops(Inner Join, OUTER REFERENCES:([CmsDB].[dbo].[AccData].[ClaimStatus]))
        |    |    |--Compute Scalar(DEFINE:([Expr1019]=((([CmsDB].[dbo].[AccData].[SurName]+', ')+[CmsDB].[dbo].[AccData].[FirstNames])+' ')+CASE WHEN [CmsDB].[dbo].[AccData].[MiddleNames] IS NOT NULL THEN [CmsDB].[dbo].[AccData].[MiddleNames] ELSE '' END, [Expr1024]=CASE WHEN [CmsDB].[dbo].[AccData].[DateReceived] IS NOT NULL THEN [CmsDB].[dbo].[AccData].[DateReceived] ELSE CASE WHEN [CmsDB].[dbo].[AccData].[DateOpened] IS NOT NULL THEN [CmsDB].[dbo].[AccData].[DateOpened] ELSE [CmsDB].[dbo].[AccData].[InjuryDate] END END))
        |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [CmsDB].[dbo].[AccData].[Claim], [CmsDB].[dbo].[AccData].[SurName], [CmsDB].[dbo].[AccData].[FirstNames], [Expr1027]) WITH ORDERED PREFETCH)
        |    |    |         |--Index Seek(OBJECT:([CmsDB].[dbo].[AccData].[IX_AccData_ByCaseManagerId]), SEEK:([CmsDB].[dbo].[AccData].[CaseManagerID]=(100346)) ORDERED FORWARD)
        |    |    |         |--Clustered Index Seek(OBJECT:([CmsDB].[dbo].[AccData].[byName]), SEEK:([CmsDB].[dbo].[AccData].[SurName]=[CmsDB].[dbo].[AccData].[SurName] AND [CmsDB].[dbo].[AccData].[FirstNames]=[CmsDB].[dbo].[AccData].[FirstNames] AND [CmsDB].[dbo].[AccData].[Claim]=[CmsDB].[dbo].[AccData].[Claim] AND [Uniq1002]=[Uniq1002]),  WHERE:([CmsDB].[dbo].[AccData].[ClientID]>(1) AND ([CmsDB].[dbo].[AccData].[DateClosed] IS NULL OR [CmsDB].[dbo].[AccData].[ClaimStatus]='R')) LOOKUP ORDERED FORWARD)
        |    |    |--Clustered Index Seek(OBJECT:([CmsDB].[dbo].[luClaimStatus].[PK_luClaimStatus_1__172]), SEEK:([CmsDB].[dbo].[luClaimStatus].[ClaimStatus]=[CmsDB].[dbo].[AccData].[ClaimStatus]) ORDERED FORWARD)
        |    |--Index Seek(OBJECT:([CmsDB].[dbo].[Clients].[PK_Clients_2__13]), SEEK:([CmsDB].[dbo].[Clients].[ID]=[CmsDB].[dbo].[AccData].[ClientID]),  WHERE:([CmsDB].[dbo].[Clients].[ID]>(1)) ORDERED FORWARD)
        |--Nested Loops(Inner Join, WHERE:([Expr1018]=[CmsDB].[dbo].[Med_cert].[ReferralID]))
             |--Clustered Index Seek(OBJECT:([CmsDB].[dbo].[Med_cert].[byClaim]), SEEK:([CmsDB].[dbo].[Med_cert].[Claim]=[CmsDB].[dbo].[AccData].[Claim]) ORDERED FORWARD)
             |--Table Spool
                  |--Stream Aggregate(GROUP BY:([CmsDB].[dbo].[Med_cert].[Claim]) DEFINE:([Expr1018]=MAX([CmsDB].[dbo].[Med_cert].[ReferralID])))
                       |--Clustered Index Scan(OBJECT:([CmsDB].[dbo].[Med_cert].[byClaim]),  WHERE:([CmsDB].[dbo].[Med_cert].[Fitness] IS NOT NULL) ORDERED FORWARD)

我通过重写初始查询解决了这个问题。它现在运行大约1秒钟,但我仍然想知道出了什么问题,这样如果它再次出现,我就可以修复它。

初始查询运行时间约为2分钟,具体取决于参数。按照验证计划和dm_db_missing_index_details建议添加索引,将运行时间减少到大约4秒。添加哈希提示将运行时间减少到2秒。

决定接受哪个答案是一个艰难的决定,大多数答案都提供了一些帮助。

4 回复 | 直到 16 年前

RickNZ 16 年前

查询只是第一次慢,还是每次都慢?如果是前者,则可能是缓存问题。

新系统是否使用与旧系统相同的磁盘子系统?如果不是,则问题可能与用于数据文件的磁盘的速度有关。

您是将DB从兼容模式90升级到100,还是将其保持在90?

您是否也从旧系统中带来了所有数据?如果不是,则统计数据可能不同,从而导致不同(且较慢)的查询计划。

您是否尝试过在SQL 2008中使用缺少的索引功能?

SELECT * FROM sys.dm_db_missing_index_details

Andomar 16 年前

actual execution plan 在两台机器上。图形版本可能是最有用的;您可以比较这些树,并检查2008服务器上哪个箭头真正大。

要在堆栈溢出时过帐,请以文本形式检索计划,如:

set showplan_text on
go
<your query>

编辑:执行计划提到聚集索引扫描:

|--Clustered Index Scan(OBJECT:([CmsDB].[dbo].[Med_cert].[byClaim]),  
WHERE:([CmsDB].[dbo].[Med_cert].[Fitness] IS NOT NULL) ORDERED FORWARD)

我会带上索引试试这个 med_cert(fitness,claim,ReferralID) messages SSMS中的标签;它有时包括索引建议。

Disillusioned 16 年前

没有什么特别突出的问题。虽然我确实注意到了一些我要提到的小问题。

同一查询的查询计划是否相同?我在计划中看到对AccData.MiddleNames的引用,但在查询中没有看到。

        |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [CmsDB].[dbo].[AccData].[Claim], [CmsDB].[dbo].[AccData].[SurName], [CmsDB].[dbo].[AccData].[FirstNames], [Expr1027]) WITH ORDERED PREFETCH)
        |    |    |         |--Index Seek(OBJECT:([CmsDB].[dbo].[AccData].[IX_AccData_ByCaseManagerId]), SEEK:([CmsDB].[dbo].[AccData].[CaseManagerID]=(100346)) ORDERED FORWARD)
        |    |    |         |--Clustered Index Seek(OBJECT:([CmsDB].[dbo].[AccData].[byName]), SEEK:([CmsDB].[dbo].[AccData].[SurName]=[CmsDB].[dbo].[AccData].[SurName] AND [CmsDB].[dbo].[AccData].[FirstNames]=[CmsDB].[dbo].[AccData].[FirstNames] AND [CmsDB].[dbo].[AccData].[Claim]=[CmsDB].[dbo].[AccData].[Claim] AND [Uniq1002]=[Uniq1002]),  WHERE:([CmsDB].[dbo].[AccData].[ClientID]>(1) AND ([CmsDB].[dbo].[AccData].[DateClosed] IS NULL OR [CmsDB].[dbo].[AccData].[ClaimStatus]='R')) LOOKUP ORDERED FORWARD)

我注意到 WHERE ClientID>1

作为提示,下次遇到此问题时,请查看图形查询计划,并查看哪些节点非常昂贵。它将指导您在何处检查索引(用于创建或重建)。

C. Ross trotttrotttrott 16 年前

从2000年到2005年,我遇到了一个非常类似的问题。我们有一个100万行的表视图(带有自连接等),查询运行了三个多小时(我们从不让它结束,所以我们不知道它是否会返回)。我的问题似乎与表中“嵌套循环”的数量直接相关。我在你的执行计划中看到了不少:

Nested Loops(Left Outer Join, OUTER REFERENCES:([CmsDB].[dbo].[AccData].[Claim]))
    |--Nested Loops(Inner Join, OUTER REFERENCES:([CmsDB].[dbo].[AccData].[ClientID]))
    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([CmsDB].[dbo].[AccData].[ClaimStatus]))

我曾经 Query Hint : Hash 在我所有有问题的连接上,它将查询时间减少到了更易于管理的30-45分钟。