In Comprehensive Database Performance Health Check, I often discuss that indexes can reduce the performance of your system big time. Many still think that indexes are good for a SELECT statement but that is not true and I have previously created a video on that topic as well as discussed it in depth during the health checks. Today we are going to see another interesting topic where the index affects the delete statement. Let us start watching the video Indexing for Delete.
Let me know what you think of this video about Indexing for Delete.
Here are my few recent videos and I would like to know what is your feedback about them. Do not forget to subscribe SQL in Sixty Seconds series. I hope you appreciate learning about the last few SQL in Sixty Seconds Video.
- Compressed Backup and Performance – SQL in Sixty Seconds #196
- Find Missing Identity – SQL in Sixty Seconds #195
- Remove Key Lookup – SQL in Sixty Seconds #194
- Using Stored Procedure in SELECT – SQL in Sixty Seconds #193
- Performance Troubles – Function on Date Variable – SQL in Sixty Seconds #192
- Performance: Between and Other Operators – SQL in Sixty Seconds #191
- Most Used Database Files – SQL in Sixty Seconds #190
Well, that’s it for today. Would you like me to build a video on this topic? If yes, let me know and I will build a video on this topic on my SQL in Sixty Seconds channel.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
2 Comments. Leave new
Nice video! I wonder if TRUNCATE TABLE would perform better with an index? Of course, this only applies if you want to delete ALL the rows of a table. TRUNCATE TABLE is usually faster than DELETE in that case because it doesn’t log anything. I might run some tests with TRUNCATE TABLE to see if there’s any performance gain if the table has a clustered index!
What are these _dta_ indexed? Can drop them? If a index where allow_row_locks and allow_page_locks are off, can I drop them?