Performance of sqlserver drops few times per hour

Approx. 3 times per hour sqlserver is writing data for 8 to 10 seconds.

What it is writing i dont know, but the result of this is that the

duration of executing queries is much larger then normal ( 10 times ).

Normally the query takes up to 400 milliseconds, and during this writing problem, it takes 6 or more seconds.

Sqlserver is not doing backups when this happens.

Does anyone have any idea?Perhaps background checkpoints are causing the writes. You could turn on trace flag 3504 (DBCC TRACEON(3504,-1)) which will cause checkpoint summaries to be written to the errorlog and see if the times are coincident.|||

The first this I always do is run up NT performance monitor to see if the server itslef is doing anything.

Performance graphs I use are all the CPU's and logical disk %read , %write , Avg Disk write queue , Avg Disk read queue. Disk queues should be less than 2 per spindal eg if you have 10 drives then this can peek past 20 but should not remain at that fro any lenght of time.

From there it's into SQL profiler

|||Great 2 answers and they both helped me :)

Thanks people it was the checkpoint. Now i need to find a way to do more checkpoints in an hour.

Anyone an idea?

Thanks|||You can set the checkpoint/recovery interval.

e.g.
--chkpoint every 10 minute
exec sp_configure 'recovery interval', '10'
reconfigure with override
go|||You can set the checkpoint/recovery interval.

e.g.

--chkpoint every 10 minute

exec sp_configure 'recovery interval', '10'

reconfigure with override

go