.::: Get CPU Utilization History for last 60 minutes,Get I/O Usage,Memory usage by Database,Memory Usage Query SQL Server :::.

-- Query Get CPU Utilization History for last 60 minutes SQL Server 

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

SELECT TOP(60) 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] , @@SERVERNAME as ServerName,@@servicename as ServiceName
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;

-- Get I/O Usage by database

SELECT     @@SERVERNAME as ServerName,
    @@servicename as ServiceName,
    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 @@SERVERNAME as ServerName,
    @@servicename as ServiceName,[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;

-- SQL Server Memory Usage Query

-- =============================================
-- Author:      Eli Leiba- Create date: 01-04-2021
-- Description: Check current SQL memory status compared to the OS status
-- =============================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.fn_CheckSQLMemory()
RETURNS @Sql_MemStatus TABLE
 (
   SQLServer_Start_DateTime datetime,
   SQL_current_Memory_usage_mb int,
   SQL_Max_Memory_target_mb int,
   OS_Total_Memory_mb int,
   OS_Available_Memory_mb int)
AS
BEGIN
   declare @strtSQL datetime
   declare @currmem int
   declare @smaxmem int
   declare @osmaxmm int
   declare @osavlmm int
 
   -- SQL memory
   SELECT
      @strtSQL = sqlserver_start_time,
      @currmem = (committed_kb/1024),
      @smaxmem = (committed_target_kb/1024)           
   FROM sys.dm_os_sys_info;
   
   --OS memory
   SELECT
      @osmaxmm = (total_physical_memory_kb/1024),
      @osavlmm = (available_physical_memory_kb/1024)
   FROM sys.dm_os_sys_memory;
   
   INSERT INTO @Sql_MemStatus values (@strtSQL, @currmem, @smaxmem, @osmaxmm, @osavlmm)
 
   RETURN
END
GO

-- how to use the function:
USE master
GO
select @@SERVERNAME as ServerName,@@servicename as ServiceName,* from dbo.fn_CheckSQLMemory()
GO