First published on MSDN on Oct 28, 2015
For those that have some experience analyzing query plans, you probably have come across spills to TempDB. These warnings can show up in Hash or Sort operations as a yellow exclamation mark. In fact, although both use TempDB, these are quite different in intent and also in implementation. For instance, a hashing operation that spills will be supported by a Workfile in TempDB, while a sort operation (amongst others) is supported by a Worktable.
A spill that occurs during a Sort operation is known as a
. Sort warnings indicate that sort operations do not fit into memory. This does not include sort operations involving the creation of indexes, only sort operations within a query (such as an ORDER BY clause used in a SELECT statement).
If a query involving a sort operation generates a Sort Warnings event class with a spill level value of 2, the performance of the query can be affected because multiple passes over the data are required to sort the data. In the below example we see a
spill level value of 1
, meaning that one pass over the data was enough to complete the sort. Still, this is using TempDB, and as such using I/O potentially with negative impact on the query performance.
To understand how impactful the spill is in this context, for a more complete analysis, it is important to know how much I/O we are talking about, and correlate that with the performance requirements for the query.
That is why in SQL Server 2016 we have enhanced the information about the spill. As seen below, the left image shows a Sort Warning up to SQL Server 2014, whereas the right image shows the SQL Server 2016 enhancement. There, we now can see
how many threads spilled
(this example shows a parallel execution),
how many pages
were involved in the spill, as well as the
for the operator, all in the Warnings section.
Another type of spill might occur during a Hash operation, which is known as a
. These occur when a hash recursion or cessation of hashing (hash bailout) has occurred during a hashing operation.
occurs when the build input does not fit into available memory, resulting in the split of input into multiple partitions that are processed separately. If any of these partitions still do not fit into available memory, it is split into sub-partitions, which are also processed separately. This splitting process continues until each partition fits into available memory or until the maximum recursion level is reached.
occurs when a hashing operation reaches its maximum recursion level and shifts to an alternate plan to process the remaining partitioned data. These events can cause reduced performance in your server.
As seen below, the left image shows a Hash Warning up to SQL Server 2014, and the right image shows the SQL Server 2016 enhancement, with the same added information described for the Sort warning.
With this added information, when analyzing the plan, you now have a more complete picture of what a spill meant for your query performance.