CPU Utilization on 64 bit SQL Server 2005

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » CPU Utilization on 64 bit SQL Server 2005

CPU Utilization on 64 bit SQL Server 2005

Rate Topic

Display Mode

Topic Options

Author

Message

beejug1983

Posted Monday, December 19, 2011 10:18 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:05 PM
Points: 264, Visits: 1,285

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.

Post #1224141

Ninja's_RGR'us

Posted Monday, December 19, 2011 10:44 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:29 PM
Points: 21,357, Visits: 9,533

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

Post #1224149

Bhuvnesh

Posted Tuesday, December 20, 2011 6:01 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1224368

beejug1983

Posted Tuesday, December 20, 2011 11:28 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:05 PM
Points: 264, Visits: 1,285

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.

Post #1224889

Ninja's_RGR'us

Posted Wednesday, December 21, 2011 5:42 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:29 PM
Points: 21,357, Visits: 9,533

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

Post #1225047

beejug1983

Posted Wednesday, January 11, 2012 5:47 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:05 PM
Points: 264, Visits: 1,285

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 Status Login 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 TASK 2121
10 BACKGROUND sa master BRKR EVENT HNDLR 15
11 BACKGROUND sa master BRKR TASK 6318
12 BACKGROUND sa chancelor CHECKPOINT 3129192

Thnaks in advance

Post #1234494

« Prev Topic | Next Topic »

Permissions