Spill data to tempdb

When viewing execution plan, on the sort Operator, a warning was found:Operator used tempdb to spill data during execution with spill level 1

View the execution plan in XML format and discover a spilltotempdb node:

< Warnings >   <  spilllevel= "1"/></Warnings>

Sort Warnings is raised by SQL Server when a sort operation cannot is done with memory and must spill to tempdb.

One, Grant Memory

When SQL Server compiles a query statement, Optimizer will Estimate the memory required by the query statement to complete the sort or hash operation (Grant memory), which SQL Server must request when the statement is actually executed. Otherwise, the query statement will not execute. In the execution of the query statement, the Grant memory will not be consumed by other thread, and will not be reduced, so that each query statement has a stable memory environment, to ensure that the query statement can be executed smoothly. Since the grant memory is the value predicted by optimizer at Compile-time, the amount of memory that is really needed for the sort or hash operation, when the query statement executes, may be greater than the grant memories. For the index sort operation, SQL Server requests a beta from buffer pools, increasing the grant Memory to avoid spill Data to tempdb, and if the non-index sort operation, SQL Server in order to complete the sort or hash operation, it is necessary to overflow a part of the Sort intermediate table or hast table data to tempdb, to do a sort or hash operation in slow disk (build hash table or probe hash match), which reduces query performance. At the same time, the SQL Server executor throws a sort Warning or Hash Warning, which is thrown at run-time and cannot be compile-time through the query plan (sys.dm_exec_query_plan and Sys.dm_exec_text_query_plan) gets to.

Refer to "Never Ignore a Sort Warning in SQL Server":

SQL Server does ask for more memory for a in-progress sort to avoid spilling to TempDB. But (there was always a ' but '), it's not for regular sorts. It is only works for index sorts. The interesting thing it can "steal" from the buffer pool as much memory as it needs, so as to avoid a spill to disk.

1,query Memory Spills

Reference: Query Memory spills

When your sometimes look at execution plans, you can see that the SELECT operator have sometimes a so-called Memory Gran T assigned. This Memory Grant was specified in kilobytes and was needed for the query execution, when some operators (like Sort/hash ope rators) in the execution plans need memory for execution–the so called Query memory.

This query memory must was granted by SQL Server before, the query is actually executed. The query Optimizer uses the underlying Statistics to determine how much query Memory must is acquired for a given query. The problem is now, while the Statistics is out-of-date, and SQL Server underestimates the processed rows. In this case, SQL Server would also request to less query Memory for the given query. But when the query actually executes, the query can ' t resize it granted query Memory, and can ' t just request more. The query must operate within the granted query Memory. In this case, SQL Server have to spill the sort/hash-operation into TempDb, which means, we very fast in-memory Operat Ion becomes a very slow physical on-disk operation. SQL Server Profiler would report those Query Memory spills through the events Sort Warnings and Hash warning .

2,hash Join

Before a hash join begins execution, SQL Server tries to estimate how much memory it would need to build its hash table. It uses the cardinality estimate for the size of the build input along with the expected average row size to estimate the Memory requirement. To minimize the memory required by the hash join, the optimizer chooses the smaller of the "the" Build table. SQL Server then tries to reserve sufficient memory to ensure that the hash join can successfully store the entire build TA ble in memory.If SQL Server grants the hash join less memory than it requests or If the estimate are too low, the hash join might run Out of memory during the build phase. If the hash join runs out of memory, it begins spilling a small percentage of the total hash table to disk (to a WORKF Ile in tempdb). The hash join keeps track of which buckets of the hash table is still in memory and which ones has been spilled to Disk. As it reads each of the new row from the build table, it checks-to-see whether it hashes to a in-memory or an on-disk bucket. If it hashes to an in-memory bucket, it proceeds as usual. If it hashes to a on-disk bucket, it writes the row to disk. This process of running out of memory and spilling buckets to disk can repeat multiple times until the build phase is comp Lete.

Second, in SQL Server 2012, track memory spills through extended events.

--Step1,create Event SessionCreateEvent Session Tracksortwarning onServerAddEvent sqlserver.sort_warning (Action (sqlserver.sql_text)where(sqlserver.database_id= the))AddTarget Package0.event_file (SetFileName=N'D:\TrackSortWarning.xel', max_file_size= -) with(Max_memory=4096Kb,event_retention_mode=allow_single_event_loss,max_dispatch_latency= -seconds,startup_state= on);--Step2, start the event session--Step3,query; withCte as (Select object_name,cast(Event_data asxml asEvent_data fromSys.fn_xe_file_target_read_file ('D:\tracksortwarning*.xel',NULL,NULL,NULL))SelectEvent_data.value ('(event/@timestamp) [1]','datetime') as [timestamp], Event_data.value ('(event/data[@name = "Sort_warning_type"]/text) [1]','varchar (+)') asSort_warning_type, Event_data.value ('(event/action[@name = "Sql_text"]/value) [1]','varchar (max)') asSql_text fromCte

Third, solve the memory spills problem

1,parameter sniffing

When the query plan executes, the compiler probes the current value of the variable, generating an execution plan that is "good enough" for the current value of the variable.

 select   ...  from   Dbo.dt_test  where  co1>   @var_id  option  (RECOMPILE); 

When sort warnings was caused by parameter sniffing, the solution was to use the RECOMPILE query hint in the S tored procedure definition. This option forces the optimizer to regenerate the execution plan for the statement containing the hint, rather than r Eusing an existing execution plan. The optimizer still sniffs the parameter value, but this process happens whenever a stored procedure are executed, not only For the first invocation. When a execution plan is generated, the optimizer checks the value of the parameter, chooses a optimal execution plan an D grants sufficient memory to the appropriate operators.

2, update outdated stats

If automatic Update stats info is not turned on, SQL Server optimizer generates an execution plan based on outdated statistics, which may result in a smaller number of estimated rows than actual data rows, resulting in a small grant memory, using UPDATE STATISTICS (Transact-SQL) updates statistics.

3, create index, or rewrite the query statement using searchable Arguments (SARG)

If the query statement does not meet the requirements of SARG, SQL Server Optimizer cannot use index or statistics, resulting in a bias in the optimizer estimated "estimated number of rows". In this case, you must rewrite the query statement, using SARG. If necessary, add the grant Memory dynamically by creating an index to avoid spill data to tempdb.

Recommended reading "Never Ignore a Sort Warning in SQL Server"

Reference Documentation:

Never Ignore a Sort Warning in SQL Server

Correct SQL Server TempDB spills in Query plans caused by outdated Statistics

Query Memory Spills

Identifying and solving Sort Warnings problems in SQL Server

Operator used tempdb to spill data during execution with spill level

Spill data to tempdb