Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
 Posted - 2004-10-20 : 05:00:57
|
| Having searched and read BOL, I'm still not much clearer. The only other hit on EAGER spool was not very forthcoming, so I am hoping that someone else can comment on this.I (obviously) also seeing some interesting EAGER SPOOL'ing in a delete statement:delete [FinRecon].[dbo].[gcmstransactions]from gcms..offline_file a inner join [FinRecon].[dbo].[gcmstransactions] b on b.offline_file_id = a.offline_file_idwhere a.datetime_created < '2003-09-19 00:00:00.000'With:CREATE TABLE [dbo].[GCMSTransactions] ( [id] [int] IDENTITY (1, 1) NOT NULL , [de2] [varchar] (19) NULL , [de6] [varchar] (12) NULL , [de3s1] [varchar] (2) NULL , [mti] [varchar] (4) NULL , [de24] [varchar] (3) NULL , [pds0025s1] [varchar] (1) NULL , [datetime_processed_iwv] [datetime] NULL , [msg_id_ica] [varchar] (33) NULL , [de12] [varchar] (12) NULL , [de43s1] [varchar] (83) NULL , [de43s2] [varchar] (83) NULL , [de43s3] [varchar] (83) NULL , [de43s4] [varchar] (10) NULL , [de43s5] [varchar] (3) NULL , [de43s6] [varchar] (3) NULL , [de25] [varchar] (4) NULL , [de95] [varchar] (10) NULL , [de42] [varchar] (15) NULL , [de38] [varchar] (6) NULL , [de41] [varchar] (8) NULL , [de30s1] [char] (12) NULL , [message_id] [int] NULL , [offline_file_id] [int] NULL , CONSTRAINT [PK_GCMSTransactions] PRIMARY KEY CLUSTERED ( [id] ) WITH FILLFACTOR = 90 ON [PRIMARY] )index_name index_description index_keysgcmstransactions_PIX1 nonclustered located on PRIMARY offline_file_idIX_Message_id nonclustered, unique located on PRIMARY message_idIX_msg_id_ica nonclustered located on PRIMARY msg_id_icaIX_mti nonclustered located on PRIMARY mtiPK_GCMSTransactions clustered, unique, primary key located on PRIMARY idWhat I am getting as an access path:--RG 20041019 - Access path:-- Cost of 140: 10% = Index Scan on GCMSTransactions.PK_GCMStransaction (cost of 14.4). 565K row on gcmstransactions.-- 2x 8% = Table Spool/Eager Spool (cost of 11.7)-- 3x sorts (25 % -cost 47: GCMSTransactions.Msg_id_ica asc, ), (--Adding: create index gcmstransactions_PIX1 on gcmstransactions (offline_file_id)--improved cost to 112. 1% = Index Scan on GCMSTransactions.gcmstransactions_PIX1 (cost 1.39)-- 2x 4% = Table Spool/Eager SpoolWonder what (if anything) I can do about the Eager Spool's ...*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here!*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
|
|
|
|
so... eager spooling ... any ideas?