SQL Server weekly review checklist

USE msdb;

-- Completed job outcomes during the last seven days.
SELECT
    j.name AS job_name,
    CASE h.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        WHEN 4 THEN 'In progress'
    END AS run_status,
    dbo.agent_datetime(h.run_date, h.run_time) AS run_start_time,
    ((h.run_duration / 10000) * 3600)
      + (((h.run_duration % 10000) / 100) * 60)
      + (h.run_duration % 100) AS duration_seconds,
    h.message
FROM dbo.sysjobhistory AS h
JOIN dbo.sysjobs AS j
    ON j.job_id = h.job_id
WHERE h.step_id = 0
  AND h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -7, GETDATE()), 112))
ORDER BY run_start_time DESC;

-- Failed, retried, or canceled steps can be hidden by a successful final outcome.
SELECT
    j.name AS job_name,
    h.step_id,
    h.step_name,
    CASE h.run_status
        WHEN 0 THEN 'Failed'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        ELSE CONVERT(varchar(20), h.run_status)
    END AS step_status,
    dbo.agent_datetime(h.run_date, h.run_time) AS run_start_time,
    h.message
FROM dbo.sysjobhistory AS h
JOIN dbo.sysjobs AS j
    ON j.job_id = h.job_id
WHERE h.step_id > 0
  AND h.run_status IN (0, 2, 3)
  AND h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -7, GETDATE()), 112))
ORDER BY run_start_time DESC, job_name, h.step_id;

USE msdb;

WITH CheckdbSteps AS (
    SELECT
        j.job_id,
        j.name AS job_name,
        j.enabled,
        s.step_id,
        s.step_name,
        s.command
    FROM dbo.sysjobs AS j
    JOIN dbo.sysjobsteps AS s
        ON s.job_id = j.job_id
    WHERE UPPER(s.command) LIKE '%DBCC CHECKDB%'
       OR UPPER(s.command) LIKE '%DATABASEINTEGRITYCHECK%'
       OR UPPER(j.name) LIKE '%CHECKDB%'
       OR UPPER(j.name) LIKE '%INTEGRITY%'
),
StepHistory AS (
    SELECT
        h.job_id,
        h.step_id,
        MAX(dbo.agent_datetime(h.run_date, h.run_time)) AS last_step_run,
        SUM(CASE WHEN h.run_status = 0 THEN 1 ELSE 0 END) AS failures_in_90_days
    FROM dbo.sysjobhistory AS h
    WHERE h.step_id > 0
      AND h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -90, GETDATE()), 112))
    GROUP BY h.job_id, h.step_id
)
SELECT
    cs.job_name,
    CASE WHEN cs.enabled = 1 THEN 'Enabled' ELSE 'Disabled' END AS job_status,
    cs.step_name,
    CASE
        WHEN UPPER(cs.command) LIKE '%PHYSICAL_ONLY%' THEN 'PHYSICAL_ONLY'
        WHEN UPPER(cs.command) LIKE '%REPAIR_%' THEN 'Repair option present - investigate'
        WHEN UPPER(cs.command) LIKE '%DATABASEINTEGRITYCHECK%' THEN 'DatabaseIntegrityCheck procedure'
        ELSE 'DBCC CHECKDB'
    END AS check_type,
    sh.last_step_run,
    ISNULL(sh.failures_in_90_days, 0) AS failures_in_90_days,
    LEFT(REPLACE(REPLACE(cs.command, CHAR(13), ' '), CHAR(10), ' '), 800) AS command_preview
FROM CheckdbSteps AS cs
LEFT JOIN StepHistory AS sh
    ON sh.job_id = cs.job_id
   AND sh.step_id = cs.step_id
ORDER BY cs.job_name, cs.step_id;

-- Run in one important user database with Query Store enabled.
WITH PeriodStats AS (
    SELECT
        q.query_id,
        p.plan_id,
        qt.query_sql_text,
        CASE
            WHEN rsi.start_time >= DATEADD(day, -7, SYSUTCDATETIME()) THEN 'Current week'
            ELSE 'Previous week'
        END AS review_period,
        SUM(rs.count_executions) AS executions,
        SUM(rs.avg_duration * rs.count_executions)
            / NULLIF(SUM(rs.count_executions), 0) AS weighted_avg_duration_us,
        SUM(rs.avg_logical_io_reads * rs.count_executions)
            / NULLIF(SUM(rs.count_executions), 0) AS weighted_avg_reads
    FROM sys.query_store_runtime_stats AS rs
    JOIN sys.query_store_runtime_stats_interval AS rsi
        ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
    JOIN sys.query_store_plan AS p
        ON p.plan_id = rs.plan_id
    JOIN sys.query_store_query AS q
        ON q.query_id = p.query_id
    JOIN sys.query_store_query_text AS qt
        ON qt.query_text_id = q.query_text_id
    WHERE rsi.start_time >= DATEADD(day, -14, SYSUTCDATETIME())
    GROUP BY
        q.query_id,
        p.plan_id,
        qt.query_sql_text,
        CASE WHEN rsi.start_time >= DATEADD(day, -7, SYSUTCDATETIME())
             THEN 'Current week' ELSE 'Previous week' END
)
SELECT TOP (25)
    query_id,
    plan_id,
    LEFT(query_sql_text, 1200) AS query_sql_text,
    MAX(CASE WHEN review_period = 'Previous week' THEN executions END) AS previous_executions,
    MAX(CASE WHEN review_period = 'Current week' THEN executions END) AS current_executions,
    CONVERT(decimal(18,2), MAX(CASE WHEN review_period = 'Previous week' THEN weighted_avg_duration_us END) / 1000.0) AS previous_avg_ms,
    CONVERT(decimal(18,2), MAX(CASE WHEN review_period = 'Current week' THEN weighted_avg_duration_us END) / 1000.0) AS current_avg_ms,
    CONVERT(decimal(18,2), MAX(CASE WHEN review_period = 'Previous week' THEN weighted_avg_reads END)) AS previous_avg_reads,
    CONVERT(decimal(18,2), MAX(CASE WHEN review_period = 'Current week' THEN weighted_avg_reads END)) AS current_avg_reads
FROM PeriodStats
GROUP BY query_id, plan_id, query_sql_text
HAVING MAX(CASE WHEN review_period = 'Current week' THEN executions END) IS NOT NULL
ORDER BY current_avg_ms DESC, current_avg_reads DESC;

SELECT
    ag.name AS availability_group_name,
    ar.replica_server_name,
    ars.role_desc,
    ars.connected_state_desc,
    ars.synchronization_health_desc AS replica_health,
    DB_NAME(drs.database_id) AS database_name,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc AS database_health,
    drs.is_suspended,
    drs.suspend_reason_desc,
    drs.log_send_queue_size,
    drs.redo_queue_size,
    sys.fn_hadr_backup_is_preferred_replica(DB_NAME(drs.database_id))
        AS is_preferred_backup_replica
FROM sys.availability_groups AS ag
JOIN sys.availability_replicas AS ar
    ON ar.group_id = ag.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states AS ars
    ON ars.replica_id = ar.replica_id
LEFT JOIN sys.dm_hadr_database_replica_states AS drs
    ON drs.replica_id = ar.replica_id
ORDER BY ag.name, ar.replica_server_name, database_name;