question

Firstly, how you can be sure that the same execution plan exists for the server and for your local PC? ;) Secondly, can you please post the actual execution plan in a format other than an image? Also, I can see a temp table, can you give its purpose and details. Other details like indexes, number of rows etc. for the involved tables can also help to restructure the query. For e.g. I would rather have this query be re-structured as the following (assuming that ProductGroupID is unique in ProductGroups table) SELECT PG.ProductGroupID FROM ProductGroups AS PG WITH ( NOLOCK ) WHERE EXISTS ( SELECT ProductGroupID FROM ProdGroupItems WITH ( NOLOCK ) WHERE Manual = 1 AND CPEStatusFlag = 2 AND ProductGroupID = PG.ProductGroupID ) AND EXISTS ( SELECT 1 FROM ProductGroupLocUpdate AS PGLU WITH ( NOLOCK ) WHERE PG.ProductGroupID = PGLU.ProductGroupID AND PGLU.EngineID = 2 ) AND NOT EXISTS ( SELECT 1 FROM #UploadedProdGroups AS UPG WHERE UPG.ProductGroupID = PGLU.ProductGroupID ) To me, it gives better readability :) Looking at the image, seems like there is index missing on ProductGroupID in ProductGroups table (I may be completely wrong) Last but not least, I hope you know the ill-effects of NOLOCK hint.

Without seeing the plan itself with the ability to drill down on the values in the properties sheet, it's difficult to point to specifics. However, looking at the picture of the plan, the Merge Join operation with a very small data set combined with a larger data set suggests that there might be discrepancies between the data and the statistics. That's about the most I can say based on the information provided. Two things about the query. First, DISTINCT is a crutch that is used when queries are not structured correctly, data storage is not structured correctly, or you're missing constraints. I would strongly suggest fixing those issues rather than relying on an aggregation function to eliminate duplicate rows. Second, as @usman mentions, NOLOCK is a very dangerous hint to be using. It can lead to grossly incorrect data including duplicate and missing rows. There are two things you could do instead. If you really can get away with dirty reads, just change the connection strings to use read_uncommitted. Then you don't have to put a hint on each table. That's a horrible way to code. But most people are going to have problems with dirty reads. I usually advise, if blocking due to locks is a serious problem in your code, that you use snapshot isolation such as read_committed_snapshot. While this does put more of a load on tempdb, it radically reduces blocking due to locks. And it doesn't lead to incorrect data.