SQL Server Performance Forum – Threads Archive

Checkpoint process

I see a line in sys.sysprocesses. The process’s status is suspend and the command is CHECKPOINT. I have the information here exactly as it is on my monitor. It seems is consuming hi cpu. What should I do?
[/code]
spid: 10
kpid: 7416
block: 0
waittype: 0x0081
waittime: 232546
lastwaittype: CHECKPOINT_QUEUE
waitresource:
dbid: 1
uid: 1
cpu: 427046
physical_io: 36695
memusage: 0
login_time: 2007-04-04 10:01:32.787
lastbatch: 2007-04-04 10:01:32.787
ecid: 0
open_tran: 0
status: suspended
sid:0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
hostname:
program_name:
hostprocess:
cmd:CHECKPOINT nt_domain:
nt_username:
loginame: sa
[/code] CanadaDBA

CPU is cumulative since the last time the server was restarted for a system process like this: don’t worry about it being high for checkpoint.

What is this Checkpoint process? Should it be there all the time? CanadaDBA

Yes it is very important. You need to do some reading in Books Online.

quote:For performance reasons, the Database Engine performs modifications to database pages in memory and does not write the page to disk after every change. However, periodically the Database Engine needs to perform a checkpoint to write these dirty pages to disk. Writing dirty pages to disk creates a known good point from which the Database Engine can start applying changes contained in the log during recovery after an unexpected shutdown or crash.

See the following topic in 2005 Books Online ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/98a80238-7409-4708-8a7d-5defd9957185.htm

CHECKPOINT process is visible when there is such activing on the transaction log or called from the application too. But if it is hanging for too much time then you must look at the system resource usage mainly disk & cpu. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Thanks Thomas! Good information. Satya, As you can see in the data, the process is in there as of April 4th. All of the databases are set in Single recovery mode since we don’t need to keep any TLog. We have some application that access the databases and generate reports. The data says last batch was 4-4-07. Today is 4-11-07. Isn’t there anything I can do? Per Thomas, this is normal. CanadaDBA

Check the relevant SPID and see what it is, if its a system process (SPID < 50) then don’t worry as it is taken by system itself [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] and if not you have to see which user is performing the actino. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.</center>

Well, as you can see in the data in my first post, SPid is 10. CanadaDBA

You don’t need to do anything. It doesn’t matter that your DBs are in simple recovery mode, Checkpoint still needs to run as it deals with data pages not log.

Good to know. Thanks! CanadaDBA

]]>