Performance issue in Prod compared to DR

  • We just took over this SQL server application and have been working on the really bad queries. We tuned a few but this one we have no clue on

    The query takes the form of INSERT INTO A ..... , SELECT .... FROM B WHERE NOT EXISTS (SELECT .... FROM A WHERE MATCHING KEYS)

    Table A has a cardinality of 260 million rows

    The query runs in under 15 minutes in DR while inserting between 500,000 and 600000 rows from TABLE B but takes over 90 minutes everyday in Prod for the same.

    This is what I have so far
     - Worked with the DBA in updating the stats on both tables and indexes. I also verified the various details on the index in question including fragmentation count,page count , fragment size etc and they are extremely close
    - I have verified the actualy execution plans and it is hard to believe but they are exactly the same with the bulk of the time being spent in accessing the clustered index (around 79%)
    - The said clustered index is not unique

    What else can I check to see what can be done to improve the performance?  I would have expected the run times to be fairly even and maybe should have been better in Prod compared to DR. The one thing I don't know is what the underlying servers run on.

    Appreciate all your help.

  • Can you post the actual execution plan please?
    Is the query getting blocked? Spending a lot of time waiting?

    Those percentages int the plan are not times. They're estimated costs (emphasis *estimated*) and they can be very wrong.

    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

Viewing 2 posts - 1 through 1 (of 1 total)

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