Missing Indexes, Lazy Spools and Undocumented Flags

Now, lets start with the fact that Spools are awesome! Who doesn’t like free indexes stored in the tempdb? The whole idea about that spool operator is that it consumes a set of data and stores it in a temporary structure in the tempdb in order to be reused.

However, they could be expensive! The whole idea of loading data into a lazy spool operator and being a bit lazy so that the data is there when needed later within the operations of the query is not that appealing to me. That’s not what Billy Idol said! We need speed!

SELECT  *
FROM    Sales.CountryRegionCurrency AS CRC
        INNER JOIN Sales.CountryRegionCurrency AS x ON x.CountryRegionCode = CRC.CountryRegionCode
        INNER JOIN Sales.Currency ON Currency.CurrencyCode = CRC.CurrencyCode
		INNER JOIN sales.CurrencyRate ON  CurrencyRate.FromCurrencyCode = Currency.CurrencyCode
		OPTION (FORCE ORDER, LOOP JOIN, MAXDOP 1, RECOMPILE)
 

Notice: I’ve used some hints to force a Spool( Who’s got time to wait for one, AMIRITE?)

1

As you can see, it has a Lazy Spool and of course some weird missing index suggestion 😦

Now lets use some undocumented trace flags!

First, make sure you use: DBCC TRACEON(3604) so we can actually see what’s happening.

I know we’ve said that Lazy Spool doesn’t use any memory, but it’s good to check, OK??

SELECT  *
FROM    Sales.CountryRegionCurrency AS CRC
        INNER JOIN Sales.CountryRegionCurrency AS x ON x.CountryRegionCode = CRC.CountryRegionCode
        INNER JOIN Sales.Currency ON Currency.CurrencyCode = CRC.CurrencyCode
		INNER JOIN sales.CurrencyRate ON  CurrencyRate.FromCurrencyCode = Currency.CurrencyCode
		OPTION (FORCE ORDER, LOOP JOIN, MAXDOP 1, RECOMPILE, QUERYTRACEON 2373)
 

You shall notice the following memory utilization during the optimization process:

Memory before deriving properties of LogOp_Spool: 38
Memory after deriving properties of LogOp_Spool: 38
Some other logical operations *sigh
Memory before rule BuildSpool: 39
Memory after rule BuildSpool: 39

As we can see, our Spool is free memory of charge! Wait, does this make us happy enough to call it a day? Nope.

SELECT  *
FROM    Sales.CountryRegionCurrency AS CRC
        INNER JOIN Sales.CountryRegionCurrency AS x ON x.CountryRegionCode = CRC.CountryRegionCode
        INNER JOIN Sales.Currency ON Currency.CurrencyCode = CRC.CurrencyCode
		INNER JOIN sales.CurrencyRate ON  CurrencyRate.FromCurrencyCode = Currency.CurrencyCode
		OPTION (FORCE ORDER, LOOP JOIN, MAXDOP 1, RECOMPILE, QUERYTRACEON 8607)
 

By using Querytraceon 8607 we will have a look at the physical operators and the output tree that is indeed closer to the final stage of the execution plan.

This pretty much gives us a nice overview of what is happening inside the magical query that we want to use to destroy the database! After some digging, we can see that indeed the join on Sales.CurrencyRate is the one to blame for unwanted icons appearing in our query (a.k.a the LAZY Spool (Sounds like a Call of Duty nickname))

 

PhyOp_Spool LAZY

 PhyOp_Range TBL: sales.CurrencyRate(1) ASC Bmk ( QCOL: [AdventureWorks2014].[Sales].[CurrencyRate].CurrencyRateID) IsRow: COL: IsBaseRow1004 

 ScaOp_Comp x_cmpEq

 ScaOp_Identifier QCOL: [AdventureWorks2014].[Sales].[CurrencyRate].FromCurrencyCode

 ScaOp_Identifier QCOL: [AdventureWorks2014].[Sales].[Currency].CurrencyCode

Well, lets pull the trigger! Hasta la vista, baby spool!
CREATE NONCLUSTERED INDEX Sales_CurrencyRate_FromCurrencyCode ON sales.CurrencyRate (FromCurrencyCode) WITH (DATA_COMPRESSION = PAGE)
GO
SELECT *
FROM Sales.CountryRegionCurrency AS CRC
INNER JOIN Sales.CountryRegionCurrency AS x ON x.CountryRegionCode = CRC.CountryRegionCode
INNER JOIN Sales.Currency ON Currency.CurrencyCode = CRC.CurrencyCode
INNER JOIN sales.CurrencyRate ON CurrencyRate.FromCurrencyCode = Currency.CurrencyCode
OPTION (FORCE ORDER, LOOP JOIN, MAXDOP 1, RECOMPILE)

2
As you can see, everything comes to an end, even a persisted spool!

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s