operator used tempdb to spill data during execution with spill level 1
![alt text]I have a store procedure that is running slow, Attached is the sql plan. Part of the query is consuming 36% of the total cost. Below is the TSQL. INSERT INTO #PaymentSummary( ps_ivd_key, ps_amount) SELECT pd_ivd_key, SUM(pd_amount - ISNULL(pd_adjust_amount, 0.00)) FROM #PaymentDetail (NOLOCK) GROUP BY pd_ivd_key Here is the schema of the temp tables CREATE TABLE #PaymentDetail( pd_ivd_key UNIQUEIDENTIFIER NULL, pd_ajd_key UNIQUEIDENTIFIER NULL, pd_adjust_amount MONEY NULL, pd_amount MONEY NULL) CREATE INDEX idx_pd_ivd_key ON #PaymentDetail( pd_ivd_key) CREATE INDEX idx_pd_ajd_key ON #PaymentDetail( pd_ajd_key) CREATE TABLE #PaymentSummary( ps_ivd_key UNIQUEIDENTIFIER NULL, ps_amount MONEY NULL) CREATE INDEX idx_ps_ivd_key ON #PaymentSummary( ps_ivd_key) : /storage/temp/3495-capture.png
[link text] XML planned is attached. The proc takes about 12 minute to run on SQL Server 2012 where on SQL Server 2008 takes 6 min. SQL Server 2012 has 36 GB ram vs SQL server 2008 has 16gb. : /storage/temp/3496-execution-plan.xml
Is the database the same on both servers? That might explain it. Have you run it a second time on the 2012 copy? You need to get the data into the buffer pool first before timings are relevant. Have you updated statistics on the 2012 copy? If you upgrade a server or restore a database to a later verson of SQL Server it's always a good idea to update the statistics. Have you looked at the missing indexes recommended in the plan? For example, the second to last query estimated 14k rows in mb_membership but found 65k instead and did a key lookup on each one. The plan is recommending an index that will probably let it do an index seek or scan instead. In case you haven't heard about it, look for SQL Sentry Plan Explorer. It's a free tool for getting more information out of execution plans. Really really useful for this kind of thing.
David, First and Foremost thank you for your quick response.I have ran the proc more than 5 times, the execution plan should already be the cache plan. I have a maintenance plan that I rebuild or reorganize the indexes on a nightly basis using Ola Hallengren's scripts. The stats are up to date and I also use SQL Sentry Plan Explorer. In that specific batch of TSQL the actual rows are 7,377,192 and the estimated rows are 7,475,160. The problem is the data is spilling to TEMPDB, the question is How can i rewrite that query where it will not use the TEMPDB, I will also add the recommended indexes and rerun. Again I really appreciate all your help.