Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a very large table in a SQL Server 2008 R2 Standard server which I am archiving rows by copying them into another database on a separate disk and then deleting them from the original table using an SSIS data flow. The table has a bigint Primary Key and rows are being deleted in numerical order of this key. As I am deleting rows, however, the overall Index size of the table is steadily increasing, and I cannot figure out why. The data size is remaining the same throughout this process.


Here are the table details:

Row Count: ~300,000,000
Data Size: ~65 GB
Index Size: ~65 GB

Rows are being deleted out at a rate of ~350,000 per hour.

The column definitions use the following data types: smallint, int, bigint, char, varchar, nvarchar, uniqueidentifier, bit, datetime

The table has one Primary Key which is the Clustered key on the table as well as 4 non-clustered indexes.

The table is also part of a replication maintenance plan. The replicated copy of the table is decreasing in both Data Size and Index Size!


I performed the same process on another table last week and I could see both the Data Size and Index Size of that table decreasing as I deleted rows.

Is there any explanation as to why the Index Size keeps going up in this case?

share|improve this question
    
How many indexes do you have on the table? –  JNK Feb 25 at 17:32
    
The table has 4 indexes plus the PK. –  aaroncatlin Feb 25 at 17:33
    
Is the PK the clustered index? –  JNK Feb 25 at 17:34
1  
It sounds like a ghost record issue to me, but someone smarter can likely give you more detail. –  JNK Feb 25 at 17:35
1  
@aaroncatlin how and when are you checking the size? are you looking at windows explorer? sp_spaceused? etc? –  swasheck Feb 25 at 17:58

1 Answer 1

up vote 8 down vote accepted

Alright so I believe you doing and INSERT is increasing the size then after you do your OLEDB command which is sending hundreds of thousands of DELETE commands to SQL Server is what's causing your problem. So here is a short Demo/Tutorial on how to do this DELETE operation more effectively. Even if this doesn't solve your index problem it will make your package run more efficiently.

STEP 1: Build a staging table for this information.

You're going to want to CREATE a table that's sole purpose is to hold the columns necessary for the delete. Whatever makes up your Primary Key will do.

STEP 2: Truncate the Staging table.

Create Execute SQL Task

It's important to truncate first as this will all for re-run if the package fails.

Edit SQL Task

Step 3: Data Flow task.

Next add a Data Flow Task and open.

Add DFT

If you are doing your archive elsewhere than disregard the Multicast and the Archive destination in the next image.

DFT Instructions

STEP 3: Delete

Delete

Delete Statement

That should give you a pretty good idea of how to do this if you have any other question feel free to ask.

The reason this will be more efficient is because you are then doing set based operations instead of sending 350K DELETE commands to SQL server.

share|improve this answer
    
Thanks for this. The problem I can see with the Multicast is that it's not necessarily allowing the row to be inserted before it gets deleted. In fact, I had a "Communication Link Error" occur to the Archive DB whilst archiving overnight which would have meant that I would have lost records. Would it make much of a difference if I moved the Delete Staging Table part of the flow after the Archive part and removed the MultiCast? –  aaroncatlin Feb 26 at 9:26
1  
I've also marked this as the answer as it is definitely helpful and very well put together. Thanks for going to this effort. (I know these sorts of comments are technically against T's & C's, but thought it needed to be said!) –  aaroncatlin Feb 26 at 9:30
    
One final note, that when I received the Communication Link Error last night, the table was obviously given chance for the Ghost Record Cleanup to occur and the Data Size has now dropped by 5 GB. –  aaroncatlin Feb 26 at 9:32
    
-1, no biml provided –  billinkc Feb 26 at 16:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.