Transaction level difference between using a large IN filter VS. "BEGIN TRAN/COMMIT"

Question

I would like to delete about 100,000 records with minimal server overhead. I've had a few nagging questions I haven't been able to test properly so I figured I'd ask some experts here. Which would be better:

1-

BEGIN TRAN
DELETE FROM dbo.x
WHERE ID IN (
1
,2
,3
,4
...
,100000
)
COMMIT

2-

BEGIN TRAN
DELETE FROM dbo.x
WHERE ID = 1
GO
DELETE FROM dbo.x
WHERE ID = 2
GO
...
COMMIT 

3-

DELETE FROM dbo.x
WHERE ID = 1
GO
DELETE FROM dbo.x
WHERE ID = 2
GO
...

My assumption is that #1 would cause a scan based on sample %, pick out the items, remove them, and log it as a series of transactions. Perhaps it would base the info in the transaction log as to what pages were changed instead of each indvl. transaction, thus you can only rollback the entire activity and remark the pages, or you could commit. Is that correct?

On #2, I'm thinking does the GO statement cause a lot more transaction log activity by not allowing the SQL Server Storage Engine to roll all of these into 1 large transaction, but still provide some optimization for the transaction log with the BEGIN TRAN - COMMIT blocks, thus making it more effective than #3 but less effective than #1.

I then would assume #3 would be the worst one as each indvl. transaction is logged.

If anyone has any good blog posts or test scenarios they could point me to that would be great too. I've looked at ways to dig deep in the transaction log to figure it out myself but at this point, I thought I would ask you guys.

Thanks!

Answer 1

From a logging point of view 1 and 2 will be about the same as in both cases you are doing all the deletes within a single transaction. There will be lots of locking and probably blocking while the delete is being run. #3 is a single transaction per batch, so users won't be impacted very much and you'll have lots of small transactions in the transaction log instead of one large one. #2 and #3 should take about the same amount of time to run. #1 should take less time than #2 and #3 because it's just one command, however the run time will probably still cause problems.

I'd probably want to do something like this. This will minimize the locking and blocking that needs to happen by only dealing with 1000 rows at a time.

SELECT NULL
WHILE @@ROWCOUNT <> 0
BEGIN
  DELETE TOP (1000) FROM dbo.x
  WHERE ID IN ( /* if possible use "BETWEEN 1 and 100000" instead*/
  1
  ,2
  ,3
  ,4
  ...
  ,100000
  )
END