Results 1 to 7 of 7
  1. #1
    Join Date
    May 2006
    Posts
    38

    Lightbulb Unanswered: Different Execution Plans - Same Query

    Hi SQL Gurus,

    I am looking for your review on one of the performance issues which we have.

    One of our query used to run within 20 secs and I had saved a execution Plan then . This Execution Plan showed parallelism in every step.

    After a month, the performance degraded and this time the query takes over 2 hours. I saved the plan again to compare. On checking I find that no parallelism is being involved this time.


    Technology : SQL Server 2005 ( SP3)


    I have tried rebuilding the index and have also done UPDATE stats with FULLSCAN on the underlying tables of the query but I still get the same execution plan with no parallelism involved.

    On the data front,

    --> the changes to the table has been minimal ( it has grown from 102 million to around 110 million records )
    --> All indexes are the same


    One interesting this is -- when I do a DBCC SHOW STATS on the earlier data ( the one which took around 20 secs ) , the rows sampled is less than 5%

    While in the current state ( which take around 2 hours ) , the rows sampled through the indexes is 100 %


    Can you please review and guide me through any pointers that I need to look.

    Thanks in advance!

    Warm Regards,
    RanjitSHans

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    is this in a stored proc?

    want to post the ddl, including indexes and the query
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2006
    Posts
    38
    Hi Brett,

    The sql statements are select statements for a report using a view ( cannot index the view as it contains certain Outer Joins ) ..

    There is no change to the SQL statements .. it is the same one.

    however the execution plans differ ..

    - Also, there has been no environmental changes to the OS/ SQl instance.

    - No excessive blocking or any wait-types.. this time around .. it just sits there active but doesnt do anything for like an hour and the execution plan throws around 61% index seeks ( earlier it was 64% with parallelism )

    - there are no index scans happening in either of the plans

    - I have tried doing it by MAXDOP 1 and OPTION LOOP /MERGE / HASH .. still no effect on the execution times


    Thanks in advance for the guidance!

    Warm Regards,
    RanjitSHans





    Thanks,

  4. #4
    Join Date
    May 2006
    Posts
    38
    The status is --

    -> I could find out that indexes/fragmenatation is not the cause for slowing it down

    -> No environmental changes (Os/ SQL ) and no resource contentions with CPU/Network/IO etc

    Now I wonder what else may cause a query to slow down if the above two are not causing the bottlenecks ..

    Any idea what else can I check


    Thanks,
    Hans

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    May 2006
    Posts
    38

    Cool

    Thanks Brett / Thrasymachus

    As things were kinda heating up .. I took the easy way out ..

    I partitioned the table into chunks and lo and behold .. I got my results back in 20 secs ..


    I have bcp'ed the underlying data into my ever-growing archive and plan to look at the query predicates some other time.. I am guessing it could be a case of bad indexes for the table or something ..


    In anycase.. Thanks once again for your review and attention to this one.


    Warm regards,
    RanjitSHans

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    well good luck and let us know how it turns

    post the query and the ddl if you care and we can have a look
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •