Query help

• sundar329

  Say Hey Kid

  Points: 680

  More actions

  September 6, 2013 at 2:23 am

  #276949

  Hi friends,

  When I run the below procedure am getting as CPU utilization of the server is 185% ,300%. Where am I wrong in the script..

  create procedure CPU

  as

  begin

  declare @a as bigint, @b-2 as bigint, @val float

  select @a=sum(cntr_value)

  from sys.dm_os_performance_counters

  where object_name = 'SQLServer:Resource Pool Stats' and cntr_type = 537003264

  select distinct @b-2=cntr_value

  from sys.dm_os_performance_counters

  where object_name = 'SQLServer:Resource Pool Stats' and cntr_type = 1073939712

  set @val=(select cast(@a as float) / cast(@b as float)*100 as [cpu])

  print @val

  declare @temp varchar(max)

  set @temp='CPU utilization of server is '+CONVERT(VARCHAR(3),LEFT(@val,3))+'%'+' Check what is running in the server'

  if(@val>50)

  BEGIN

  Exec msdb.dbo.sp_send_dbmail

  @recipients='isdm@plintron.com',

  @subject='Server utilization is high',

  @body=@temp

  END

  end

  Thanks in advance.

• sqlnaive

  SSCoach

  Points: 17437

  More actions

  September 6, 2013 at 3:07 am

  #1647958

  Try this:

  set @val=(select cast(@a as float)*100 / cast(@b as float) as [cpu])

  Sundar, It's giving result correctly to me.

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  September 6, 2013 at 2:27 pm

  #1648241

  It seams to be working correctly for me as well...

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001