Geek Answers Handbook

Removing sql server is slowed down by indexes

I am running a script archive that deletes rows from a large (~ 50 m DB record) depending on the date they were entered. The date field is a clustered index in the table, and so I apply my conditional operator to.

I run this deletion in a while loop, trying from 1,000 to 100,000 entries in a batch. Regardless of batch size, it is surprisingly slow; approximately 10,000 records are deleted per minute. Considering the execution plan, there is a lot of time spent on "Deleting an Index." There are about 15 fields in the table, and about 10 of them have some kind of index for them. Is there any way around this problem? I’m not even sure why it takes so long to remove each index, can someone shed some light on what is happening here? This is an example of my execution plan:

alt text http://img94.imageshack.us/img94/1006/indexdelete.png

(The sequence indicates the Delete command)

This database works live and is often included, so I hesitate to use the crop and crop method. Are there any other features that I miss here?

+5
source share
5 answers

Removing 10k entries from a clustered index + 5 non-clustered entries should not necessarily be 1 minute. It looks like you really have a very slow IO subsystem. What are the values ​​for:

  • Avg. Disk sec / write
  • Avg. Disk sec / read
  • Avg. Recording Queue Duration
  • Avg. Disk Read Queue Length

, ( Log!). LUN , , . , . SQL Server , , . , -, -, .

-, , . , , , . , ( ...). , . , , sys.dm_db_index_usage_stats , - .

-, . , , - ( ).

, , , .

+6

, 5 .

- 5 .

, . , ? (10x) ? , .

, , . . , , .

+3

, @NickLarsen . , . , , .

- , , . , . Microsoft SQL Server, .

+2

, IsDeleted 1, ? SELECTs UPDATEs, .

.

+1

, SQL Server 2005/2008, , . " " /.

Unfortunately, with a table in production, transferring it to this technology, it will take some coding, knowledge and output for T-SQL to update / transfer it. If in place, although any existing options and inserts will work against it without problems, maintaining and adding / removing partitions is where you need t-sql to control the process.

+1
source

More articles:


All Articles