May 20, 2014 at 6:14 pm
Hi,
I would like to archive /delete data from a 100GB table. I have to delete on the basis of date column. Date column has been added to clustered index But not having an individual non clustered index.
My estimated execution plan shows a index scan.
Should I impose an non-clustered index on the date column then try to archive /delete after confirming the index seek is used in estimated execution plan or, is there any other method to do this?
Thanks,
Ami
May 21, 2014 at 7:22 am
Is the date column the first column in the clustered index? If not, then you will have a scan. If it is the first column in the clustered index, then you just need to break the delete into several smaller chunks, so the index will kick in. Indexes only tend to kick in at about 4% selectivity, if I recall.
May 21, 2014 at 7:23 am
Matt Crowley (5/21/2014)
Indexes only tend to kick in at about 4% selectivity, if I recall.
~0.5%, unless the index is covering (which a clustered index always is), in which case anything up to 100%.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 21, 2014 at 7:57 am
Are you using the EXACT same data type in your query as the column has?
Please post the table definition (including all indexes) and the code you are using for the DELETE.
If you are using the correct data type and the proper WHERE clause the query should do a seekable delete that I would expect to be very efficient.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply