Database health checkDiagnóstico do banco — sql-demo-01

WITH b AS (
    SELECT database_name,
           MAX(CASE WHEN type = 'D' THEN backup_finish_date END) AS last_full,
           MAX(CASE WHEN type = 'I' THEN backup_finish_date END) AS last_diff,
           MAX(CASE WHEN type = 'L' THEN backup_finish_date END) AS last_log
    FROM msdb.dbo.backupset
    WHERE is_copy_only = 0
    GROUP BY database_name
)
SELECT d.name                AS [database],
       d.recovery_model_desc AS recovery_model,
       b.last_full,
       b.last_diff,
       b.last_log,
       CASE
           WHEN b.last_full IS NULL THEN 'no full backup'
           WHEN ISNULL(b.last_diff, b.last_full) < DATEADD(hour, -{{full_max_hours}}, GETDATE())
                AND b.last_full < DATEADD(hour, -{{full_max_hours}}, GETDATE()) THEN 'full/diff too old'
           ELSE 'log backup too old'
       END AS problem
FROM sys.databases d
LEFT JOIN b ON b.database_name = d.name
WHERE d.database_id <> 2               -- tempdb
  AND d.state_desc = 'ONLINE'
  AND d.source_database_id IS NULL     -- snapshots
  AND (
        b.last_full IS NULL
     OR (ISNULL(b.last_diff, b.last_full) < DATEADD(hour, -{{full_max_hours}}, GETDATE())
         AND b.last_full < DATEADD(hour, -{{full_max_hours}}, GETDATE()))
     OR (d.recovery_model_desc <> 'SIMPLE' AND d.name <> 'model'
         AND ISNULL(b.last_log, '19000101') < DATEADD(hour, -{{log_max_hours}}, GETDATE()))
  )
ORDER BY d.name;

SELECT d.name AS [database],
       NULLIF(CAST(DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS datetime), '19000101') AS last_good_checkdb,
       DATEDIFF(day, NULLIF(CAST(DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS datetime), '19000101'), GETDATE()) AS days_ago,
       d.create_date
FROM sys.databases d
WHERE d.database_id <> 2                -- tempdb
  AND d.state_desc = 'ONLINE'
  AND d.source_database_id IS NULL      -- snapshots
  AND d.create_date < DATEADD(day, -{{max_days}}, GETDATE())
  AND ISNULL(CAST(DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS datetime), '19000101') < DATEADD(day, -{{max_days}}, GETDATE())
ORDER BY d.name;

-- Only waits with a clear meaning are counted; idle and background waits never reach the report.
WITH meaning (pattern, cat_en, cat_pt) AS (
    SELECT * FROM (VALUES
        ('PAGEIOLATCH[_]%',         'Disk: reading data pages',                     'Disco: leitura de páginas de dados'),
        ('WRITELOG',                'Disk: transaction log writes',                 'Disco: escrita no log de transações'),
        ('IO[_]COMPLETION',         'Disk: other I/O',                              'Disco: outros I/O'),
        ('LCK[_]M[_]%',             'Blocking: waiting for locks',                  'Bloqueio: esperando locks'),
        ('PAGELATCH[_]%',           'Page contention in memory (often TempDB)',     'Disputa de páginas em memória (geralmente TempDB)'),
        ('SOS[_]SCHEDULER[_]YIELD', 'CPU pressure',                                 'Pressão de CPU'),
        ('CXPACKET',                'Parallelism',                                  'Paralelismo'),
        ('CXCONSUMER',              'Parallelism',                                  'Paralelismo'),
        ('RESOURCE[_]SEMAPHORE',    'Queries waiting for memory grants',            'Consultas esperando memória'),
        ('THREADPOOL',              'Out of worker threads',                        'Falta de threads de trabalho'),
        ('ASYNC[_]NETWORK[_]IO',    'Application not reading results fast enough',  'Aplicação lendo resultados devagar'),
        ('HADR[_]SYNC[_]COMMIT',    'Synchronous Availability Group commit',        'Commit síncrono do Availability Group')
    ) v (pattern, cat_en, cat_pt)
),
c AS (
    SELECT m.cat_en, MAX(m.cat_pt) AS cat_pt, SUM(ws.wait_time_ms) AS ms, SUM(ws.waiting_tasks_count) AS tasks,
           MAX(ws.wait_type) AS example
    FROM sys.dm_os_wait_stats ws
    JOIN meaning m ON ws.wait_type LIKE m.pattern
    WHERE ws.wait_time_ms > 0
    GROUP BY m.cat_en
),
t AS (SELECT SUM(ms) AS total FROM c),
up AS (SELECT DATEDIFF(second, sqlserver_start_time, SYSDATETIME()) / 3600.0 AS hours FROM sys.dm_os_sys_info)
SELECT c.cat_en AS [meaning@en],
       c.cat_pt AS [meaning@pt-BR],
       c.example AS wait_type,
       CAST(c.ms * 100.0 / t.total AS decimal(5, 1)) AS share_pct,
       CAST(c.ms / 1000.0 / NULLIF(up.hours, 0) AS decimal(14, 1)) AS wait_s_per_hour,
       CAST(c.ms * 1.0 / NULLIF(c.tasks, 0) AS decimal(14, 1)) AS avg_wait_ms,
       CASE WHEN c.cat_en IN ('Out of worker threads', 'Queries waiting for memory grants') THEN 'confirmed' ELSE 'hint' END AS _confidence
FROM c CROSS JOIN t CROSS JOIN up
WHERE c.ms * 100.0 / t.total >= {{min_share_pct}}
  AND c.ms / 1000.0 / NULLIF(up.hours, 0) >= {{min_wait_s_per_hour}}
ORDER BY c.ms DESC;

WITH c AS (SELECT name, CAST(value_in_use AS bigint) AS v FROM sys.configurations),
     s AS (SELECT cpu_count, physical_memory_kb / 1024 AS ram_mb FROM sys.dm_os_sys_info)
SELECT 'max server memory (MB)' AS setting, CAST(c.v AS varchar(20)) AS current_value,
       CAST(CAST(s.ram_mb * 0.8 AS bigint) AS varchar(20)) AS suggested, CAST(s.ram_mb AS varchar(20)) + ' MB RAM' AS host
FROM c CROSS JOIN s WHERE c.name = 'max server memory (MB)' AND c.v >= 2147483647
UNION ALL
SELECT 'max degree of parallelism', CAST(c.v AS varchar(20)), '8', CAST(s.cpu_count AS varchar(20)) + ' CPUs'
FROM c CROSS JOIN s WHERE c.name = 'max degree of parallelism' AND c.v = 0 AND s.cpu_count > {{maxdop_cpus}}
UNION ALL
SELECT 'cost threshold for parallelism', CAST(c.v AS varchar(20)), '50', CAST(s.cpu_count AS varchar(20)) + ' CPUs'
FROM c CROSS JOIN s WHERE c.name = 'cost threshold for parallelism' AND c.v = 5
UNION ALL
SELECT 'optimize for ad hoc workloads', CAST(c.v AS varchar(20)), '1', NULL
FROM c WHERE c.name = 'optimize for ad hoc workloads' AND c.v = 0;

SELECT TOP (20)
       DB_NAME(d.database_id) AS [database],
       d.statement AS [table],
       d.equality_columns,
       d.inequality_columns,
       d.included_columns,
       s.user_seeks + s.user_scans AS uses,
       CAST(s.avg_user_impact AS decimal(5, 1)) AS est_impact_pct,
       CAST(s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans) AS decimal(18, 0)) AS improvement,
       s.last_user_seek
FROM sys.dm_db_missing_index_details d
JOIN sys.dm_db_missing_index_groups g ON g.index_handle = d.index_handle
JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle
WHERE d.database_id > 4
  AND s.avg_user_impact >= {{min_impact_pct}}
  AND s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans) >= {{min_improvement}}
ORDER BY improvement DESC;