question

neeehar avatar image
neeehar asked

Performance issues in sql deletes

I have to delete data from 7 large tables. Adding non-clustered indexes speed up the process but I was told that setting up indexes occupy a lot of space. So, do you think if its a best practice to create the non-clustered index and drop them after the delete operation is complete? My set up is SQL Development Server 2008 R2.
sql-server-2008delete
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I added non-clustered based on the warning message from query execution plan
0 Likes 0 ·

1 Answer

· Write an Answer
Tom Staab avatar image
Tom Staab answered
Before you start adding nonclustered indexes, you should make sure you have a good clustered index. A unique clustered is not required, but it is definitely preferred. In addition, it is also better if the clustered index key is a column that increases in value because the clustered index dictates the layout of the table data. For that reason, a surrogate key is often a good choice. Having said all of that, deleting data using clustered key criteria is the most efficient method. If that cannot be done in your scenario, you can make a nonclustered index as you mentioned. The storage space required for a nonclustered index is the size of the index key columns plus the size of the clustered key (because every nonclustered index must include the clustered index key as a way to access the rest of the table) plus any extra columns included in the nonclustered index. Try to use an existing clustered or nonclustered index if possible before creating a new one because every insert or delete operation must act on every index as well as any update that affects any columns in the index. I do not recommend creating the indexes just for the delete and then dropping them again unless this is a one-time event. If you need it once, you might need it again, so why make the database engine repeat the work of generating it? I hope that helps. Let us know if you have any additional questions.
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you Tom for responding with all details. I have a clustered index and use it in my query joins but when i saw the query execution plan it said that adding the missing non clustered index could improve the query cost by 96% and it did improve after I added the non clustered index. I had second thoughts after I read on some blog that its not wise to do the things the query analyzer suggests. Please advice if I should follow the query analyzer?
0 Likes 0 ·
There are so many "it depends" situations with SQL Server. I can't tell you certainly one way or the other whether or not to follow the analyzer. If it helped (and especially if this delete is something you will do regularly), I'd say keep it and monitor performance.
0 Likes 0 ·
Thank You Tom...
0 Likes 0 ·
I agree with pretty much everything @Tom has said. If that index helped the criteria for your delete statement, chances are pretty high that criteria is also in use in select statements. Leave the index in place and monitor see if maintaining it is adding too much overhead.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.