SQL Server Performance

Different execution plan

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by atulgoswami, Feb 1, 2010.

  1. atulgoswami New Member

    I was just working on one performance issue and came across a strange point, which i am not aware.
    I got the statistics from prod environment for one SP, in which there was one process, in which data is inserted into one temp table.
    Temp table was created explicitly at the beginning of the stored procedure and one non clustered index on it.

    The Issue: The select statement, which is used to populate this temp table, shows huge row scan (12 million rows) at one join condition and that was a merge join.

    Now when i took out the select statement and ran on my local environment and checked the execution plan for this select statement only then there is a huge difference. In production almost all joines were performed using HASh MATCH-Clustered Index Scan and from my local environment it shows nested loop - Clustered Index Seek
    But Why it is different if i exclude the insert process.
    What i guess, is execution plan changes on the amout of data processed as my local environment does not have that much data but this is just totally different. Now in this case, if my assumption is right then how to analyze this kind of problems where we have to wait to get access of prod copy environment.
    Thanks in advance
  2. preethi Member

    Hi,
    Execution plans are created based on the statistics on data distribution. When you have different sets of data the execution plans could be different. Having that said, I assume that you are referring only a small set of data (where in dev environment it is having a higher percentage than in production) you need to check a few other things.
    • Did you run the query in production for a larger data set before? (if so you may be still using the same execution plan)
    • Are the statistics Up to date in both environments?
    • Do you have same amount of indexes.
    • Index fragmentation in production environment could have caused some issues.
    We may not be able to say the exact reason for the difference in performance without knowing the query, schema involved and data distribution.

Share This Page