1/*============================================================================
2 File: srv_cpu_mem_overview.sql
3
4 Summary: Gets basic system information.
5 Date: May 2011
6 Updated: June 2013
7
8 SQL Server Version: 10.50.1600.0 (SQL Server 2008 R2 RTM) or later
9------------------------------------------------------------------------------
10 Written by Janos Berke, IamBerke.com
11
12 For more scripts and sample code, check out http://www.iamBerke.com
13
14 THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
15 ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
16 TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
17 PARTICULAR PURPOSE.
18============================================================================*/
19--run in SQLCMD mode by using ALT+Q+M
20DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);
21
22SELECT TOP(256)
23 SQLProcessUtilization AS [SQLServerProcessCPUUtilization],
24 SystemIdle AS [System Idle Process],
25 100 - SystemIdle - SQLProcessUtilization AS [OtherProcessCPUUtilization],
26 [MemoryUtilization],
27 DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [EventTime]
28FROM (
29 SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
30 record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle],
31 record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization],
32 record.value('(./Record/SchedulerMonitorEvent/SystemHealth/MemoryUtilization)[1]', 'int')AS [MemoryUtilization],
33 [timestamp]
34 FROM (
35 SELECT [timestamp], CONVERT(xml, record) AS [record]
36 FROM sys.dm_os_ring_buffers WITH (NOLOCK)
37 WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
38 AND record LIKE N'%SystemHealth%') AS x
39 ) AS y
40ORDER BY record_id DESC OPTION (RECOMPILE);