用SQL语句检查CPU和磁盘空间

--查看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;