question

Every version of SQL Server has a different query optimizer and the optimizer makes different choices. For most queries, with a consistent set of statistics, the choices are usually the same. But for some queries, especially very complex queries, the choices can change. Your choices here are fairly limited. First, make sure your statistics are up to date on the 2008 box. If the statistics have aged because of changes to the data that could explain the changes in plans. Also, if the statistics are just different between the 2008 and 2005 boxes, you could be seeing differences in the plans generated. Use [DBCC SHOW_STATISTICS][1] on each box to compare the statistics between them. One or both are likely to be out of date or possibly sampled differently than the other. Usually this comes from a very complex query that needs to be tuned, even in the 2005 version. Try tuning the query to get better performance. I can't make suggestions beyond this here because I don't know what you're working with. Finally, you can try some of the more obscure stuff to see if it will help. Check the state of Traceflag 4199. If it's set to on in your 2008 instance, you're using all the latest optimization techniques. You could experiment with setting it to off. I have a blog post [about that here][2] for more information. You could also try making the optimizer work harder by setting Traceflag 2301 to on. This will open up more choices to the optimizer. But, this can be a very dangerous thing to do. So be careful. I've got another [blog post on that here][3]. The most likely solutions are statistics or tuning the query. [1]: http://msdn.microsoft.com/en-us/library/ms174384.aspx [2]: http://www.scarydba.com/2011/03/07/execution-plan-stability/ [3]: http://www.scarydba.com/2011/06/20/make-the-optimizer-work-harder/

Given that you're going from SQL2005 to SQL2008, you might need to run a couple of maintenance routines before allowing your database to go live. @Grant Fritchey has already suggested updating statistics. You might also want to run DBCC UPDATEUSAGE just to check your data is good in the new environment. (Although that shouldn't affect the optimizer...)

Just posting pictures of execution plans doesn't allow for enough information to understand why something is the way it is. I can make a few guesses at other things to look at. One plan is parallel and the other is not. What is the cost threshold for parallelism on each server? Also, what is the max degree of parallelism on each server? If these are different that could explain why you're seeing differences in the plans. Other than that, I can't help based on what you've provided. At minimum we would need to see the execution plan itself. You can save them as a .sqlplan file and then upload that to here. But, it will show your query too, but that's also needed to understand what's going on. Be very certain that traceflag 4199 is helpful to you. It's a server-wide setting. It could have far reaching impact.

You say that the SQL2008 database was created from a Restore of the SQL2005 database; however, the index being used by the SQL2008 query isn't the same name as that being used by the SQL2005 query. So, yes. @Grant is right - we need to see more than you've provided. Please upload the .sqlplan files and the full schemas for the Action2 & Proxy2 tables (including indexes & keys) from both servers.

Trace 4199 doesn't change anything... in my enviroment. I detected a problem with sql server with "IS NOT NULL OR" and a conjunction of LEFT OUTER JOIN. Changing "IS NOT NULL" to "<> ''" and LEFT OUTER JOIN with CROSS JOIN seems to correct the query plan on sql 2008... ¿? In any case, thank you very much for all your effort and pacience