As a quick follow-up to our last post on pruning data based on Date, here is how the underlying indexes impact the Query Plan.
CREATE NONCLUSTERED INDEX [STSDHSMVSdvLog_created_by_ix] ON [FLDHSMVTest].[STSDHSMVSrvLog] ( [record_created] ASC ) ON [PRIMARY]
Find Minimal Out of Date Range ID
To get the minimal ID that we can safely prune below, we run the query below
select @idPKCutoff = min( tblL.[ID] ) from [FLDHSMVTest].[STSDHSMVSrvLog] tblL where tblL.record_created > @dateCutoff
To speed up the query we added a new index.
The included column, ID, serves as a covering index.
CREATE NONCLUSTERED INDEX [INDX_DBA_RecordCreated] ON [FLDHSMVTest].[STSDHSMVSrvLog] ( [record_created] ASC ) INCLUDE ( [ID] ) WITH ( FILLFACTOR = 100 ) ON [PRIMARY]
I really like it when I can expressly state that an Index Fill Factor is 100%; as is the case for our dateAdded index.
Post Adding new index – INDX_DBA_RecordCreated
Zoom In to Table Spool – Impacted Delete
We see that the Table Spool is traced back to removing data from one of the Indexes.
- The impacted index, INDX_DBA_RecordCreated was added as a covering index, record_created +ID
- The existing index, STSDHSMVSdvLog_created_by_ix, only has record_created
Disable Index – STSDHSMVSdvLog_created_by_ix
Let us disable the non-covering index, STSDHSMVSdvLog_created_by_ix
alter index [STSDHSMVSdvLog_created_by_ix] on [FLDHSMVTest].[STSDHSMVSrvLog] DISABLE;
Post Disabling Index – STSDHSMVSdvLog_created_by_ix
- Table Spools
- When we experience Spools, we see Worktable references of 2 Scan counts, and 2025 Logical Reads
- Actual Table
- Date Cutoff
- When index exists, Logical reads of 10219
- When Index disabled, Logical read of 7148
- 30% less expensive when Index Disabled
- ID Cutoff
- When index exists, Logical Reads of 6635
- When index disabled, Logical Reads of 3564
- 50% less expensive when Index Disabled
- Date Cutoff
Once we disabled the no longer needed index, we rid ourselves of the Table Spool.