asked
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
This web site runs by votes. For all the helpful answers below, please indicate this by clicking on the thumbs up next to each of those answers. If any one answer lead to a solution, please indicate this by clicking the check mark next to that answer.
If you could show us the actual (not estimated) execution plan, that will probably show why. You can save the plan to a file and add it here.
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.
Hi Deepa, Please update statistics of the table. This is because statistics are not automatically updated during the upgrade process.
18 People are following this question.
