CPU % from SQL

• rickzan

  Valued Member

  Points: 57

  More actions

  January 16, 2003 at 1:51 am

  #105583

  Anybody knows hot to read the value of CPU % from T_SQL? I have to monitor a databse with the server out of my (administrator)control: I have administrator rights on the db but not on the server. More difficult: I cannot add software except for the standatd W2K one

  thanks

• rortloff

  SSC Enthusiast

  Points: 128

  More actions

  January 16, 2003 at 7:28 am

  #446497

  I don't know of a way to monitor CPU busy from SQL but there is a way to check the state of SQL's processing capacity.

  DBCC SQLPERF (UMSSTATS) will provide some good info. The only problem is if you're disk bottlenecked or another system level bottleneck or have locking problems you will not know from just running this command alone.

  The values reported are cumulative so run DBCC SQLPERF (UMSSTATS, Clear) first.

• Greg Larsen

  SSC-Insane

  Points: 20961

  More actions

  January 16, 2003 at 7:55 am

  #446505

  You might try something like this to calculate something close to CPU Busy:

  declare @d1 datetime

  declare @d2 datetime

  declare @CPU1 int

  declare @CPU2 int

  select @CPU1 = @@CPU_BUSY, @d1 = getdate()

  while datediff(ss,@d1,getdate()) < 1

  select @CPU2 = @@CPU_BUSY, @d2 = getdate()

  select cast((@cpu2 - @cpu1) as decimal(10,3)) / datediff(ms,@d1,@d2) * 1000 as CPU_BUSY

  Gregory Larsen, DBA

  If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

  Gregory A. Larsen, MVP