Post reply

Upgrading from 2000 to 2008 performance issue

  • October 13, 2010 at 11:29 am

    #1236384

    The LOOP join is a hint where they're trying to force a choice on the optimizer. In addition to fixing the scan, I'd test taking out that hint to see what it does for performance.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • October 13, 2010 at 5:01 pm

    #1236572

    I removed the Loop hint and it seem to make the performance worse than what it was before the move.

    It degraded tremendously with the loop hint removed.

  • October 13, 2010 at 7:21 pm

    #1236599

    reggie burns-317942 (10/13/2010)

    I removed the Loop hint and it seem to make the performance worse than what it was before the move.

    It degraded tremendously with the loop hint removed.

    Did you check the datatypes of the columns in the joins like Jo and I suggested? It makes a hell of a difference in 2K8.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 14, 2010 at 9:17 am

    #1236939

    Yea. All the datatypes in the joins are int.

    Could it be because of having over 50mil records in one table and over 80mil in a 2nd table and neither are partitioned in 2008?

  • October 15, 2010 at 11:49 am

    #1237751

    Could I see a performance change with 2000 and 2008 if they are configured as

    2000 has Windows2003 Enterprise with 8GB RAM

    2008R2 has Windows2008 R2 Enterprise with 8GB RAM

    Could 2008 performace go down tremendously?

  • October 15, 2010 at 11:52 am

    #1237753

    reggie burns-317942 (10/15/2010)

    Could I see a performance change with 2000 and 2008 if they are configured as

    2000 has Windows2003 Enterprise with 8GB RAM

    2008R2 has Windows2008 R2 Enterprise with 8GB RAM

    Could 2008 performace go down tremendously?

    I would say no, based on this change only.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 6 posts - 16 through 20 (of 20 total)

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