Home Dashboard Directory Help
Search

Simple Transact SQL Insert Statement causing performance killing Table Spool\Eager Spool Error by Daniel Adeniji


Status: 

Active


1
1
Sign in
to vote
Type: Bug
ID: 781879
Opened: 3/21/2013 4:19:19 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description




Simple Transact SQL Insert Statement causing performance killing Table Spool\Eager Spool Error

It appears that when multiple indexes appear in a table and some of the indexes are partitioned while others are not, inserts are relatively much slower.

We traced the problem back to the fact that some of the indexes are partitioned and some are not.


Tested in MS SQL Server - v2008/R2 :

MS SQL Server 2008 R2 SP1 - 10.50.2500 (X64)
Jan 17 2011 00:54:03


====================================================

I have attached the corresponding MS SQL Server Transact-SQL Files.
Details
Sign in to post a comment.
Posted by Microsoft on 4/29/2013 at 1:47 PM
Hello,

Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that are by design. The reason is because if the clustered index is partitioned one way, and a non-clustered index partitioned another way, we can’t maintain the non-clustered index together with the clustered, hence the separate branch and the spool. Based on his blog, he seems to have figured this out on his own eventually.

Thanks again for reporting the product issue and continued support in improving our product.
Gus Apostol, SQL Server Program Manager
Posted by Microsoft on 4/29/2013 at 1:45 PM
Hello,

Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that are by design. The reason is because if the clustered index is partitioned one way, and a non-clustered index partitioned another way, we can’t maintain the non-clustered index together with the clustered, hence the separate branch and the spool. Based on his blog, he seems to have figured this out on his own eventually.

Thanks again for reporting the product issue and continued support in improving our product.
Gus Apostol, SQL Server Program Manager
Posted by Daniel Adeniji on 4/27/2013 at 10:54 AM
Reproduced on MS SQL Server v2012 and updated:

Microsoft – SQL Server – Table Inserts – Table Spool [Eager Spool] – Is misaligned partitioning really the issue?

http://danieladeniji.wordpress.com/2013/03/29/microsoft-sql-server-table-inserts-table-spool-eager-spool-explained-is-misaligned-partitioning-really-the-issue/
Posted by Daniel Adeniji on 3/29/2013 at 8:47 AM
Ammended post:

http://danieladeniji.wordpress.com/2013/03/29/microsoft-sql-server-table-inserts-table-spool-eager-spool-explained-is-misaligned-partitioning-really-the-issue/
Posted by Daniel Adeniji on 3/22/2013 at 9:32 AM
To allow quick error communication, I have posted a couple of blog entries; they are:

1) http://danieladeniji.wordpress.com/2013/03/20/microsoft-sql-server-insert-into-big-tables-are-slow/
2) http://danieladeniji.wordpress.com/2013/03/22/microsoft-sql-server-table-inserts-table-spool-eager-spool-explained/

I will like for better clarity as to all underlying reasons for the slowness.
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
reviewIndexes.sql 3/21/2013 95 bytes
insert.sql 3/21/2013 312 bytes
dbo.customerRevised.Table.sql 3/21/2013 2 KB
PartitionScheme.sql 3/21/2013 1 KB
dbo.customers.Table.sql 3/21/2013 4 KB
TableSpool.zip 4/27/2013 55 KB