How to fix : Operarator used tempdb to spill data during execution warning

29 April,2015 by Jack Vamvas

Question: During a  query analysis , the execution plan showed a  warning on a Physical Sort operator with the warning message Operator used tempdb to spill data during execution with spill level 1

 

Operator_used_tempdb_to_spill_data_during_execution

 

<Warnings>

     <SpillToTempDb SpillLevel="1" />

</Warnings>

 

Why is this occurring? How can I fix it?

 

Answer:The warning appears because SQL Server is granting insufficient memory to complete the operation. Understanding the root cause is the challenge.

For ORDER BY and SORT IN TEMPDB read SQL Server – SORT IN TEMPDB and Sort Warnings

Has the execution plan not granted enough memory?

Is the SQL Server under memory pressure?

Are they large queries?

Fix the problem with Root cause analysis.  To progress with fixing the issue , use these suggestions as a starting point.

1) Follow troubleshooting guides for root cause analysis.

How to troubleshoot a slow running query in SQL Server - SQL ...

2) Interpreting execution plans and troubleshooting skills

3) Good quality t-sql and suitable index strategy

4) Regular  index and statistics maintenance

SQL Server sp_updatestats and UPDATE STATISTICS - SQL Server ...

5) Server memory analysis

SQL Server Performance ,memory pressure and memory usage ...

Read more on Execution Plans

SQL Server execution plan as text - SQL Server DBA

SQL Server - How to improve Execution Plan Reuse - SQL Server DBA

SQL Execution Plan - Comparing Estimated Rows and Actual Rows


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to fix : Operarator used tempdb to spill data during execution warning


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer