CPU Utilization on 64 bit SQL Server 2005

Question

Post reply

CPU Utilization on 64 bit SQL Server 2005

beejug1983

SSCertifiable

Points: 6491
More actions
December 19, 2011 at 10:18 pm

#247845

Hi,
I have SQL Server 2005 64 bit running on Windows 2008 64 bit. I am continuously getting average CPU more than 100. I checked from sys.sysprocesses and found below processes are continuously consuming high CPU few more heavy transactions runs. But I want to know if I am getting CPU utilization more than 100 because of below processes.
spid waittime lastwaittype status CPU
10 4480 0 0x007E 78424187 ONDEMAND_TASK_QUEUE background
4 5620 0 0x009D 78418906 KSOURCE_WAKEUP background
16 4512 0 0x00A9 78418890 BROKER_TRANSMITTER background
17 4516 0 0x00A9 78418875 BROKER_TRANSMITTER background
12 5056 0 0x00AD 78418796 BROKER_EVENTHANDLER background
11 5728 0 0x0081 179140 CHECKPOINT_QUEUE background
Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I'd debug this with sp_WhoIsActive

http://sqlblog.com/files/default.aspx

And those articles as well

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

beejug1983 (12/19/2011)
spid waittime lastwaittype status CPU
16 4512 0 0x00A9 78418890 BROKER_TRANSMITTER background
17 4516 0 0x00A9 78418875 BROKER_TRANSMITTER background
12 5056 0 0x00AD 78418796 BROKER_EVENTHANDLER background

it means that you are using service broker here. are you closing the "conversation".try to disable/stop the service broker and see if cpu goes down and then look into the service borker handled process(if any).

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

beejug1983 SSCertifiable Points: 6491 More actions · Answer 3

Ninja-I Can see few user's active connections. But most of the CPU are being utilized by background processes.

Bhuvanesh-I need to check the service broker.

Thanks in advance.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

SB is not my forte so I'll be in listen mode most likely.

beejug1983 SSCertifiable Points: 6491 More actions · Answer 5

Hi All, following is the output of sp_wh2 active, I just could not understand why these all background processes taking much cpu.CPU usage always shows very high on server. Can you please let me understand about below details.

SPID StatusLogin DBName Command CPU

1 BACKGROUND sa NULL RESOURCE MONITOR 7160

2 BACKGROUND sa NULL XE TIMER 202

3 BACKGROUND sa NULL XE DISPATCHER 0

4 BACKGROUND sa NULL LOG WRITER 1021182

5 BACKGROUND sa NULL LOCK MONITOR 79872

6 BACKGROUND sa NULL LAZY WRITER 1458078

7 BACKGROUND sa master SIGNAL HANDLER 0

8 sleeping sa master TASK MANAGER 0

9 BACKGROUND sa master TRACE QUEUE TASK2121

10 BACKGROUND sa master BRKR EVENT HNDLR15

11 BACKGROUND sa master BRKR TASK 6318

12 BACKGROUND sa chancelor CHECKPOINT 3129192

Thnaks in advance