The CPU on the SQL 2008 sp2 64bit box is always at 70% utilization which is causing queries to time out especially the ones which have sorting operations
like 'DISTINCT' and 'ORDER BY' clause. On further investigation from performance monitor we found that one of the SQL processes was consuming most of the CPU.
We looked for the the processid in the sysprocess and found that cmd check point was the related processid consuming the resources.
spidkpidstatus hostname cmdDB Name
111948background CHECKPOINT master
When the CPU is normal with < 10% utilization interspersed with spikes, the sorting queries comeback in 0 secs.
Below are the env variables
System: Windows 2008 R2 Standard 64bit
SQL Server: Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)
DB Size: 3gb
Number of DBS
on the instance: 2
DB Modes:Stand by/read only since these are destination log shipping databases with logs restored every night.
Any suggestions and inputs would help.