SQL Server stability 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_descFROM 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_onFROM sys.databasesWHERE 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_mbFROM sys.databases AS dLEFT JOIN sys.database_query_store_options AS qso    ON qso.database_id = d.database_idWHERE 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_msFROM sys.dm_os_wait_statsWHERE 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_statementFROM sys.dm_exec_requests AS rJOIN sys.dm_exec_sessions AS s    ON s.session_id = r.session_idOUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS stWHERE r.session_id <> @@SPIDORDER 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_timeFROM sys.query_store_query_text AS qtJOIN sys.query_store_query AS q    ON q.query_text_id = qt.query_text_idJOIN sys.query_store_plan AS p    ON p.query_id = q.query_idJOIN sys.query_store_runtime_stats AS rs    ON rs.plan_id = p.plan_idWHERE rs.last_execution_time >= DATEADD(day, -7, SYSDATETIME())GROUP BY qt.query_sql_textORDER 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_textFROM sys.dm_exec_requests AS rJOIN sys.dm_exec_sessions AS blocked_s    ON blocked_s.session_id = r.session_idLEFT JOIN sys.dm_exec_sessions AS blocker_s    ON blocker_s.session_id = r.blocking_session_idOUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS blocked_textOUTER APPLY sys.dm_exec_sql_text(blocker_s.most_recent_sql_handle) AS blocker_textWHERE r.blocking_session_id <> 0ORDER 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.messageFROM msdb.dbo.sysjobhistory AS hJOIN msdb.dbo.sysjobs AS j    ON j.job_id = h.job_idWHERE 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_noteFROM sys.databases AS dLEFT 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_descORDER BY d.name;

SELECT    name AS file_name,    type_desc,    physical_name,    size * 8 / 1024 AS size_mb,    growth,    is_percent_growth,    max_sizeFROM tempdb.sys.database_filesORDER 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_mbFROM 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_mbFROM sys.dm_db_session_space_usage AS suJOIN sys.dm_exec_sessions AS s    ON s.session_id = su.session_idORDER 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_msFROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfsJOIN sys.master_files AS mf    ON mf.database_id = vfs.database_id   AND mf.file_id = vfs.file_idORDER 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_sizeFROM sys.master_filesWHERE database_id > 4ORDER 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_enabledFROM msdb.dbo.sysjobs AS jLEFT JOIN msdb.dbo.sysoperators AS o    ON o.id = j.notify_email_operator_idORDER BY j.notify_level_email, j.name; SELECT    p.name AS profile_name,    pp.is_default,    a.name AS account_name,    a.email_addressFROM msdb.dbo.sysmail_profile AS pLEFT JOIN msdb.dbo.sysmail_principalprofile AS pp    ON pp.profile_id = p.profile_idLEFT JOIN msdb.dbo.sysmail_profileaccount AS pa    ON pa.profile_id = p.profile_idLEFT JOIN msdb.dbo.sysmail_account AS a    ON a.account_id = pa.account_idORDER BY p.name, a.name;