--查看4小时内的CPU变化值，1分钟统计一次
DECLARE @ts_now BIGINT;
SELECT @ts_now = ms_ticks
FROM sys.dm_os_sys_info;
--select * from sys.dm_os_sys_info
SELECT record_id ,
DATEADD(ms, CONVERT(BIGINT, -1) * ( @ts_now - [timestamp] ), GETDATE()) AS EventTime ,
SQLProcessUtilization SQLServer占用CPU使用率 ,
SystemIdle System的占用CPU使用率 ,
100 - SystemIdle - SQLProcessUtilization AS 其他进程占用CPU使用率
FROM ( SELECT record.value('(./Record/@id)[1]', 'int') AS record_id ,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]',
'int') AS SystemIdle ,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
'int') AS SQLProcessUtilization ,
timestamp
FROM ( SELECT timestamp ,
CONVERT(XML, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%'
) AS x
) AS y
ORDER BY record_id DESC;
--查看磁盘空间大小
CREATE TABLE #a
(
id INT IDENTITY(1, 1) ,
DiskName VARCHAR(50)
);
INSERT INTO #a
( DiskName
)
EXEC xp_cmdshell 'wmic LOGICALDISK get name';
CREATE TABLE #b
(
id INT IDENTITY(1, 1) ,
freespace VARCHAR(50)
);
INSERT INTO #b
( freespace
)
EXEC xp_cmdshell 'wmic LOGICALDISK get freespace';
CREATE TABLE #c
(
id INT IDENTITY(1, 1) ,
size VARCHAR(50)
);
INSERT INTO #c
( size
)
EXEC xp_cmdshell 'wmic LOGICALDISK get size';
SELECT 服务器名称 = @@servername ,
DiskName 磁盘,
CONVERT(BIGINT, REPLACE(size, CHAR(13), '')) / 1024 / 1024 / 1024 AS 总大小_GB ,
CONVERT(BIGINT, REPLACE(#b.freespace, CHAR(13), '')) / 1024 / 1024
/ 1024 AS 剩余大小_GB ,
CONVERT(VARCHAR, CONVERT(DECIMAL(4, 2), ( CONVERT(DECIMAL(15, 2), CONVERT(DECIMAL(15,
2), REPLACE(#b.freespace,
CHAR(13), ''))
/ 1024 / 1024 / 1024 * 100)
/ CONVERT(DECIMAL(15, 2), CONVERT(DECIMAL(15,
2), REPLACE(size,
CHAR(13), ''))
/ 1024 / 1024 / 1024) )))
+ '%' AS 剩余率
FROM #a
JOIN #b ON #a.id = #b.id
JOIN #c ON #a.id = #c.id
WHERE #a.id > 1
AND #b.freespace IS NOT NULL
AND CHARINDEX(CHAR(13), REPLACE(#b.freespace, ' ', '')) <> 1;
DROP TABLE #a;
DROP TABLE #b;
DROP TABLE #c;
