Upgraded to Sql Server 2008. Now one query is running really slow

Question

I upgraded from sql server 2000 to 2008 over the weekend. Now one query is running really slow (> 30sec for about 50 rows).

The query is:

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

The problem has something to do with 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))

Any ideas? I have rebuilt the indexes and updated the statistics

The execution plan is:

|--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)

I have resolved the solution by rewriting the initial query. It now runs in about 1 seconds, but I still want to know what went wrong so I can fix it if it appears again.

Summary The initial query running time was about 2 minutes depending on parameters. Adding the indexes as suggested by the exeqution plan and dm_db_missing_index_details reduced running time to about 4 seconds. Adding the Hash hint reduced running time down to 2 seconds.

It was a tough call deciding which answer to accept, most answers provided some assist.

Answer 1

Compare the actual execution plan on both machines. The graphical version is probably most useful; you can compare the trees, and check which arrow is really big on the 2008 server.

For posting on Stack Overflow, retrieve the plan in text form like:

set showplan_text on
go
<your query>

EDIT: The execution plan mentions a clustered index scan:

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

I'd try this with an index on med_cert(fitness,claim,ReferralID). Even better, you could run this in SQL Profiler and follow the index suggestion(s) that it generates. Also check the messages tab in SSMS; it sometimes includes index suggestions.

Answer 2

Is the query only slow the first time, or every time? If the former, it might be a caching issue.

Is the new system using the same disk subsystem as the old one? If not, the issue might be related to the speed of the disks you're using for your data file(s).

Did you upgrade the DB from compatibility mode 90 to 100, or did you keep it at 90?

Did you bring over all of the data too from your old system? If not, perhaps the statistics are different, resulting in a different (and slower) query plan.

Have you tried using the missing index feature in SQL 2008?

SELECT * FROM sys.dm_db_missing_index_details

Answer 3

Nothing in particular stands out as problematic. Though I do note a few minor issues I'll mention.

Was the query plan for the same query? I see reference to AccData.MiddleNames in the Plan, but not in the Query.

I do have a few observations...

The plan indicates that your clustered index on AccData is SurName, FirstName, Claim. This is not ideal; you'd prefer either the PK or data that groups nicely for your clustered index. If you don't use the PK, you have to do 'bookmark lookups' as illustrated below to obtain the full record via the clustered index. If the optimiser sees/predicts this happening too much, it may prefer to do a table-scan and possibly sort the data instead.

        |    |    |    |--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)

I noted WHERE ClientID>1 in your query; this seems strange a perhaps redundant. It more likely just complicates the query optimiser's job by forcing it to consider something that is in fact redundant.

The query plan shows all joins using 'nested loops'. This is usually okay, provided the volume of data at each join is not too much. (You can check this on your graphical plan). Basically, the plan seems to be hitting appropriate indexes on all joins. It's tricky to be sure not seeing exactly what those indexes are, and not seeing the volumes of data involved.

As a tip, next time you encounter this problem, look at the graphical query plan, and see which node(s) are very expensive. It will guide you where to check indexes (for create or rebuild).

Answer 4

I had a very similar issue going from 2000 to 2005. We had a view over table of 1 million rows (with self joins, etc), and the query ran for over three hours (we never let it finish, so we don't know if it would ever return). My problem seemed to be directly linked to the number of "Nested Loops" in the table. I see quite a few in your execution plan:

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]))

I used Query Hint: Hash on all my problematic joins, and it reduced the time of the query to a more manageable 30-45 minutes.

I'd love to find the root cause as well, but this is a basic work around.