Transact SQL – Prune Data based on Date – Material Effect of Indexes

Background

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.

Schema

Existing Index


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


Added Index

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]

Explanation

I really like it when I can expressly state that an Index Fill Factor is 100%; as is the case for our dateAdded index.

 

Instrumentation

Post Adding new index – INDX_DBA_RecordCreated

Index Info

QueryIndexInfo

Query Plan

Entire

QueryPlan-Before

 

Zoom In to Table Spool – Impacted Delete

TableSpool-Inpacted-IndexDelete

 

Statistics I/O

StatisticsIO-Before

 

Diagnosis

We see that the Table Spool is traced back to removing data from one of the Indexes.

Review Index

QuickReviewOfIndex

Review Index

  1. The impacted index, INDX_DBA_RecordCreated was added as a covering index, record_created +ID
  2. The existing index, STSDHSMVSdvLog_created_by_ix, only has record_created

Disable Index

Disable Index – STSDHSMVSdvLog_created_by_ix

Let us disable the non-covering index, STSDHSMVSdvLog_created_by_ix

Code


     alter index [STSDHSMVSdvLog_created_by_ix]
			on [FLDHSMVTest].[STSDHSMVSrvLog] DISABLE;

 

Instrumentation

Post Disabling Index – STSDHSMVSdvLog_created_by_ix

Index Info

sp_helpIndexInfo

 

Query Plan

QueryPlan-After

Statistics I/O

StatisticsIO-After

 

Compare

Statistics I/O

 

Metric Table Index Exists Index Disabled
 Pruning based on date cutoff STSDHSMVSrvLog  Scan count 1, logical reads 10219 (Delete )  Scan count 1, logical reads 7148
Worktable  Scan count 2, logical reads 2025 ( Delete )
 Pruning based on ID cutoff STSDHSMVSrvLog Scan count 1, logical reads 1132 ( Find Minimal ID)
Scan count 1, logical reads 6635 ( Delete )
 Scan count 1, logical reads 1131 ( Find Minimal ID )
Scan count 1, logical reads 3564 ( Delete )
Worktable Scan count 2, logical reads 2025  ( Delete )

 

Explanation

  1. Table Spools
    • When we experience Spools, we see Worktable references of 2 Scan counts, and 2025 Logical Reads
  2. 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

Version Impacted

This problem is isolated to certain MS SQL Server Versions.


select 
		  [version] = @@version
		, [edition] = serverproperty('edition')
		, [productLevel] = serverproperty('productLevel')
		, [productVersion] = serverproperty('productVersion')

Not Experienced

9.00.5069.00

SQLVersion-Experienced

9.00.5266.00

SQLVersion-Experienced-9-00-5266-00

Experienced

We did not experience on the following SQL Server Versions

SQLVersion-NotExperienced

Tabulated

 

Table Spool Version Edition Product Level Product Version
 Yes Microsoft SQL Server 2005 – 9.00.5069.00 (X64) Standard Edition (64-bit )  SP3  9.00.5069.00
Microsoft SQL Server 2005 – 9.00.5266.00 (X64) Standard Edition (64-bit )  Sp3  9.00.5266.00
 No Microsoft SQL Server 2005 – 9.00.5292.00 (x64) Standard Edition (64 bit ) SP4 9.00.5292.00

 

Summary

Once we disabled the no longer needed index, we rid ourselves of the Table Spool.

 

One thought on “Transact SQL – Prune Data based on Date – Material Effect of Indexes

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