Sarab (8/16/2009)
Ok, so if i want to commit all the changes at a rapid speed i can request a checkpoint with a small duration & SQL will put all the required resources to complete checkpoint within the requested time.
No, not at all.
You cannot control the duration of checkpoint. It runs for as long as it takes to write all dirty data pages to disk. SQL will write those pages as fast as it can, depending on the throughput of the IO subsystem.
But lets say i am changing the Recovery Interval(I know its not the best practice, i am just taking an example) to a duration of 90Secs then whether this setting will be applied for the checkpoint (which SQL runs at the time of startup) or the setting will be applied for each and every Checkpoint which happens at some specific intervals?
Recovery interval indirectly controls how often checkpoint runs.
By setting the recovery interval to 90 sec, you're saying to SQL run the checkpoint process often enough that should the SQL service restart unexpectedly, the duration of the restart recovery will not exceed 90 seconds.
The higher the restart interval is set, in general, the less often checkpoint will run and the longer it'll take each time. But you cannot directly control checkpoint interval or duration.
This blog post may help a bit - http://sqlinthewild.co.za/index.php/2009/06/26/the-lazy-writer-and-the-checkpoint/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability