High CPU on SQL 2008 64BIT

Question

Post reply

High CPU on SQL 2008 64BIT

am-244616

SSCarpal Tunnel

Points: 4936
More actions
December 21, 2010 at 7:00 am

#232182

Guys,
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.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

What do you see if you set up perfmon to collect CPU information and SQLServer:Buffer Manager Checkpoint pages/sec?

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

am-244616 SSCarpal Tunnel Points: 4936 More actions · Answer 2

Thanks for the response David

I look at perfmon and gathered the process which consumes most CPU, subsequently from that process I grabbed the corresponding thread and plugged it in the the query below

select cmd, cpu, spid, lastwaittype

from master..sysprocesses

--WHERE CMD = 'RESOURCE MONITOR'

WHERE KPID = '1764'

cmdcpuspidlastwaittype

RESOURCE MONITOR3215319061PREEMPTIVE_XE_CALLBACKEXECUTE

Please let me know if you need any further info

Thanks

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 3

Yes, I see what you did. What I need to know though is Checkpoint pages/sec high when CPU is high and is there always a direct correlation. The wait type information is not helpful to me at this point.

So, if you could run perfmon and look at those two counters together and get back to me that would be great.

Note: I see that you had this similar issue back in November. Are you still looking for the same answer?

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

am-244616 SSCarpal Tunnel Points: 4936 More actions · Answer 4

David,

Back in November when we had the issue with "Resource Monitor" upon investigation it was indicated that it is a know issue and SP2 would take care of it (Pls see the links below). Since then we applied SP2 but the problem is still persistent.

http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/bd9e4476-39e2-46f8-a426-bef9601f9ad9

http://support.microsoft.com/kb/968722/en-us

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 5

Ok - thanks for the update. Please let me know what you see with those two counters.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

am-244616 SSCarpal Tunnel Points: 4936 More actions · Answer 6

David there is a direct correlation i.e checkpoint pages is high when cpu is high.

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 7

So, there is very little you can do to alleviate that aside from making sure that your IO subsystem can handle that activity; which at present it is not.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 8

Once you know the CPU is high, I'd start drilling down on what's causing it to be that high. What processes are using it up? I'd go to the DMO objects and start gathering metrics on CPU, and I'd monitor them to see when you're hitting waits, what those waits are, and what those queries are doing. It might be a system configuration issue, but it's most likely a fundamental tuning issue, incorrect or missing indexes, badly written queries, etc.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 9

Enable Optimize for Adhoc Workloads option for the SQL Server Instance. This will reduce the procedure cache bloating and the CPU compilation overhead.

sp_configure 'show advanced options', 1

go

reconfigure

go

sp_configure 'optimize for ad hoc workloads', 1

go

reconfigure

go

Aslo check for the following Perfmon Counters and let us know their values

SQL Server: SQL Statistics -- Batch Requests/Sec

SQL Server: SQL Statistics -- SQL Compilations/Sec

SQL Server: SQL Statistics -- SQL ReCompilations/Sec

Thank You,

Best Regards,

SQLBuddy