sql server - Multiple Table Spools (Eager spools) during index inserts -

i performing simple insert of few hundred rows, e.g.:

insert foo select * bar 

the table has handful of secondary indexes. these indexes disabled query runs instantly. secondary indexes enabled, query takes seconds run, relatively high subtree cost.

the issue every secondary index, database performs:

• physical operation: table spool
• logical operation: lazy spool

where caches:

• all columns in destiation table (when needs values needs)
• multiple times values (rather once)

while may interesting know why sql server (2008 r2 sp2) thinks needs this, need way make inserting 100 rows in live server not take 6 seconds.

the really, really, horrible part every every table spool, sql server caches value of every column, every time:

which burning logical io.

• without these problematic index updates, complete import of 60,000 rows happens in second or two
• with these indexes, complete import takes literally dozens of minute

steps reproduce

of course, real auditlog table contains 4m rows. can reproduce exact same operators, high subtree cost, using empty auditlog table:

create table [dbo].[auditlog](     [auditlogid] [int] identity(216,1) not null,     [changedate] [datetime] not null constraint [df_auditlog_changedate]  default (getdate()),     [rowguid] [uniqueidentifier] not null,     [changetype] [varchar](50) not null,     [tablename] [varchar](128) not null,     [fieldname] [varchar](128) not null,     [oldvalue] [varchar](max) null,     [newvalue] [varchar](max) null,     [systemuser] [varchar](128) null constraint [df_auditlog_systemuser]  default (suser_sname()),     [username] [varchar](128) not null constraint [df_auditlog_username]  default (user_name()),     [hostname] [varchar](50) not null constraint [df_auditlog_hostname]  default (host_name()),     [appname] [varchar](128) null constraint [df_auditlog_appname]  default (app_name()),     [userguid] [uniqueidentifier] null,     [tagguid] [uniqueidentifier] null,     [tag] [varchar](max) null,     [timestamp] [timestamp] not null,     constraint [pk_auditlog] primary key clustered ([auditlogid] asc) ) 

and have painful indexes:

set ansi_padding off go /****** object:  index [ix_auditlog_changedate]    script date: 11/17/2016 2:58:43 pm ******/ create nonclustered index [ix_auditlog_changedate] on [dbo].[auditlog] (     [changedate] asc )with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [primary] go set ansi_padding on  go /****** object:  index [ix_auditlog_fieldname]    script date: 11/17/2016 2:58:43 pm ******/ create nonclustered index [ix_auditlog_fieldname] on [dbo].[auditlog] (     [fieldname] asc )with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [primary] go set ansi_padding on  go /****** object:  index [ix_auditlog_lastrowactionbytable]    script date: 11/17/2016 2:58:43 pm ******/ create nonclustered index [ix_auditlog_lastrowactionbytable] on [dbo].[auditlog] (     [tablename] asc,     [changetype] asc,     [rowguid] asc,     [userguid] asc )with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [primary] go /****** object:  index [ix_auditlog_rowguid]    script date: 11/17/2016 2:58:43 pm ******/ create nonclustered index [ix_auditlog_rowguid] on [dbo].[auditlog] (     [rowguid] asc )with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [primary] go set ansi_padding on  go /****** object:  index [ix_auditlog_rowinsertedbyuserguid]    script date: 11/17/2016 2:58:43 pm ******/ create nonclustered index [ix_auditlog_rowinsertedbyuserguid] on [dbo].[auditlog] (     [changetype] asc,     [rowguid] asc,     [userguid] asc )with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [primary] go /****** object:  index [ix_auditlog_rowlastmodifiedbyuserguid]    script date: 11/17/2016 2:58:43 pm ******/ create nonclustered index [ix_auditlog_rowlastmodifiedbyuserguid] on [dbo].[auditlog] (     [rowguid] asc,     [changedate] asc,     [userguid] asc )with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [primary] go set ansi_padding on  go /****** object:  index [ix_auditlog_tablename]    script date: 11/17/2016 2:58:43 pm ******/ create nonclustered index [ix_auditlog_tablename] on [dbo].[auditlog] (     [tablename] asc )with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [primary] go /****** object:  index [ix_auditlog_tagguid]    script date: 11/17/2016 2:58:43 pm ******/ create nonclustered index [ix_auditlog_tagguid] on [dbo].[auditlog] (     [tagguid] asc,     [rowguid] asc )with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [primary] go /****** object:  index [ix_auditlog_userguid]    script date: 11/17/2016 2:58:43 pm ******/ create nonclustered index [ix_auditlog_userguid] on [dbo].[auditlog] (     [changedate] asc,     [userguid] asc )with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [primary] 

and create our insert:

insert auditlog(             rowguid,              changetype,              userguid,              tablename,              fieldname,              tagguid,              tag)  select         'e5e31edd-7d39-47fd-bcff-4b7044ac433d',         'inserted',         '4a2fdacd-0209-403b-adbc-1b8a68e90350', --userguid         'customers', --tablename         '', --fieldname         '7a74267d-64f9-44d7-a1d7-1490a66136bf', --tagguid         'contoso' (     --a dummy derived table lets select above row 100 times     select top 400 (a.number * 256) + b.number number     (         select number master..spt_values type = 'p' , number <= 255) (number),         (select number master..spt_values type = 'p' , number <= 255) b (number) ) dt 

wait times ask?

| wait type      | wait time (s) | wait count | |----------------|---------------|------------| | io_completion  |        4.55 s |        211 | | writelog       |        0.79 s |         37 |  | pageiolatch_up |        0.36 s |          1 | | pagelatch_up   |        0.09 s |          2 | | pageiolatch_ex |        0.07 s |          4 | 

4.55s of 6s execution in io_completion:

occurs while waiting i/o operations complete. wait type represents non-data page i/os. data page i/o completion waits appear pageiolatch_* waits.

non-redundant indexes say?

| index name                            | columns                                  | index entry size         | |---------------------------------------|------------------------------------------|--------------------------| | ix_auditlog_changedate                | changedate                               | 12 bytes per entry       | | ix_auditlog_userguid                  | changedate, userguid                     | 28 bytes per entry       | | ix_auditlog_fieldname                 | fieldname                                |  4 bytes per entry (avg) | | ix_auditlog_tablename                 | tablename                                | 13 bytes per entry (avg) | | ix_auditlog_lastrowactionbytable      | tablename, changetype, rowguid, userguid | 52 bytes per entry (avg) | | ix_auditlog_rowguid                   | rowguid                                  | 20 bytes per entry       | | ix_auditlog_rowlastmodifiedbyuserguid | rowguid, changedate, userguid            | 44 bytes per entry       | | ix_auditlog_rowinsertedbyuserguid     | changetype, rowguid, userguid            | 43 bytes per entry (avg) | | ix_auditlog_tagguid                   | tagguid, rowguid                         | 36 bytes per entry       | 

no sort warnings

sql server profiler results batch

• duration: 7,401 ms
• reads: 233,597
• writes: 17,077
• cpu: 1,141 ms

no sort warnings. nor there attention, bitmap warning, execution warning, hash warning, missing column statistics, missing join predicate, sort warning, user error message.

indexes rebuilt. statistics updated.

you have problem overlaping , redundant indexes.

this qry help: t-sql finding redundant indexes