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

1，为什么会出现warning？

To quote: Actual number of rows is greater then estimated one. SQL Server grants memory before execution, looking on estimated values. At run time it gets more rows then expected so sort spills in temp db. All you can do in this situation is to make sure that estimated values are correct. Try to update statistics on involved tables. Remove predicates one-by-one to find one which leads to wrong estimates.

Spills to TempDB are essentially spills to disk. 

SQL Server 抛出Warning的原因是Actual Number of Rows 和 Estimated Number of Rows 不一致，在SQL Server 在进行排序操作时，由于预先被授予的内存少于实际需要的内存时，导致SQL Server必须将中间结果集存储到tempdb中。如果修复这个warning，使全部数据都在内存中排序，就能提高查询语句的性能。

2，怎么使 Actual Number of Rows 和 Estimated Number of Rows 保持一致？

Why do queries spill to disk?

Because SQL Server didn‘t grant them enough memory to complete their operations. Perhaps the execution plan underestimated the amount of memory required, or perhaps the box is under memory pressure, or they‘re just big queries. (Remember, SQL Server uses memory for three things - caching raw data pages, caching execution plans, and workspace for queries. That workspace memory ends up being fairly small.)

How can I reduce spills?

By writing sargable T-SQL statements, having up-to-date statistics, putting enough memory in the server, building the right indexes, and interpreting the execution plans when things don‘t work out the way you expected. Check out Grant Fritchey‘s book SQL Server Query Performance Tuning for detailed explanations of all of those.

参考Post：

Never Ignore a Sort Warning in SQL Server

SQL Server 2012: Sort operator causing tempdb spill

Correct SQL Server TempDB Spills in Query Plans Caused by Outdated Statistics

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

标签：