SQL Serverstability review guide

SELECT
    @@SERVERNAME AS server_name,
    SERVERPROPERTY(N'Edition') AS edition,
    SERVERPROPERTY(N'ProductVersion') AS product_version,
    SERVERPROPERTY(N'ProductLevel') AS product_level,
    SERVERPROPERTY(N'ProductUpdateLevel') AS product_update_level,
    si.sqlserver_start_time,
    DATEDIFF(hour, si.sqlserver_start_time, SYSDATETIME()) AS uptime_hours,
    si.cpu_count,
    si.scheduler_count,
    si.physical_memory_kb / 1024 AS physical_memory_mb,
    si.virtual_machine_type_desc
FROM sys.dm_os_sys_info AS si;

SELECT
    name AS database_name,
    state_desc,
    compatibility_level,
    recovery_model_desc,
    is_read_only,
    is_auto_close_on,
    is_auto_shrink_on,
    is_query_store_on
FROM sys.databases
WHERE name NOT IN (N'master', N'model', N'msdb', N'tempdb')
ORDER BY name;

SELECT
    d.name AS database_name,
    d.is_query_store_on,
    qso.actual_state_desc,
    qso.desired_state_desc,
    qso.readonly_reason,
    qso.current_storage_size_mb,
    qso.max_storage_size_mb
FROM sys.databases AS d
LEFT JOIN sys.database_query_store_options AS qso
    ON qso.database_id = d.database_id
WHERE d.name NOT IN (N'master', N'model', N'msdb', N'tempdb')
ORDER BY d.name;

SELECT TOP (40)
    wait_type,
    wait_time_ms,
    waiting_tasks_count,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms,
    CASE
        WHEN waiting_tasks_count = 0 THEN 0
        ELSE wait_time_ms / waiting_tasks_count
    END AS avg_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP',
    N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
    N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
    N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE',
    N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
    N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT',
    N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
    N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK',
    N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
    N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT', N'ONDEMAND_TASK_QUEUE',
    N'PARALLEL_REDO_DRAIN_WORKER', N'PARALLEL_REDO_LOG_CACHE',
    N'PARALLEL_REDO_TRAN_LIST', N'PARALLEL_REDO_WORKER_SYNC',
    N'PARALLEL_REDO_WORKER_WAIT_WORK', N'PREEMPTIVE_XE_GETTARGETSTATE',
    N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
    N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',
    N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH',
    N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
    N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY',
    N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
    N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP',
    N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
    N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES',
    N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
    N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
    N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT',
    N'XE_TIMER_EVENT'
)
ORDER BY wait_time_ms DESC;

SELECT
    r.session_id,
    s.login_name,
    s.host_name,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    r.cpu_time,
    r.logical_reads,
    r.reads,
    r.writes,
    r.total_elapsed_time,
    SUBSTRING(
        st.text,
        (r.statement_start_offset / 2) + 1,
        CASE
            WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), st.text))
            ELSE (r.statement_end_offset - r.statement_start_offset) / 2 + 1
        END
    ) AS running_statement
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

SELECT TOP (25)
    DB_NAME() AS database_name,
    qt.query_sql_text,
    SUM(rs.count_executions) AS executions,
    CONVERT(decimal(18,2), SUM(rs.avg_duration * rs.count_executions) / NULLIF(SUM(rs.count_executions), 0) / 1000.0) AS avg_duration_ms,
    CONVERT(decimal(18,2), SUM(rs.avg_cpu_time * rs.count_executions) / NULLIF(SUM(rs.count_executions), 0) / 1000.0) AS avg_cpu_ms,
    CONVERT(decimal(18,2), SUM(rs.avg_logical_io_reads * rs.count_executions) / NULLIF(SUM(rs.count_executions), 0)) AS avg_logical_reads,
    MIN(rs.first_execution_time) AS first_execution_time,
    MAX(rs.last_execution_time) AS last_execution_time
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
WHERE rs.last_execution_time >= DATEADD(day, -7, SYSDATETIME())
GROUP BY qt.query_sql_text
ORDER BY avg_duration_ms DESC;

SELECT
    r.session_id AS blocked_session_id,
    r.blocking_session_id,
    DB_NAME(r.database_id) AS database_name,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.status,
    r.command,
    blocked_s.host_name AS blocked_host,
    blocked_s.login_name AS blocked_login,
    blocker_s.host_name AS blocker_host,
    blocker_s.login_name AS blocker_login,
    SUBSTRING(
        blocked_text.text,
        (r.statement_start_offset / 2) + 1,
        CASE
            WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), blocked_text.text))
            ELSE (r.statement_end_offset - r.statement_start_offset) / 2 + 1
        END
    ) AS blocked_statement,
    blocker_text.text AS blocker_last_sql_text
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS blocked_s
    ON blocked_s.session_id = r.session_id
LEFT JOIN sys.dm_exec_sessions AS blocker_s
    ON blocker_s.session_id = r.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS blocked_text
OUTER APPLY sys.dm_exec_sql_text(blocker_s.most_recent_sql_handle) AS blocker_text
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

SELECT TOP (150)
    j.name AS job_name,
    h.step_id,
    h.step_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'
        ELSE CONVERT(varchar(20), h.run_status)
    END AS run_status,
    msdb.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 msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j
    ON j.job_id = h.job_id
WHERE h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -30, GETDATE()), 112))
  AND (h.run_status <> 1 OR h.run_duration >= 3000)
ORDER BY run_start_time DESC, duration_seconds DESC;

SELECT
    d.name AS database_name,
    d.recovery_model_desc,
    MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS last_full_backup,
    MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) AS last_diff_backup,
    MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS last_log_backup,
    CASE
        WHEN MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) IS NULL
            THEN 'No full backup found'
        WHEN d.recovery_model_desc IN ('FULL', 'BULK_LOGGED')
             AND MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) IS NULL
            THEN 'Full/Bulk-logged with no log backup found'
        WHEN MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) < DATEADD(day, -7, GETDATE())
            THEN 'Full backup older than 7 days'
        ELSE 'Review against RPO'
    END AS review_note
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS bs
    ON bs.database_name = d.name
   AND bs.backup_finish_date >= DATEADD(day, -60, GETDATE())
WHERE d.name <> N'tempdb'
GROUP BY d.name, d.recovery_model_desc
ORDER BY d.name;

SELECT
    name AS file_name,
    type_desc,
    physical_name,
    size * 8 / 1024 AS size_mb,
    growth,
    is_percent_growth,
    max_size
FROM tempdb.sys.database_files
ORDER BY type_desc, file_id;

SELECT
    SUM(user_object_reserved_page_count) * 8 / 1024 AS user_object_mb,
    SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_object_mb,
    SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb,
    SUM(unallocated_extent_page_count) * 8 / 1024 AS free_space_mb,
    SUM(mixed_extent_page_count) * 8 / 1024 AS mixed_extent_mb
FROM tempdb.sys.dm_db_file_space_usage;

SELECT TOP (25)
    s.session_id,
    s.login_name,
    s.host_name,
    (su.user_objects_alloc_page_count - su.user_objects_dealloc_page_count) * 8 / 1024 AS user_objects_mb,
    (su.internal_objects_alloc_page_count - su.internal_objects_dealloc_page_count) * 8 / 1024 AS internal_objects_mb
FROM sys.dm_db_session_space_usage AS su
JOIN sys.dm_exec_sessions AS s
    ON s.session_id = su.session_id
ORDER BY (su.user_objects_alloc_page_count + su.internal_objects_alloc_page_count) DESC;

SELECT
    DB_NAME(vfs.database_id) AS database_name,
    mf.type_desc,
    mf.physical_name,
    vfs.num_of_reads,
    vfs.num_of_writes,
    vfs.io_stall_read_ms,
    vfs.io_stall_write_ms,
    CASE WHEN vfs.num_of_reads = 0 THEN 0 ELSE vfs.io_stall_read_ms / vfs.num_of_reads END AS avg_read_latency_ms,
    CASE WHEN vfs.num_of_writes = 0 THEN 0 ELSE vfs.io_stall_write_ms / vfs.num_of_writes END AS avg_write_latency_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
    ON mf.database_id = vfs.database_id
   AND mf.file_id = vfs.file_id
ORDER BY avg_read_latency_ms + avg_write_latency_ms DESC;

SELECT
    DB_NAME(database_id) AS database_name,
    type_desc,
    name AS file_name,
    physical_name,
    size * 8 / 1024 AS size_mb,
    growth,
    is_percent_growth,
    max_size
FROM sys.master_files
WHERE database_id > 4
ORDER BY database_name, type_desc, file_name;

EXEC sys.xp_readerrorlog 0, 1, N'error';
EXEC sys.xp_readerrorlog 0, 1, N'severity';
EXEC sys.xp_readerrorlog 0, 1, N'failed';
EXEC sys.xp_readerrorlog 0, 1, N'deadlock';
EXEC sys.xp_readerrorlog 0, 1, N'timeout';
EXEC sys.xp_readerrorlog 0, 1, N'I/O';
EXEC sys.xp_readerrorlog 0, 1, N'corrupt';
EXEC sys.xp_readerrorlog 0, 1, N'Login failed';

SELECT
    j.name AS job_name,
    CASE WHEN j.enabled = 1 THEN 'Enabled' ELSE 'Disabled' END AS job_status,
    SUSER_SNAME(j.owner_sid) AS job_owner,
    j.notify_level_email,
    o.name AS email_operator,
    o.enabled AS operator_enabled
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.sysoperators AS o
    ON o.id = j.notify_email_operator_id
ORDER BY j.notify_level_email, j.name;

SELECT
    p.name AS profile_name,
    pp.is_default,
    a.name AS account_name,
    a.email_address
FROM msdb.dbo.sysmail_profile AS p
LEFT JOIN msdb.dbo.sysmail_principalprofile AS pp
    ON pp.profile_id = p.profile_id
LEFT JOIN msdb.dbo.sysmail_profileaccount AS pa
    ON pa.profile_id = p.profile_id
LEFT JOIN msdb.dbo.sysmail_account AS a
    ON a.account_id = pa.account_id
ORDER BY p.name, a.name;