Microsoft – SQL Server – Table Inserts – Table Spool [Eager Spool] – Explained

Background

In an earlier post, Slow Inserts into Big tables (https://danieladeniji.wordpress.com/2013/03/20/microsoft-sql-server-insert-into-big-tables-are-slow/), I noted how we traced slow system performance to a Table Spool \ Eager Spool.

Introduction

To seed community thoughts and participation, I will like to provide a stringent example that describes the same problem.

For our particular case, let us set up a strict scenario:

  • A partitioned table
  • A few indexes

Set up Lab

Create Partition Function & Scheme


use [tempdb]
go

if not exists   
                (

                    select *
                    from   sys.partition_functions
                    where  name = 'pfnYear'

                )
begin

    print 'Creating partition function pfnYear(smallint) ...'

    create partition function pfnYear(smallint)
    as range right for values
    (
          2011
        , 2012
        , 2013
        , 2014
        , 2015
    )

    print 'Created partition function pfnYear(smallint) '

end

go


 

Create Partition Scheme

Create Partition Scheme


if not exists   
                (
                    select *
                    from   sys.partition_schemes
                    where  name = 'partitionSchemeYear'
               )
begin

    print 'Creating partition scheme partitionSchemeYear  ...'

        create partition scheme partitionSchemeYear 
        as partition pfnYear
        ALL TO ([default])

    print 'Created partition scheme partitionSchemeYear'

end

go

 

Create Table and Indexes


use [tempdb]
go

if OBJECT_ID('dbo.customer') is not null
begin
    drop table [dbo].[customer] 
end    
go	

CREATE TABLE [dbo].[customer]
(
	[customerID] [varchar](30) NOT NULL,
	[regionName] [varchar](50) NULL,
	[InsertTime] [datetime] 
         constraint defaultCustomerInsertTime DEFAULT (getutcdate()) NULL,

	[salesDate] [datetime] not null 
          constraint defaultCustomerSalesDate DEFAULT (getutcdate()),
	[SalesYear] as 
          cast(isNull(datepart(year,[salesDate]), 1990) as smallint) 
              persisted not null,

	CONSTRAINT [PK_customer] PRIMARY KEY NONCLUSTERED 
		(
			[customerID] ASC
		      , [salesYear]
		)
		WITH 
                (
		        PAD_INDEX  = OFF
	              , STATISTICS_NORECOMPUTE  = OFF
		      , IGNORE_DUP_KEY = OFF
		      , ALLOW_ROW_LOCKS  = ON
		      , ALLOW_PAGE_LOCKS  = ON
		      , FILLFACTOR = 50
               ) 
	   ON [partitionSchemeYear]([SalesYear])
) 
ON [partitionSchemeYear]([SalesYear])

GO

CREATE CLUSTERED INDEX [idx_pfnYear_SalesYear__customerID] 
ON [dbo].[customer] 
(
	[salesYear] ASC,
	[customerID] ASC
)
WITH 
(
      PAD_INDEX  = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , IGNORE_DUP_KEY = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS  = ON
    , ALLOW_PAGE_LOCKS  = ON
    , FILLFACTOR = 50
) 
ON [partitionSchemeYear]([SalesYear])
GO

CREATE NONCLUSTERED INDEX [idx_InsertTime] 
ON [dbo].[customer] 
    (
        [InsertTime] ASC
    )
    INCLUDE ( [customerID]) 
    WITH (

              ONLINE = ON
            , ALLOW_ROW_LOCKS  = ON
            , ALLOW_PAGE_LOCKS  = ON
          ) 
          ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [idx_Region] 
ON [dbo].[customer] 
    (
        [regionName] ASC
    )
    INCLUDE ( [customerID]) 
    WITH (

              ONLINE = ON
            , ALLOW_ROW_LOCKS  = ON
            , ALLOW_PAGE_LOCKS  = ON
          ) 
          ON [PRIMARY]

GO

 

When we ran the insert statement and looked deeper this is what we saw:

Execution Plan - dbo.Customer {Expensive Plan -- 20130321__1057PM}

Explanation:

  • After the Clustered Index Insert, there are two Table Spool (Eager Spool) operators
  • Post each Table Spool (Eager Spool), we have the Index Insert operation

Statistics IO

Statistics IO -- 2013-03-21 at 11.04.51 PM

Explanation:

  • We have the actual table insert
  • And, an unexplained “Worktable” entry

To fix:

Revised Table

Here is our strategy

  • Align all indexes within the same partition

 

Here is our SQL:



use [tempdb]
go

if OBJECT_ID('dbo.customerRevised') is not null
begin
    drop table [dbo].[customerRevised] 
end    
go	

CREATE TABLE [dbo].[customerRevised]
(

    [customerID] [varchar](30) NOT NULL,

    [regionName] [varchar](50) NULL,
    [InsertTime] [datetime] 
        CONSTRAINT defaultcustomerRevisedInsertTime 
           DEFAULT (getutcdate()) NULL,
    [salesDate] [datetime] not null 
         constraint defaultcustomerRevisedSalesDate 
          DEFAULT (getutcdate()),
    [SalesYear] as 
          cast(isNull(datepart(year,[salesDate]), 1990) as smallint) 
            persisted not null,

	CONSTRAINT [PK_customerRevised] PRIMARY KEY NONCLUSTERED 
	(
	     [customerID] ASC
	   , [salesYear]
	)
	WITH (
		     PAD_INDEX  = OFF
		   , STATISTICS_NORECOMPUTE  = OFF
		   , IGNORE_DUP_KEY = OFF
		   , ALLOW_ROW_LOCKS  = ON
		   , ALLOW_PAGE_LOCKS  = ON
		   , FILLFACTOR = 50
            ) 
	ON [partitionSchemeYear]([SalesYear])
) ON [partitionSchemeYear]([SalesYear])

CREATE CLUSTERED INDEX [idx_pfnYear_SalesYear__customerID] 
ON [dbo].[customerRevised] 
(
	[salesYear] ASC,
	[customerID] ASC
)
WITH 
(
      PAD_INDEX  = OFF
    , STATISTICS_NORECOMPUTE  = OFF
    , SORT_IN_TEMPDB = OFF
    , IGNORE_DUP_KEY = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS  = ON
    , ALLOW_PAGE_LOCKS  = ON
    , FILLFACTOR = 50
) 
ON [partitionSchemeYear]([SalesYear])
GO

CREATE NONCLUSTERED INDEX [idx_InsertTime] 
ON [dbo].[customerRevised] 
    (
        [InsertTime] ASC
    )
    INCLUDE ( [customerID]) 
    WITH (

              ONLINE = ON
            , ALLOW_ROW_LOCKS  = ON
            , ALLOW_PAGE_LOCKS  = ON
          ) 
          --ON [PRIMARY]
          ON [partitionSchemeYear]([SalesYear])

GO

CREATE NONCLUSTERED INDEX [idx_Region] 
ON [dbo].[customerRevised] 
    (
        [regionName] ASC
    )
    INCLUDE ( [customerID]) 
    WITH (

              ONLINE = ON
            , ALLOW_ROW_LOCKS  = ON
            , ALLOW_PAGE_LOCKS  = ON
          ) 
          --ON [PRIMARY]
          ON [partitionSchemeYear]([SalesYear])
GO


 

SQL – Insert into Revised Table


truncate table [dbo].[customerRevised]
go

insert into [dbo].[customerRevised]
(
    [customerID], [salesDate]
)
values
(
    'B', GETUTCDATE()
)

go

Execution Plan – Revised Table

 

Execution Plan -- {20130321__1127PM}

 

Explanation:

With all of indexes aligned on the same partition, here is what we notice:

  • Our query is now 43% of the full 100%; while the original query is at 57%
  • We lost the ‘heavy’ weight of the Table Spool (Eager Spool)
  • All of our index maintenance is occurring within the same operation

Clustered Index Insert {Original}:

Clustered Index Insert -- 2013-03-21 at 11.35.40 PM

Clustered Index Insert {Revised}:

Clustered Index Insert {Revised}

Explanation — Clustered Index:

Original Design

  • In our original query, visibly within the ‘Object’ section, we performed the two index operations that are targeting the same clustered key partition
  • We carried those two completed indexes forward (along with the indexes targeting the unpartitioned default filegroup)


Revised Design

  • All index maintenance is performed within the same operation

Here is our Statistics I/O:

Statistics IO (Revised)

  • We shrugged off the weight of the work-table I/O access

 

Summary

Like the old song says “It is 5:00 somewhere” ( Alan Jackson & Jimmy Buffett — It’s 5 ‘o’Clock Somewhere – http://www.youtube.com/watch?v=X_7wdpGvTms ), and so it is time to go.

But, to quickly review indexes on a table and see how they are aligned, issue sp_helpindex <table-name>:

 

sp_helpindex

You want to look in the “Index Description” column and ensure that they are defined under PRIMARY or the same partition scheme.

 

References:

Leave a Reply

Please log in using one of these methods to post your comment:

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