CODEWITHSUNDEEP
sql-server
Shawn
Shawn

Reputation: 5260

sql server checkpoint concurrency

I have 2 sessions, each performs the same tasks but on different tables, as follows

begin tran
 update...set...
commit tran
checkpoint

Each update is a large batch. The database is under simple recovery mode. To save t-log from growing too large we issue checkpoint so that t-log truncation can happen.

My question is: If session A committed the transaction and issued a checkpoint while session B is still in the process of updating will the checkpoint issue by session A wait on session B due to session B's active transaction? In other words would a checkpoint have to wait for all active transaction to finish? How likely is it for the two sessions to form a deadlock?

Also if two checkpoint commands are issued at the same time what will happen?

Note that the session A updates table_A and session B updates table_B. They never update the same table at any given time.

Also I know that using insert into, rename, drop can achieve faster update. But I am limited not to do so. I just want to know about checkpoint concurrency.

Thanks,

Upvotes: 1

Views: 362

Answers (2)

usr
usr

Reputation: 171206

Checkpoints have absolutely no relation to the data in the database. They do not cause data changes or changes in visibility.

You are likely degrading performance considerably.

Also, it is unlikely that this solves your log problems because SQL Server by default checkpoints regularly anyway. Learn about the log a little more and you'll surely find a better way to address that. Or ask a question about your log problems.

Upvotes: 0

Monty Wild
Monty Wild

Reputation: 4001

A manual Checkpoint simply tells SQL Server to write in-memory changes to disk. It should have no effect on the size of the log.

If Session A commits and checkpoints while Session B is in a transaction on a different table, these are unrelated events - the Session A checkpoint will go ahead, as will the Session B transaction. Since a manual checkpoint simply forces a write of the in-memory data to disk at a time of the programmer's choosing rather than at a time of SQL Server's choosing, the only perceptible consequence should be slightly degraded performance.

Since checkpoints take effect at the database level, concurrent Checkpoints should have the same effect as one Checkpoint.

Upvotes: 1

Related Questions