Validar Memoria, CPU, Consumo Memoria, Consumo de CPU, Tipo Servidor, Batch Requests/sec e Page Life Expectancy

Pessoal, abaixo um script muito interessante para uma validação inicial de de um Servidor SQL Server:
Retorno da consulta:
-- Total de Memoria no Servidor
-- % Uso de Memoria
-- % Qtde de CPU
-- VM ou servidor Fisico
-- Transações por segundo no SQL Server (bom para monitorar como está a utilização do banco de dados);
-- Quantidade de conexões no banco de dados (bom para monitorar quando temos um maior número de usuários conectados)
-- Consumo de CPU do servidor (bom para acompanhar o consumo de CPU);
-- [Page Life Expectancy] Expectativa de vida em segundos de uma página na memória do SQL Server (bom para monitorar se você está mantendo dados em cache por muito tempo, evitando acessos a disco

Parte do script veio da rotina de contatores do brother Fabrício Lima (https://www.fabriciolima.net/blog/2016/06/22/queries-do-dia-a-dia-monitorando-contadores-de-forma-simples-via-dmv/)

USE MASTER
GO

DECLARE 
 @BATCHREQUESTS INT,
 @USER_CONNECTION INT,
 @CPU INT, 
 @PLE INT,
 @V_CPU_COUNT INT,
 @V_BATCH_REQUESTS INT

DECLARE 
 @REQUESTSPERSECONDSAMPLE1 BIGINT,
 @REQUESTSPERSECONDSAMPLE2 BIGINT

DECLARE 
 @V_SERVER_MEMORY_GB VARCHAR(255),
 @V_PER_MEMORY_USED VARCHAR(255),
 @V_VIRTUAL_MACHINE_TYPE_DESC VARCHAR(255)


SELECT 
 @REQUESTSPERSECONDSAMPLE1 = CNTR_VALUE 
FROM SYS.DM_OS_PERFORMANCE_COUNTERS WHERE COUNTER_NAME = 'BATCH REQUESTS/SEC'

WAITFOR DELAY '00:00:05'

SELECT 
 @REQUESTSPERSECONDSAMPLE2 = CNTR_VALUE 
FROM SYS.DM_OS_PERFORMANCE_COUNTERS WHERE COUNTER_NAME = 'BATCH REQUESTS/SEC'

SELECT @BATCHREQUESTS = (@REQUESTSPERSECONDSAMPLE2 - @REQUESTSPERSECONDSAMPLE1)/5

SELECT 
 @USER_CONNECTION = CNTR_VALUE
FROM SYS.DM_OS_PERFORMANCE_COUNTERS
WHERE COUNTER_NAME = 'USER CONNECTIONS'
        
SELECT  TOP(1) @CPU  = (SQLProcessUtilization + (100 - SystemIdle - SQLProcessUtilization ) )
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 
        
        
SELECT
 @PLE = CNTR_VALUE 
FROM SYS.DM_OS_PERFORMANCE_COUNTERS
WHERE COUNTER_NAME = 'PAGE LIFE EXPECTANCY'
 AND OBJECT_NAME LIKE '%Buffer Manager%'


SELECT  
 @V_SERVER_MEMORY_GB = round(total_physical_memory_kb/1048576.0,2),-- AS "SERVER_MEMORY_GB"
 @V_PER_MEMORY_USED = round(100 - (100 * CAST(available_physical_memory_kb AS DECIMAL(18,3))/CAST(total_physical_memory_kb AS DECIMAL(18,3))) ,2),-- AS "PER_MEMORY_USED"
 @V_CPU_COUNT = CPU_COUNT,
 @V_VIRTUAL_MACHINE_TYPE_DESC = VIRTUAL_MACHINE_TYPE_DESC
FROM SYS.DM_OS_SYS_INFO SYSINFO
OUTER APPLY SYS.DM_OS_SYS_MEMORY 


DECLARE @V1 BIGINT, @DELAY SMALLINT = 2, @TIME DATETIME;
 
SELECT @TIME = DATEADD(SECOND, @DELAY, '00:00:00');
 
SELECT @V1 = CNTR_VALUE 
FROM MASTER.SYS.DM_OS_PERFORMANCE_COUNTERS
WHERE COUNTER_NAME = 'Batch Requests/sec';
 
WAITFOR DELAY @time;
 
 /* [VIRTUAL_MACHINE_TYPE_DESC]
 https://docs.microsoft.com/pt-br/sql/relational-databases/system-dynamic-management-views/sys-dm-os-sys-info-transact-sql?view=sql-server-2017
 NONE = SQL Server não está em execução em uma máquina virtual.
 HYPERVISOR = o SQL Server está em execução dentro de um hipervisor, o que implica uma virtualização assistida por hardware. Quando a função Hyper-V é instalada, o hipervisor hospeda o sistema operacional, para que uma instância em execução no sistema operacional host esteja em execução no hipervisor.
 OTHER = o SQL Server está em execução dentro de uma máquina virtual que não usa assistente de hardware, como o Microsoft Virtual PC.
 */

SELECT 
 @V_BATCH_REQUESTS = (CNTR_VALUE - @V1)/@DELAY   -- Transações por segundo no SQL Server (bom para monitorar como está a utilização do banco de dados);
FROM MASTER.SYS.DM_OS_PERFORMANCE_COUNTERS
WHERE COUNTER_NAME='Batch Requests/sec';


SELECT 
 @V_SERVER_MEMORY_GB    AS [SERVER_MEMORY_GB]   -- Total de Memoria no Servidor
 ,@V_PER_MEMORY_USED    AS [PER_MEMORY_USED]   -- % Uso de Memoria
 ,@V_CPU_COUNT     AS [CPU_COUNT]     -- % Qtde de CPU
 ,@V_VIRTUAL_MACHINE_TYPE_DESC AS [VIRTUAL_MACHINE_TYPE_DESC] -- VM ou servidor Fisico
 ,@V_BATCH_REQUESTS    AS [BATCH REQUESTS/SEC]   -- Transações por segundo no SQL Server (bom para monitorar como está a utilização do banco de dados);
 ,@USER_CONNECTION    AS QT_USER_CONNECTION   -- Quantidade de conexões no banco de dados (bom para monitorar quando temos um maior número de usuários conectados)
 ,@CPU       AS [%_CPU]      -- Consumo de CPU do servidor (bom para acompanhar o consumo de CPU);
 ,@PLE       AS [Page Life Expectancy]  -- [Page Life Expectancy] Expectativa de vida em segundos de uma página na memória do SQL Server (bom para monitorar se você está mantendo dados em cache por muito tempo, evitando acessos a disco

Consulta do Dia-a-Dia que todos DBA's precisam ter na manga

--Size Tables
create table #TableSize (
    Name varchar(255),
    [rows] int,
    reserved varchar(255),
    data varchar(255),
    index_size varchar(255),
    unused varchar(255))
create table #ConvertedSizes (
    Name varchar(255),
    [rows] int,
    reservedKb int,
 SizeKB FLOAT,
 SizeMB FLOAT,
    SizeGB FLOAT,
    reservedIndexSize int,
    reservedUnused int)

EXEC sp_MSforeachtable @command1="insert into #TableSize
EXEC sp_spaceused '?'"
insert into #ConvertedSizes (Name, [rows], reservedKb,SizeKB,SizeMB, SizeGB, reservedIndexSize, reservedUnused)
select name, [rows], 
SUBSTRING(reserved, 0, LEN(reserved)-2), 
SUBSTRING(data, 0, LEN(data)-2), 
(CAST(SUBSTRING(data, 0, LEN(data)-2) AS FLOAT)/1024), 
round((CAST(SUBSTRING(data, 0, LEN(data)-2) AS FLOAT)/1024)/1024,2), 
SUBSTRING(index_size, 0, LEN(index_size)-2), 
SUBSTRING(unused, 0, LEN(unused)-2)
from #TableSize

select * from #ConvertedSizes
--WHERE Size > 0.1
order by SizeGB desc

drop table #TableSize
drop table #ConvertedSizes

--- Size Bases
with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)
select
    name as NomeDoBanco,
    ((select sum(size) from fs where type = 0 and fs.database_id = db.database_id)/1024) DataFileSizeGb,
    ((select sum(size) from fs where type = 1 and fs.database_id = db.database_id)/1024) LogFileSizeGB
into #tmp_database
from sys.databases db


select 
*
from #tmp_database
order by DataFileSizeGb desc
drop table #tmp_database

-- Valida Discos do Servidor
SELECT DISTINCT
 @@servername as "Servidor",
 QUOTENAME(CAST(CONNECTIONPROPERTY('local_net_address') AS VARCHAR)) AS "IP",
 VS.volume_mount_point [Montagem]
 ,VS.logical_volume_name AS [Volume]
 ,CAST(CAST(VS.total_bytes AS DECIMAL(19,2))/1024 /1024 /1024 AS DECIMAL (10,2)) AS [Total (GB)]
 ,CAST(CAST(VS.available_bytes AS DECIMAL(19,2))/1024 /1024 /1024 AS DECIMAL (10,2)) AS [Espaço Disponível (GB)]
 ,CAST((CAST(VS.available_bytes AS DECIMAL(19,2)) / CAST(VS.total_bytes AS DECIMAL(19,2)) * 100 ) AS DECIMAL(10,2)) AS [Espaço Disponível ( % )]
 ,CAST((100 - CAST(VS.available_bytes AS DECIMAL(19,2)) / CAST(VS.total_bytes AS DECIMAL(19,2)) * 100) AS DECIMAL (10,2)) AS [Espaço em uso ( % )]
FROM sys.master_files AS MF
CROSS APPLY [sys].[dm_os_volume_stats](MF.database_id, MF.FILE_ID) AS VS

-- Consulta Backups
SELECT TOP 100 s.database_name As NomeDobanco, 
 CASE s.[type] 
 WHEN 'D' THEN 'Full'
 WHEN 'I' THEN 'Differential'
 WHEN 'L' THEN 'Transaction Log'
 END AS TipoDeBackup, 
 m.physical_device_name AS CaminhoFisicoDoArquivo, 
 Cast(Cast(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) 
 + ' ' + 'MB' AS TamanhoDoBackup, 
 Cast(Datediff(second, s.backup_start_date, s.backup_finish_date) 
 AS VARCHAR(4)) 
 + ' ' + 'Segundos' AS TempoDoBackup, 
 s.backup_start_date AS DataInicioDoBackup, 
 s.server_name AS Servidor, 
 s.recovery_model ,*
FROM msdb.dbo.backupset s 
INNER JOIN msdb.dbo.backupmediafamily m 
ON s.media_set_id = m.media_set_id 
where s.[type]  = 'D'
ORDER BY backup_start_date DESC, 
backup_finish_date 

-- Alocação TEMPDB
SELECT A.SESSION_ID,
  B.HOST_NAME, 
  B.LOGIN_NAME ,
  (USER_OBJECTS_ALLOC_PAGE_COUNT + INTERNAL_OBJECTS_ALLOC_PAGE_COUNT)*1.0/128 AS TOTALALOCADOMB,
  D.TEXT
FROM SYS.DM_DB_SESSION_SPACE_USAGE A
JOIN SYS.DM_EXEC_SESSIONS B  ON A.SESSION_ID = B.SESSION_ID
JOIN SYS.DM_EXEC_CONNECTIONS C ON C.SESSION_ID = B.SESSION_ID
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE) AS D
WHERE  (USER_OBJECTS_ALLOC_PAGE_COUNT + INTERNAL_OBJECTS_ALLOC_PAGE_COUNT)*1.0/128 > 10 -- OCUPAM MAIS DE 100 MB
ORDER BY TOTALALOCADOMB DESC

-- Tempo Ligado
SET NOCOUNT
   ON  DECLARE @crdate DATETIME,
   @hr VARCHAR(50),
   @min VARCHAR(5)  SELECT
      @crdate=crdate
FROM
   sysdatabases
WHERE
   NAME='tempdb'  SELECT
      @hr=(DATEDIFF ( mi,
      @crdate,
      GETDATE()))/60  IF ((DATEDIFF ( mi,
      @crdate,
      GETDATE()))/60)=0  SELECT
         @min=(DATEDIFF ( mi,
         @crdate,
         GETDATE()))  
         ELSE  SELECT
            @min=(DATEDIFF ( mi,
            @crdate,
            GETDATE()))-((DATEDIFF( mi,
            @crdate,
            GETDATE()))/60)*60  PRINT 'O SQL Server "' + CONVERT(VARCHAR(20),
            SERVERPROPERTY('SERVERNAME'))+'" esta online a '+@hr+' horas e '+@min+' minutos.'

-- Ratio de Memoria
select
 (select cntr_value from sys.dm_os_performance_counters   
  where object_name like'%Memory Manager%'and counter_name like'Maximum Workspace Memory (KB)%')as'Maximum Workspace Memory (KB)',
 (select cntr_value
 from sys.dm_os_performance_counters
 where object_name like'%Memory Manager%'and counter_name like'Target Server Memory (KB)%')as'Target Server Memory (KB)',
    ( select cntr_value from sys . dm_os_performance_counters
   where object_name like'%Memory Manager%'and counter_name like'Maximum Workspace Memory (KB)%')*100.0/
       ( select cntr_value from sys . dm_os_performance_counters
   where object_name like'%Memory Manager%'and counter_name like'Target Server Memory (KB)%')as Ratio

-- Consumo CPU
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);