DelZeroSift

Administrator

DelZeroSift
A stored procedure to delete zero SIFT records.

It will delete records in small batches and only when the amount of zero SIFT records exceeds 10% of the total records. It will only run for a set amount of hours so it's easy to schedule and can therefore run during the maintenance window.

It will automatically rebuild indexes on sift tables after deletion and can be set up to make log backups during run.

I usually set it up to run daily for a couple of hours per night. It will then gradually minimize the number of zero entries in the database and after an initial hit keep them to a minimum.

http://www.mibuso.com/dlinfo.asp?FileID=1036

Discuss this download here.

stryk

Hi Throstur!

Thanks for sharing this! Even though I would be somewhat happier if you could have mentioned on the main download page that your procedure is based on my own one http://www.mibuso.com/dlinfo.asp?FileID=812 :whistle:

Anyway, I got some inspiration from that and I probably will enhance my own stuff according to that

Here my thoughts:

Even on very large database the runtime of this "Zero SIFT Deletion" usually is reasonable, e.g. 10 to 15 minutes on a 300GB db. If you run it frequently, the impact on the Transaction Log is not that big, so I'm not sure if it's feasible to fiddle with TLog backups during this process. I guess that would be necessary when running it for the first time where it might happen to delete millions of records then ...

I would not restrict the deletion to that "> 10% of all records" rule. Even "just" 10% of 0-SIFT recs could be a problem, depending on the total number of records. If you want to restrict this, I would preferably set an absolute threshold, e.g. if more than 100.000 0-SIFT recs are counted. But after all I would not restrict this at all ...

The ROWCOUNT limitation is nice - I'll check out the Execution Plans with/without (or could you give me some examples?!)

I also like the idea of instantly defragmenting the SIFT tables after clean-up, but I would suggest to make this optional, too, e.g. by providing a new parameter @with_defrag or something. The DBCC DBREINDEX could have remarkable impact in the table, processing would take longer, cause more locks and might cause high impact on the Transaction Log. I think it would be feasible if one could control if this DBCC should be executed ...

Again, thanks a lot for the inspiration!

Best regards & HAPPY NEW YEAR!
Jörg

sporri

HI Jörg

Sorry about not notifying in the post :oops: , I was not sure about the usual policies here so I decided to put your name in the comments instead. Please feel free to modify and incorporate any of the ideas I have. I'll modify this to make the index optimization optional and repost with a better credit description.

@rowcount loop comes from the same instance, big delete jobs in a single transaction get progressively slower when the log grows. By splitting the deletes into smaller transactions the overall time gets shorter and if you are doing frequent log backups you will not have a long transaction running through multiple log backups. And I needed to be able to run this in a relatively small maintainance window.

When deleting large number of records with a where clause the following statements usually execute faster than DELETE FROM TABLE WHERE BLABLA

On sql 2005.

@ROWCOUNT > 0
begin
DELETE TOP(5000) FROM WHERE BLABLA
End

SQL 2000 needs the:

declare @rowcount int
select @rowcount = 1
set ROWCOUNT 5000
@rowcount > 0
begin
DELETE FROM WHERE BLABLA
set @ROWCOUNT
end

The SET ROWCOUNT statement will not work on delete statements in the next version of sql server.

stryk

sporri wrote:

Sorry about not notifying in the post :oops: , I was not sure about the usual policies here so I decided to put your name in the comments instead. Please feel free to modify and incorporate any of the ideas I have. I'll modify this to make the index optimization optional and repost with a better credit description.
.

Don't worry about that - it's quite OK

sporri wrote:

@rowcount loop comes from the same instance, big delete jobs in a single transaction get progressively slower when the log grows. By splitting the deletes into smaller transactions the overall time gets shorter and if you are doing frequent log backups you will not have a long transaction running through multiple log backups. And I needed to be able to run this in a relatively small maintainance window.

When deleting large number of records with a where clause the following statements usually execute faster than DELETE FROM TABLE WHERE BLABLA

On sql 2005.

@ROWCOUNT > 0
begin
DELETE TOP(5000) FROM WHERE BLABLA
End

SQL 2000 needs the:

declare @rowcount int
select @rowcount = 1
set ROWCOUNT 5000
@rowcount > 0
begin
DELETE FROM WHERE BLABLA
set @ROWCOUNT
end

The SET ROWCOUNT statement will not work on delete statements in the next version of sql server.

Yes, I see. I guess this "deletion in smaller packages" is pretty good! I think I will include such a feature as well, and test it out! Again, thanks a lot for sharing your enhancements!

Best regards,
Jörg