Databases Reference
In-Depth Information
This is one query that you will want to run multiple times in quick succession, as the values returned
change very frequently, depending on your workload and how your SQL Server instance is run-
ning. Any value for these three columns that stays above 10 for a sustained period is cause for some
concern. The Average Task Count (per CPU scheduler) is a good indicator of your overall workload
level. Sustained high Average Task Counts are often caused by blocking or other resource conten-
tion. The Average Runnable Task Count indicates how many tasks are waiting for CPU time on each
CPU scheduler. It is a very reliable indicator of CPU pressure. The Average Pending DiskIO Count
measures how many pending I/O operations are on each CPU scheduler, so it is a good indicator of
overall I/O pressure. This value in particular will jump around a lot as your I/O subsystem is under
stress. For all three of these columns, lower values are better than higher values.
The next query, shown in Listing 15-24, will return your CPU utilization history over the last
256 minutes, in one-minute intervals.
LISTING 15-24:
CPU utilization history
-- Get CPU Utilization History for last 256 minutes (in one minute intervals)
-- This version works with SQL Server 2008 and above
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)
FROM sys.dm_os_sys_info WITH (NOLOCK));
SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 - SystemIdle - SQLProcessUtilization
AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - [timestamp]),
GETDATE()) AS [Event Time]
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);
-- Look at the trend over the entire period.
-- Also look at high sustained Other Process CPU Utilization values
This query shows the recent CPU utilization history for your database server, recorded in one-
minute increments. That means it can miss a short spike of CPU activity, but I don't think that's
really a big problem. You should be more interested in the trend over the last four hours than worry
about sub minute spikes. The query gives you CPU utilization by the SQL Server Database Engine,
and the sum of all other processes that are running on the database server (the “Other Process CPU
Utilization” column). This gives you an idea of CPU pressure caused by SQL Server versus other
sources such as management or monitoring software. Ideally, your mission-critical database servers
are dedicated SQL Server instances with virtually nothing else running on the machine besides SQL
Search WWH ::
Custom Search