SQL Server 2016 – Added information on TempDB Spill events – Showplan
Cross post with https://aka.ms/sqlserverteam
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 Warning . 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 memory statistics for the operator, all in the Warnings section.
Another type of spill might occur during a Hash operation, which is known as a Hash Warning . These occur when a hash recursion or cessation of hashing (hash bailout) has occurred during a hashing operation.
Hash recursion 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.
Hash bailout 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.
Pedro Lopes (@sqlpto) - Program Manager