You never quite know about MS SQL Server Query Engine.
Here we found one of our database systems running really slow. We tried everything, but nothing lead to Isaac’s well.
The system was just too slow.
Looked at every query and nothing came up.
Finally settled on an insert statement. Straight record insert, not even an insert into <table-destination> select * from <table-source>.
The query plan of a performant query looked like the query on top. And, that of the troublesome query looked like the one below.
The non-performant query has a couple of items that I could not place:
- Table Spool
- Eager Spool
The Statistic I/O for the non-performance Query Plan is also a bit out-of-sorts. It had :
- More I/O (Logical Reads)
- A worktable read
So hear I am, goggled until I am sore.
There is a bit of ‘cries’ on the Net that talks about what can cause Table Spool \ Eager Spool.
Microsoft definition for “Eager Spool” is:
Eager Spool Operator
The Eager Spool operator takes the entire input, storing each row in a hidden temporary object stored in the tempdb database. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input. If rebinding is needed, the spooled data is discarded and the spool object is rebuilt by rescanning the (rebound) input.
The Eager Spool operator builds its spool file in an “eager” manner: when the spool’s parent operator asks for the first row, the spool operator consumes all rows from its input operator and stores them in the spool.
Eager Spool is a logical operator.
Remas Rusanu did a noteworthy job – “Getting rid of Table Spool in SQL Server Execution plan” — http://stackoverflow.com/questions/1931100/getting-rid-of-table-spool-in-sql-server-execution-plan :
I certainly understand if you cannot show us the query. But is really hard to guess why the spooling is preffered by the engine whithout knowing any specifics. I recommend you go over Craig Freedman’s blog, he is an engineer in the query optimizer team and has explained a lot of the inner workings of SQL 2005/2008 optimizer. Here are some entries I could quickly find that touch the topic of spooling in one form or another:
SQL customer support team also has an interesting blog at http://blogs.msdn.com/psssql/
And ‘sqltips’ (the relational engine’s team blog) has some tips, like Spool operators in query plan…
And, so I have a bit more familiarity with what can trigger Table Spool.
To me it can occur when one is querying data from a Common Table Expression or sorting data, and when the system is caching that data for later re-use.
So here I am thinking this is a simple insert / values sql.
What can possibly trigger need for caching and later consideration or usage.
Checked the following:
- Computed columns
- Complex column defaults
Finally, checked the indexes as the operator that follows the Table Spool / Eager Spool operators is the “Index Insert”.
As we have two indexes, disabled one of them.
Wow – I am loving Isaac’s well.
So what was the problem. It appeared that Microsoft’s engine triggers “Table Spool” / “Eager Spool” combination when there are two or more indexes that are defined with different partition schemes.
In our case:
First Index : Clustered
create clustered index idx_pf_seed on [dbo].[customer] on [partitionScheme] ([regionID])
Second Index: NonClustered
create nonclustered index idx_CustomerName on [dbo].[customer] ( [insertTime] asc ) ;
Just a thought as there is so little documentation.
A few days later, I posted an addendum to this entry. That entry is available @
Microsoft – SQL Server – Table Inserts – Table Spool [Eager Spool] – Explained
- A case of avoiding eager spool
- T-SQL Takes much longer
- Query Performance and Plan Cache Issues when parameter length not specified correctly