question

jyoukhanis avatar image
jyoukhanis asked

operator used tempdb to spill data during execution with spill level 1

![alt text][1]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) [1]: /storage/temp/3495-capture.png
sql server 2012tempdb
capture.png (28.4 KiB)
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Don't trust those percentages. They are still estimates, even in an actual (as opposed to estimated) execution plan. The spill is the symptom here though, not the cause. Can you save the execution plan as XML and attach it here? That's where the good stuff is.
0 Likes 0 ·
Can you define slow?
0 Likes 0 ·
jyoukhanis avatar image
jyoukhanis answered
[link text][1] 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. [1]: /storage/temp/3496-execution-plan.xml

execution-plan.xml (372.5 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jyoukhanis avatar image
jyoukhanis answered
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.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Your using a number of temp tables within the query, you'll be using tempdb regardless. I noticed that you are updating things such as #InvoiceDetail multiple times. You could look at consolidating those down into a one update statement so that you are only hitting the table once, or at least less than what you are. Some of the calculations that you are doing you could perform on the INSERT instead of the update, for example #InvoiceSummary & is_balance. Why not do it as you insert the data rather than turn around and updated the entire table again? Especially @ 485K rows. Just some things I saw that might help you fine tune it.
0 Likes 0 ·
Is it best practice to change the datatype from UNIQUEIDENTIFIER to IDENTITY(1,1). During my research trying to put indexes on UNIQUEIDENTIFIER will slow down the performance.
0 Likes 0 ·
Having a UNIQEIDENTIFIER as the clustering key will most likely slow things down, especially on inserts. In addition, that key will be included in all of the non-clustered indexes thus potentially causing bloat. I prefer to have the smallest ever increasing identity (usually an INT) as my primary key.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.