--檢視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))
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