how can i find out how busy sql server is with IO using @@IO_BUSY

Question

I am trying to find out how busy my sql 2000 server in terms of CPU and IO through a T-SQL query.

currently I am using the following to find the CPU Usage %:

  DECLARE
  @CPU_BUSY int
  , @IDLE int

  SELECT
  @CPU_BUSY = @@CPU_BUSY
  , @IDLE = @@IDLE

  WAITFOR DELAY '00:00:00:500'

  SELECT
  (@@CPU_BUSY - @CPU_BUSY)/((@@IDLE - @IDLE + @@CPU_BUSY - @CPU_BUSY) *
  1.00) *100 AS CPUBusyPct

However when I try a similar method to find the IO I get very low IO figures when I know the server is under load: DECLARE @IO_BUSY int , @IDLE int

  SELECT
  @IO_BUSY = @@IO_BUSY
  , @IDLE = @@IDLE

  WAITFOR DELAY '00:00:00:500'

  SELECT
  (@@IO_BUSY - @IO_BUSY)/((@@IDLE - @IDLE + @@IO_BUSY - @IO_BUSY) *
  1.00) *100 AS IOBusyPct

Are these methods even correct to reasonably estimate the server loads levels over a 500ms time period?

Any better suggestions?

I've tried sp_monitor but calling it every 1 second gives strange results, with the % going over 100

Answer 1

@@IO_BUSY ... is cumulative for all CPUs, so it may exceed the actual elapsed time

@@CPU_BUSY ... is cumulative for all CPUs, so it may exceed the actual elapsed time

I'd recommend using performance counters instead, they are much more accurate. Process object for CPU and SQL Server, Wait Statistics Object for various SQL waits.