CPU-Memory-IO Utilization

-- current CPU utilization

WITH DB_CPU_Stats
AS (
SELECT DatabaseID
,DB_Name(DatabaseID) AS [DatabaseName]
,SUM(total_worker_time) AS [CPU_Time_Ms]
,CONVERT([smalldatetime], GETDATE()) AS [CPU_Stats_DateTime]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY (
SELECT CONVERT(INT, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid'
) AS F_DB
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]
,[CPU_Stats_DateTime]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 -- system databases
AND DatabaseID <> 32767 -- ResourceDB
ORDER BY row_num
OPTION (RECOMPILE);

---CPU Utilization History:

DECLARE @ts_now bigint = (SELECT ms_ticks FROM sys.dm_os_sys_info);

SELECT  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 '%<SystemHealth>%') AS x
      ) AS y
ORDER BY record_id DESC;




----- ****** 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'
,cast (sysdatetime() as date) taken_date
FROM sys.dm_io_virtual_file_stats(NULL,NULL) I
INNER JOIN sys.databases D ON I.database_id = D.database_id
where name not in ('msdb','master','model')
GROUP BY Name
ORDER BY 'Number of Reads'
DESC;


--- Memory utilization history -----------


declare @page_size int, @Instancename varchar(50);
declare @Total_physical_memory_GB as varchar(50);
declare @Available_physical_memory_MB as varchar(50);
declare @Buffer_Pool_Usage as varchar(50);
declare @Max_Server_Mermory as varchar(50);
declare @ple as int;
declare @SQLcmd varchar(8000); set @SQLcmd = '<Total Physical Memory Query>';
declare @Mem table ([Total_physical_memory_GB] varchar(50));

-- Get the SQL server version
declare @version varchar(4);
select @version = SUBSTRING(@@VERSION, CHARINDEX('2',@@VERSION),4);

begin
SET NOCOUNT ON;
SELECT @Total_physical_memory_GB = cast((([total_physical_memory_kb] / 1024)) as varchar(50))FROM [master].[sys].[dm_os_sys_memory];
SELECT @Available_physical_memory_MB = cast((([available_physical_memory_kb] / 1024)) as varchar(50))FROM [master].[sys].[dm_os_sys_memory];
SELECT @Buffer_Pool_Usage =cast(CEILING(cntr_value/1024) as varchar(50)) FROM sys.dm_os_performance_counters WHERE counter_name = 'Total Server Memory (KB)';
SELECT @Max_Server_Mermory = cast([value_in_use]as varchar(50)) FROM [master].[sys].[configurations]WHERE NAME IN ('Max server memory (MB)');
select @ple = cntr_value from sys.dm_os_performance_counters where object_name like '%Manager%' and counter_name = 'Page life expectancy';
end

select  @Total_physical_memory_GB 'Total RAM MB ' ,
        @Available_physical_memory_MB  '  Available RAM MB ',
        @Max_Server_Mermory ' Max Server Memory MB ',
@Buffer_Pool_Usage ' Current SQL Usage MB ',
cast(@ple as varchar(10)) ' Page Life Expectancy Sec '

,cast (sysdatetime() as date) taken_date