SQLServer 维护脚本分享（03）数据库中记录CPU最近N分钟内的使用

（此脚本为网络参考，出处不详）

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)  
FROM sys.dm_os_sys_info WITH (NOLOCK));  

--	前N行则表示最近的N分钟内CPU使用情况
SELECT TOP(60) 
 DATEADD(ms,-1 * (@ts_now - [timestamp]),GETDATE()) AS [Event Time],SQLProcessUtilization AS [SQL Server Process CPU Utilization],SystemIdle AS [System Idle Process],(100 - SystemIdle - SQLProcessUtilization) AS [Other Process CPU Utilization]
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 WITH (NOLOCK)  
		WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'  
		AND record LIKE N'%<SystemHealth>%'
	) AS x  
) AS y  
ORDER BY record_id DESC OPTION (RECOMPILE);  

拷贝数据到Excel中使用图像查看：

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!