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:

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):

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:

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

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”:

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”:

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

 

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

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

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

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.