Delete query taking more tempdb and execution time in sql server 2012 as compared to 2008 R2
DELETE FROM [FCT_YIELD] WHERE YIELD_KEY NOT IN ( SELECT MAX(YIELD_KEY) FROM [FCT_YIELD] GROUP BY [PHASE_KEY] ,[HOS] ,[YIELD_TYPE] ,[CUT_COMPONENT] ,[COMPONENT_TYPE] ,[UOM_KEY] ,[DQ_KEY] ,[YIELD_PCT_BY_WT] ,[RECORD_STATUS]) Time taken in SQL Server 2008 R2 - 7 min using 16GB of tempdb Time taken in SQL Server 2012 - 43 min using 97GB of tempdb
It could be that your statistics haven't been updated since the move between systems. It could be that you're hitting a regression, a change in the execution plan caused by a change in how the optimizer works. These are somewhat common, but usually only when the query is already something of an edge-case. It could be differences in how the system is configured such as the Max Degree of Parallelism, or the Cost Threshold for Parallelism, or Max Memory, the number of CPUs available. There really are a lot of options and it's just guessing without a lot more data. A very good start is what @DavidWimbush is suggesting.