I have realized something this morning. One of my developer who wrote a stored procedure to delete the records from a Table which contains Millions of records. He was trying to delete just the thousand of records and doing the DELETE procedure to run every morning. the script is simple script - DELETE FROM TABLE1 WHERE PERIOD = '201008' It took at least 10 mins to remove the records because of the size of the table. I am not going to blame him because I might do the same if I want to delete the records from the table. But my question is - 1. is there anyway to use different type of DELETE to remove the records quicker? 2. We are using Standard Version of SQL 2008 so that we can't partition the table. What is the best way to re-structure the table which contains Millions of records. (**Shall I create Tables by Period??) Thanks. 1.1.
How selective is the date 201008? You can work that out by doing: SELECT SUM(CASE WHEN PERIOD = '201008' THEN 1 ELSE 0 END)/COUNT(*) FROM TABLE_A As @Grant Fritchey says, you need to look at the execution plan to see if this index is being used. If 201008 is not selective enough, then it may be performing a table/clustered index scan to find the rows to delete. You may then experience a slow down. Does the table have multiple indexes on it that also need updating when the delete happen? **EDIT: After looking at the execution plan I have added the following:** @Leo - So, as far as I can decipher, you are getting index seeks on your nonclustered index and then table lookups against the table itself. This will be slow. I don't know what queries are run against this table, but you may want to consider changing that index to a clustered index. I am going to venture a guess that you may have other indexing issues on your system. The index here has been suggested and created by the Index Tuning Advisor. While not a bad thing in itself, it would lead me to believe there is some knowledge on indexing principals missing here. I recommend that you read up a little on the topic of indexing and SQL Server internals- [Kalen Delaney's SQL Server Internals] goes into depth on all aspects of SQL Server, including indexing and is ***excellent***. :
Make sure you have an index on the column that you use in the where clause and that the conditions is of the same datatype so you don't get any implicit conversions. 10 000 records in a delete is not that much. If you want to delete millions you should consider deleting in batches to prevent any deadlocks etc. Maybe you are experiencing an IO bottleneck. Take a look at the IO stalls with the following statement: SELECT * FROM sys.dm_io_virtual_file_stats(NULL,NULL); The IO read/write stalls / ms should be as low as possible. More than 15 ms is considered high.
Check the following 1. does your query can utilize the index. 2. check your fragmentation of indexes and updated statistics. 3. make sure that you don't have any waiting or blocked processes. Since you cant use partitioning. The best approach will be designing appropriate indexes.
You would need to look at the execution plan to determine what was going on. It could be as simple as @Hakan says, add an index. It might be more complicated if there are dependencies on that table that will require consistency checks against other tables. Do they all have indexes that the query can use as well? Get a look at the execution plan and it ought to give you some direction. The other question is, what percentage of the millions of rows are you deleting that is taking 10 minutes. Less than 1%, that's a problem. More than half, that would be expected. Sometimes, depending on the data volume involved, there's little that can be done. BTW, 5-10 million rows isn't that big a table. If it's well indexed, you ought to be able to get good performance out of it.
Tackling this from the other direction... how long does a SELECT of a given date take? If it's the same we can head down one avenue, if it's a magnitude faster we can go down another. A long SELECT points to a missing index, a quick one tends to suggest blocking during the DELETE. These are broad generalizations but will help us understand the issue better. And, as Grant et al have said - what is the execution plan?