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

在MS Sql Server中可以能过以下的方法查询出磁盘空间的使用情况及各数据库数据文件及日志文件的大小及使用利用率:

1、查询各个磁盘分区的剩余空间：
Exec master.dbo.xp_fixeddrives

2、查询数据库的数据文件及日志文件的相关信息（包括文件组、当前文件大小、文件最大值、文件增长设置、文件逻辑名、文件路径等）
select * from [数据库名].[dbo].[sysfiles]
转换文件大小单位为MB：
select name, convert(float,size) * (8192.0/1024.0)/1024. from [数据库名].dbo.sysfiles

3、查询当前数据库的磁盘使用情况：
Exec sp_spaceused

４、查询数据库服务器各数据库日志文件的大小及利用率
DBCC SQLPERF(LOGSPACE)

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

      SystemIdle,

      100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization

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))
go
sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'xp_cmdshell',1
reconfigure
GO

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 server_name=@@servername,DiskName

,convert(bigint,replace(size,char(13),''))/1024/1024/1024 as total_disk_size_gb

,convert(bigint,replace(#b.freespace,char(13),''))/1024/1024/1024 as free_disk_size_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 free_space_percent

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,#b,#c