--查看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.SP
ROLLUP 运算符生成的结果集类似于 CUBE 运算符生成的结果集。 下面是 CUBE 和 ROLLUP 之间的具体区别： CUBE 生成的结果集显示了所选列中值的所有组合的聚合。 ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。 ROLLUP 优点： （１）ROLLUP 返回单个结果集，而 COMPUTE BY 返回多个结 ......
