SQL SERVER 查看CPU利用率

 --查看CPU利用率
-- CREATE PROCEDURE sp_GetTop10_CPU () AS
BEGIN
  SET NOCOUNT ON
  DECLARE @cinterval char(8)
  DECLARE @interval int

  SET @cinterval = '00:00:10'
  CREATE TABLE #thread
  (
    RUN INT NOT NULL,
    SPID INT NOT NULL,
    THREAD INT NULL,
    STATUS varchar(30) NOT NULL,
    [IO] INT NOT NULL,
    CPU INT NOT NULL,
    MEM INT NOT NULL,
    LOCKS int NOT NULL,
    BLK INT NOT NULL,
    hostname varchar(30) NOT NULL,
    hostprocess INT NOT NULL
    CONSTRAINT PK_thread1 PRIMARY KEY CLUSTERED (RUN,SPID)
  )

  INSERT #thread
  SELECT 1,pr.spid,pr.kpid,pr.status,pr.physical_io,pr.cpu, pr.memusage,ISNULL(count(lk.spid),0),pr.blocked , max(pr.hostname), max(pr.hostprocess)
  FROM   master.dbo.sysprocesses pr
  LEFT OUTER JOIN  master.dbo.syslocks  lk
  ON pr.spid = lk.spid
  WHERE pr.spid>=50 AND pr.spid<>@@SPID
  GROUP BY pr.spid,pr.kpid,pr.status,pr.physical_io, pr.cpu,pr.memusage,pr.blocked

  WAITFOR DELAY @cinterval
  INSERT #thread
  SELECT 2,pr.spid,pr.kpid,pr.status,pr.physical_io,pr.cpu, pr.memusage,ISNULL(count(lk.spid),0),pr.blocked , max(pr.hostname), max(pr.hostprocess)
  FROM   master.dbo.sysprocesses pr
  LEFT OUTER JOIN  master.dbo.syslocks  lk
  ON pr.spid = lk.spid
  WHERE pr.spid>=50 AND pr.spid<>@@SPID
  GROUP BY pr.spid,pr.kpid,pr.status,pr.physical_io, pr.cpu,pr.memusage,pr.blocked

  SELECT TOP 10 T.*
  FROM
  (
    SELECT a.SPID, a.hostname, b.hostprocess, b.STATUS, (b.CPU-a.CPU) as 'CPU'
    FROM #thread a
    JOIN #thread b
    ON a.SPID=b.SPID and a.RUN=1 and b.RUN=2
   ) T
   ORDER BY CPU DESC
   DROP TABLE #thread
RETURN
END

GO

/*

declare @handle binary(20)
select @handle = sql_handle from master..sysprocesses where spid = 53
select 'Current statement'= text from ::fn_get_sql(@handle)
select @handle = sql_handle from master..sysprocesses where spid = 57
select 'Current statement'= text from ::fn_get_sql(@handle)

system_function_schema

*/