Post reply

Execution plan question

  • March 1, 2007 at 2:39 pm

    #172283

    I have identical structure and indexing on the tables involved in the following SQL statement.  In development, it returns 975 records in under a second, with no table scans.  In production, it takes 5 to 12 seconds to return data and includes 2 tables scans in the execution plan.  Does anyone have any ideas about why?

    SELECT    dbo.tblDocuments.Document_ID, dbo.tblDocuments_Matrix_Levels.Name_ID, dbo.tblDocuments.DocBook, dbo.tblDocuments.Project_ID,

                          MAX(CAST(dbo.tblDocuments_Matrix_Levels.ViewPrint AS Int)) AS ViewPrint, MAX(CAST(dbo.tblDocuments_Matrix_Levels.Download AS Int))

                          AS Download, MAX(CAST(dbo.tblDocuments_Matrix_Levels.Notify AS Int)) AS Notify, dbo.tblDocuments.DocumentStr, dbo.tblDocuments.Del_DocString,

                          dbo.tblDocuments.ClientDocument_No, dbo.tblDocuments.SheetNo, dbo.tblDocuments.SheetNoTotal, dbo.tblDocuments.Title,

                          dbo.tblDocuments_Rev.Rev, dbo.tblDocuments_Rev.Rev_Date, dbo.tblRev_Purpose.Rev_Purpose_Code, dbo.tblDocuments_Rev.Filename,

                          dbo.tblFileExtensions.Extension

    FROM         dbo.tblRev_Purpose RIGHT OUTER JOIN

                          dbo.tblDocuments_Rev ON dbo.tblRev_Purpose.Rev_Purpose_ID = dbo.tblDocuments_Rev.Rev_Purpose_ID LEFT OUTER JOIN

                          dbo.tblFileExtensions ON dbo.tblDocuments_Rev.Extension_id = dbo.tblFileExtensions.Extension_ID RIGHT OUTER JOIN

                          dbo.tblDocuments_Matrix_Levels WITH (NOLOCK) INNER JOIN

                          dbo.tblDocuments WITH (NOLOCK) ON dbo.tblDocuments_Matrix_Levels.Project_ID = dbo.tblDocuments.Project_ID ON

                          dbo.tblDocuments_Rev.DocumentRev_ID = dbo.tblDocuments.Rel_Rev_ID

    WHERE     (dbo.tblDocuments_Matrix_Levels.L1_ID > 0) AND (dbo.tblDocuments.L1_ID = dbo.tblDocuments_Matrix_Levels.L1_ID) AND

                          (dbo.tblDocuments.Rel_Rev_ID <> 0) OR

                          (dbo.tblDocuments.Rel_Rev_ID <> 0) AND (dbo.tblDocuments_Matrix_Levels.L2_ID > 0) AND

                          (dbo.tblDocuments.L2_ID = dbo.tblDocuments_Matrix_Levels.L2_ID) OR

                          (dbo.tblDocuments.Rel_Rev_ID <> 0) AND (dbo.tblDocuments_Matrix_Levels.L3_ID > 0) AND

                          (dbo.tblDocuments.L3_ID = dbo.tblDocuments_Matrix_Levels.L3_ID) OR

                          (dbo.tblDocuments.Rel_Rev_ID <> 0) AND (dbo.tblDocuments_Matrix_Levels.L4_ID > 0) AND

                          (dbo.tblDocuments.L4_ID = dbo.tblDocuments_Matrix_Levels.L4_ID) OR

                          (dbo.tblDocuments.Rel_Rev_ID <> 0) AND (dbo.tblDocuments_Matrix_Levels.L5_ID > 0) AND

                          (dbo.tblDocuments.L5_ID = dbo.tblDocuments_Matrix_Levels.L5_ID) OR

                          (dbo.tblDocuments.Rel_Rev_ID <> 0) AND (dbo.tblDocuments_Matrix_Levels.L6_ID > 0) AND

                          (dbo.tblDocuments.L6_ID = dbo.tblDocuments_Matrix_Levels.L6_ID) OR

                          (dbo.tblDocuments.Rel_Rev_ID <> 0) AND (dbo.tblDocuments_Matrix_Levels.L7_ID > 0) AND

                          (dbo.tblDocuments.L7_ID = dbo.tblDocuments_Matrix_Levels.L7_ID) OR

                          (dbo.tblDocuments.Rel_Rev_ID <> 0) AND (dbo.tblDocuments_Matrix_Levels.L8_ID > 0) AND

                          (dbo.tblDocuments.L8_ID = dbo.tblDocuments_Matrix_Levels.L8_ID) OR

                          (dbo.tblDocuments.Rel_Rev_ID <> 0) AND (dbo.tblDocuments_Matrix_Levels.L9_ID > 0) AND

                          (dbo.tblDocuments.L9_ID = dbo.tblDocuments_Matrix_Levels.L9_ID) OR

                          (dbo.tblDocuments.Rel_Rev_ID <> 0) AND (dbo.tblDocuments_Matrix_Levels.Document_ID > 0) AND

                          (dbo.tblDocuments.Document_ID = dbo.tblDocuments_Matrix_Levels.Document_ID)

    GROUP BY dbo.tblDocuments_Matrix_Levels.Name_ID, dbo.tblDocuments.Document_ID, dbo.tblDocuments.Del_DocString,

                          dbo.tblDocuments.ClientDocument_No, dbo.tblDocuments.SheetNo, dbo.tblDocuments.SheetNoTotal, dbo.tblDocuments.Title,

                          dbo.tblDocuments.DocumentStr, dbo.tblDocuments_Rev.Rev, dbo.tblDocuments_Rev.Rev_Date, dbo.tblDocuments_Rev.Filename,

                          dbo.tblRev_Purpose.Rev_Purpose_Code, dbo.tblFileExtensions.Extension, dbo.tblDocuments.DocBook, dbo.tblDocuments.Project_ID

    HAVING      (dbo.tblDocuments_Matrix_Levels.Name_ID = 1038)

  • March 1, 2007 at 3:35 pm

    #692729

    Are the data volumes the same in Dev and Prod ?

    Is it the same set of data, or different data, with different distribution of indexed column values ?

    Are statistics up to date on both systems ?

    Is the hardware the same ?

    Why are you filtering on a non-aggregate in the HAVING ? Shouldn't that "Name_ID = 1038" be in the WHERE so that filtering can happen to the results before GROUPing versus afterwards ?

     

  • March 2, 2007 at 4:27 am

    #692796

    In addition to the above points, check the fragmentation of the indexes on both your dev and prod boxes.

    Is it possible for you to post the table and index structure for the tables, and perhaps the execution plan, in text format.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 6, 2007 at 8:13 am

    #693322

    We spent several days chasing down why a query was running twice as fast on a test box as it ran in production. Then we noticed that the test box had processors that were twice as  fast as the production system. Something to look at.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • March 7, 2007 at 5:55 am

    #693528

    we seem to visit this question with unfailing regularity. If queries run different then there will be a reason.  To be honest I've never really encountered this type of problem without there being an obvious reason. Hardware needs to be identical so are the databases/structures on the same server, same database. is the data in the tables identical ( not just the results )

    so lets assume same server :-

    • data in tables identical, this means a data compre would find no differences, including num of rows ?
    • indexes and stats identical and all updated ?
    • fill factors identical ?
    • You actually have useful indexes?
    • You've profiled the queries to check plans and io to see which is different and by how much?
    • You've checked the proc cache to see if you're re-using a plan or recompiling - or you've forced a recompile / cleared cache for each run ?
    • you've checked physical and logical io are the same / data always in cache ?
    • You're running from the same client tool with the same settings ?

    That's for a start.

     

     

     

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • March 7, 2007 at 6:58 am

    #693549

    No, servers are not the same either in physical design or data.

    We have decided not to touch the production system since we will be moving to 64-bit SQL 2005 in the next month and can live with the performance that we currently have on the SQL 2000 server for that much longer. We will then rework that server and make it 64-bit also and let it run as a backup to the other, more powerful server.

    In development, the time for the query went from 675 ms to 250 ms by just going to SQL 2005.

    Being primarily a developer, can you guys give me some guidance on how often we should be defraging the DBs and rebuilding the statistics? The boss said last night there is nothing in place already to do this.  So I have been designated to do that also.  🙂

  • March 7, 2007 at 7:12 am

    #693554

    as always it just depends, however, if you have the window then daily. If I have the window and it doesn't cause issues I will re-index every day, I'll always do the stats every day.

    I also try to apply ntfs defrag as often as reqd. I also try to reduce fragmentation.

    Just make sure that your routines don't include any database shrinking!

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • March 7, 2007 at 7:22 am

    #693563

    You really need to evaluate the system. Bare minimum you need to be sure the statistics are getting updated regularly. You should also look into tracking down a script that will optionally defrag the indexes based on their actual fragmentation rather than simply blasting through everything every night.

    We have a minimum set of stuff we do, log backups, full backups, consistency checks, error log cycling, statistics update and index maintenance. Bear in mind, that's a minimum set of maintenance. Your system may require more. Back to the DBA's favorite phrase, it depends.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • March 7, 2007 at 7:27 am

    #693566

    You really need to evaluate the system. Bare minimum you need to be sure the statistics are getting updated regularly. You should also look into tracking down a script that will optionally defrag the indexes based on their actual fragmentation rather than simply blasting through everything every night.

    We have a minimum set of stuff we do, log backups, full backups, consistency checks, error log cycling, statistics update and index maintenance. Bear in mind, that's a minimum set of maintenance. Your system may require more. Back to the DBA's favorite phrase, it depends.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • March 7, 2007 at 7:29 am

    #693568

    Here's one to test or modify for you:

    http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1857

  • March 7, 2007 at 9:06 am

    #693602

    Steve,

    Thanks, this is exactly the kind of help I was looking for.

  • March 15, 2007 at 6:56 am

    #695203

    If you are sure that the plan on your good server is always right to pick up, you can force that using SQL 2005 Plan Guides. Checkout BOL for that.

    --- as always, test them first before moving to production. ---


    -- Amit


    "There is no 'patch' for stupidity."


    Download the Updated SQL Server 2005 Books Online.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply