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

When executing a query statement, it is found that the time difference between top (10) and top (100) is significant. When tuning it, it is found that the Sort Operator consumes up to 95% times and throws warning:

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

1, why does the warning appear?

To quote: Actual number of rows are 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 of the can do in this situation was to make sure the 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 is essentially spills to disk.

The reason SQL Server throws warning is that actual number of rows and estimated number of rows are inconsistent when SQL Server is in a sort operation because the pre-granted memory is less than the actual required memory, causing s The QL server must store the intermediate result set in tempdb. If you fix this warning, all the data is sorted in memory, you can improve the performance of the query statement.

2, how do I keep the Actual number of rows and estimated number of rows consistent?

Why does 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 T Hey ' re just big queries. (Remember, SQL Server uses memory for three things-caching raw data pages, caching execution plans, and workspace for Qu Eries. That workspace memory ends up being fairly small.)

how can I reduce spills?

By writing sargable T-SQL statements, have up-to-date statistics, putting enough memory in the server, building the Righ t indexes, and interpreting the execution plans when things Don's work out the the the-the-the-the-the-the-". Check out Grant Fritchey's book SQL Server Query performance Tuning for detailed explanations of all of THOSE.

Reference 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