Revisiting the transaction batch size

re: Revisiting the transaction batch size

Uri Dimant — Sun, 01 Aug 2010 05:26:18 GMT

Hi Linci

You said that you eliminated loop so what is your T-SQL statement?

WHILE 1 = 1

BEGIN

DELETE TOP(100000)

FROM Foo

WHERE <range on the cikey >

IF @@ROWCOUNT < 2000 BREAK;

END

re: Revisiting the transaction batch size

Uri Dimant — Sun, 01 Aug 2010 05:26:20 GMT

Hi Linci

You said that you eliminated loop so what is your T-SQL statement?

WHILE 1 = 1

BEGIN

DELETE TOP(100000)

FROM Foo

WHERE <range on the cikey >

IF @@ROWCOUNT < 2000 BREAK;

END

re: Revisiting the transaction batch size

Chris Howarth — Sun, 01 Aug 2010 06:25:40 GMT

Don't forget that log files can't be instantly initialised during creation, so if there is a requirement to keep database restore time (and therefore down time) to a minimum, then, during the restore process a 50GB file will, obviously, take a lot longer to create than, say, a 5GB file.

Just one thing to consider before taking this approach.

Chris

re: Revisiting the transaction batch size

Adam Machanic — Mon, 02 Aug 2010 20:55:55 GMT

Hi Linchi,

I think more testing is in order. Even with plenty of disk space I've had great success in using smaller batch sizes to significantly speed up large operations... I'm not sure what all of the determining factors are--it would be very interesting to figure out a set of best practices for this kind of thing.