SQL Server Performance

execution plan differences

Discussion in 'T-SQL Performance Tuning for Developers' started by rklimes, May 17, 2007.

  1. rklimes New Member

    I have a very basic query that creates different execution plans in production and test environments. Both environments have similar amount of data in the table and indexes are identical. I would preferably like to have the production server use same plan as the test server because it is more efficient. Any suggestions on how to achieve this would be appreciated. Thanks.
  2. ndinakar Member

    they may have the same data but do they have same indexes and is the prod copy properly indexed? when was the last time all the tables were reindexed? There are a lot of factors that can affect a different query plan.

    Dinakar Nethi
    SQL Server MVP
  3. rklimes New Member

    As stated the indexes are identical. The table gets reindexed approx once per week in production but never in test. In addition I have run DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE.
  4. Luis Martin Moderator

    Did you run update statistics in both servers?

    Luis Martin

    All in Love is Fair
    Stevie Wonder

    All postings are provided “AS IS” with no warranties for accuracy.

  5. rklimes New Member

    Thanks Luis. Updating the statistics in the test environment caused the execution plans to be the same as production.
  6. satya Moderator

    Great point Luis.<br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />] Thats where I see lot of developers assumes the optimization tasks are not important on test or dev. platforms. Inorder to produce the correct results it is always better to match the relevant jobs & processess between the environments.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>

Share This Page