Monitor CPU utilization, I/O Usage and Memory Usage in Sql Server

CPU Usage, I/O Usage and Memory Usage of database

Database level / Database wise CPU, memory and I/O usage

As part of DBA’s daily checklist, we need to monitor few parameters of a database throughout the day. It includes CPU utilization, Memory utilization and I/O utilization. Here are the T-SQL scripts to monitor sql server instances database wise.

CPU Utilization:

WITH DB_CPU_Stats
AS
(SELECT DatabaseID,
DB_Name(DatabaseID)AS [DatabaseName],
SUM(total_worker_time)AS [CPU_Time(Ms)]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY(SELECT CONVERT(int, value)AS [DatabaseID]
 FROM sys.dm_exec_plan_attributes(qs.plan_handle)
 WHERE attribute =N'dbid')AS epa
GROUP BY DatabaseID)
SELECT ROW_NUMBER()OVER(ORDER BY [CPU_Time(Ms)] DESC)AS [row_num],
DatabaseName,
[CPU_Time(Ms)],
CAST([CPU_Time(Ms)] * 1.0 /SUM([CPU_Time(Ms)])
OVER()* 100.0 AS DECIMAL(5, 2))AS [CPUPercent]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 -- system databases
AND DatabaseID <> 32767 -- ResourceDB
ORDER BY row_num
OPTION(RECOMPILE);

CPU Utilization History:

The query retrieves the SQL Server instance CPU usage from last 10 minutes.

/***** CPU Utilization history *****/
-- Get CPU Utilization History (SQL Server 2008 and above)
DECLARE @ts BIGINT
SELECT @ts =(SELECT cpu_ticks/(cpu_ticks/ms_ticks)
FROM sys.dm_os_sys_info);
SELECT TOP(10)SQLProcessUtilization AS [SQLServer_Process_CPU_Utilization],
SystemIdle AS [System_Idle_Process],
100 - SystemIdle - SQLProcessUtilization AS [Other_Process_CPU_Utilization],
DATEADD(ms,-1 *(@ts - [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
            WHERE ring_buffer_type =N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE'%%')AS x
)AS y
ORDER BY record_id DESC;

--<strong> Get CPU Utilization History (SQL 2005 Only)</strong>
DECLARE  @ts_now BIGINT;
SELECT   @ts_now = cpu_ticks /CONVERT(float, cpu_ticks_in_ms)
FROM  sys.dm_os_sys_info
SELECT  TOP(10)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
           WHERE ring_buffer_type =N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE'%%')AS x
          )AS y
ORDER BY record_id DESC;

SQLServer_Process_CPU_Utilization: Percentage of CPU utilizing by SQL Server instance

Sytem_Idle_Process: Percentage of CPU is idle

Other_Process_CPU_Utilization: Percentage of CPU utilizing by all other processes other than SQL Server instance.

Event_Time: Time when these details has been captured

I/O Usage by database:

/****** I/O Usage by database ****/
SELECT Name AS'Database Name'
,SUM(num_of_reads)AS'Number of Reads'
,SUM(num_of_writes)AS'Number of Writes'
FROM sys.dm_io_virtual_file_stats(NULL,NULL) I
INNER JOIN sys.databases D ON I.database_id = d.database_id
GROUP BY Name
ORDER BY 'Number of Reads'
DESC;

Memory usage by Database:

DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name])LIKE'%Buffer Manager'
AND counter_name ='Total Pages';
;WITH src AS
(SELECT database_id,
db_buffer_pages =COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
      --WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT [db_name] = CASE [database_id] WHEN 32767 THEN 'Resource DB' ELSE DB_NAME([database_id])END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent =CONVERT(DECIMAL(6,3),
db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;