sqlskills-logo-2015-white.png

SpillToTempDb warning and SpillLevel’s mapping to single versus multiple pass

 

This post shows a few examples of Sort related SpillToTempDb execution plan warnings and the associated SpillLevel attribute. 

This blog post is based on SQL Server 2012, version 11.0.2316 and I’m using the AdventureWorksDW2012 database and creating a separate version of the FactInternetSales table called FactInternetSales_Spill:

SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate
INTO [dbo].[FactInternetSales_Spill]
FROM [dbo].[FactInternetSales];

I started off with 60,398 rows and no indexes.  I then created a clustered index and had the Include Actual Execution Plan enabled:

ALTER TABLE [dbo].[FactInternetSales_Spill] 
ADD  CONSTRAINT [PK_FactInternetSales_SalesOrderNumber_SalesOrderLineNumber_Spill] 
PRIMARY KEY CLUSTERED 
(
    [SalesOrderNumber] ASC,
    [SalesOrderLineNumber] ASC
)WITH (ONLINE = OFF) ON [PRIMARY];
GO

The associated execution plan had no spill warnings:

image

I pumped up the size of this table to 664,378 rows:

INSERT [dbo].[FactInternetSales_Spill]
(ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate)
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, SalesTerritoryKey, LEFT(CAST(NEWID() AS NVARCHAR(36)),20), SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate
FROM [dbo].[FactInternetSales];
GO 10

Dropping and and re-creating the index, I still didn’t see spill warnings, so I pumped it up to 7,912,138 rows:

INSERT [dbo].[FactInternetSales_Spill]
(ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate)
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, SalesTerritoryKey, LEFT(CAST(NEWID() AS NVARCHAR(36)),20), SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate
FROM [dbo].[FactInternetSales];
GO 120

Creating the clustered index on this larger table caused the spill warning to be raised for the Sort iterator (screen shot from the graphical plan, properties of the Sort iterator and the blurb from the XML showplan):

image

Now the “SpillLevel=”8” was interesting to me.  I was also running “old-school” profiler AND an extended events session at the time to see what they had to say about these warnings.

In profiler, I saw 9 sort warning events, 8 of which are “2 – Multiple pass”.  The single pass means the sort table was written to disk and only a single pass over the data was required for the sorted input (but as you see, there were multiple spill events).  The multiple pass means that, well, multiple passes over the spilled data were needed in order to obtain the sorted output:

image

As I expected, Extended Events tells me the same thing:

image

What was also interesting is the behavior if I set ONLINE = ON for my index creation:

ALTER TABLE [dbo].[FactInternetSales_Spill] 
ADD  CONSTRAINT [PK_FactInternetSales_SalesOrderNumber_SalesOrderLineNumber_Spill] 
PRIMARY KEY CLUSTERED 
(
    [SalesOrderNumber] ASC,
    [SalesOrderLineNumber] ASC
)WITH (ONLINE = ON) ON [PRIMARY];
GO

Each event sub class now shows as “Single Pass”:

image

But that’s not all… The execution plan for my “ONLINE=ON” index creation shows a spill level of “1” – not “8” or “9”.  So each single pass – even though it happens nine times, just shows up as just SpillLevel=”1”:

image

Now my index creation was executing with parallelism, and indeed 8 threads were involved the Sort iterator execution:

image

 

Removing parallelism from the picture, aside from my index creation taking significantly longer, I see only one spill warning now:

image

And setting the maximum degree of parallelism to “4” – I see a total of 5 warnings:

image

And the plan itself shows a spill level of “4”:

image

So the “multiple pass” increases the spill level one-for-one.  If I have multiple “single pass” events for the same sort, it shows up as a spill level “1”.

This was just for my particular scenario.  Have you seen other behaviors as well?  If so, please share here.

3 thoughts on “SpillToTempDb warning and SpillLevel’s mapping to single versus multiple pass

  1. Looks like a bug. I’d normally use the spill level to estimate how severely the data overflowed to tempdb. Can’t do that anymore with this bug.

  2. I certainly didn’t expect this behavior. In absence of documentation, the question is if Microsoft sees this as "by design" or a bug as you say.

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.