SQL Server Performance Forum – Threads Archive

execution plan differences

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.

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
***********************
http://weblogs.sqlteam.com/dinakar/

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.

Did you run update statistics in both servers?
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

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

Great point Luis. [<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. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of 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.</center>

]]>