100 % CPU usage during Checkpoint

[farhy]

hi,

we have a SQL Server 2005, on a multi cpu computer with 200 GB ram and, during a Checkpoint we have 100% cpu load on
a single processor.

Has anyone seen such a behavier on SQL Server 2005 or do you have a computer with similar high ram without this problem?

Is there any posibility to bind a session to specific cpu, to see how the performance is, when the cpu load is high.

thanx in advance, I hope you can help

br
farhy

 

[mrdenny]

200 Gigs of RAM? Is this 32bit Windows or 64bit Windows?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[farhy]

Hi,

this is a 64Bit cluster

do you know such a behavior.

My theory is: When a checkpoint occurs, all the pages in the ram (200GB) must be checked if the dirty bit is set, therefore
one cpu go up to 100% for 15 sec.
If a request is scheduled to this cpu, it takes much longer, than usual.

Can we configure the sql server, so that the checkpoint task is done by more than one cpu?

thank you for your help.
br
farhy

 

[mrdenny]

I don't think that you can tell SQL to use more than one processor.

Have you made any changes to the recovery interval setting of sp_configure?

How often is the database checkpointing?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com