One script that checks the whole server and returns a single list, worst first: CRITICAL (fix now), WARNING (look soon), OK and INFO. It covers backups, CHECKDB, disk space, failed jobs, waits, memory, tempdb, settings and more. Health Check 01 to 12 show the detail behind each line. Needs SQL Server 2016 SP2 or later….
Version, patch level, edition, uptime, CPUs, memory and the key server settings in one place. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 01_Server_Overview.sql – version, uptime,…
Every database with its state, size, recovery model, what is holding up log reuse, and settings worth knowing (auto close, auto shrink, page verify, Query Store, RCSI, encryption). Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes…
The last full, differential and log backup of every database, hours since the last full, and the last 7 days of backup history. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server…
Free space on every drive that holds a database file, and each file’s size, space used and growth setting. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. –…
SQL Agent jobs that failed in the last 7 days, what is running now, and the last outcome of every job. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server 2017 and…
What SQL Server has spent its time waiting for since it started, with the harmless background waits left out, so the real bottleneck (CPU, disk, locks, memory, network) stands out. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area….
What is running right now and who is blocking whom. If sp_WhoIsActive is installed it uses that for more detail. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server 2017 and 2025….
Errors and warnings from the SQL Server error log in the last 7 days (current and previous log): failed logins, I/O warnings, deadlocks, memory paged out and so on. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only,…
When each database last passed DBCC CHECKDB, and any suspect (possibly corrupt) pages recorded in msdb. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 09_Integrity.sql – when…
CPU use for the last few hours (SQL Server and everything else), memory use, and which memory clerks are using it. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server 2017 and…
tempdb files and how full they are, the version store, and which sessions are using tempdb right now. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 11_TempDB.sql…
Health of availability groups, database mirroring and log shipping, whichever this server uses. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 12_High_Availability.sql – availability groups, mirroring and…
The queries using the most CPU, reads and time since their plans were cached. Change the ORDER BY to sort by reads or duration. For history across restarts use Query Store (Performance 02). Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 01_Top_Queries.sql – the queries using the most CPU, reads and time…
Run it in the database: queries that got slower in the last 24 hours than in the 7 days before, usually because the plan changed. Needs Query Store turned on. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 02_Query_Store_Regressions.sql – run IN the database: queries that got slower in the last 24…
Indexes SQL Server thinks would help, across all databases, since start-up, with a CREATE INDEX statement for each. Treat them as hints: check for overlap with existing indexes before creating any. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 03_Missing_Indexes.sql – indexes SQL Server thinks would help, across all databases, since start-up….
Run it in the database: indexes that are written to but never read since start-up. Only meaningful once the server has been up through a full business cycle (month end and so on). Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 04_Unused_Indexes.sql – run IN the database: indexes that are written to…
Run it in the database: statistics that are more than 7 days old or have had more than 20% of rows changed since they were last updated. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 05_Statistics_Age.sql – run IN the database: statistics that are old or have had many changes since they…
Run it in the database: fragmentation of every index over 1000 pages, using the light LIMITED scan. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 06_Index_Fragmentation.sql – run IN the database: fragmentation of indexes over 1000 pages (LIMITED scan, fairly light). Read-only. SET NOCOUNT ON; SELECT OBJECT_SCHEMA_NAME(ps.object_id) + N’.’ + OBJECT_NAME(ps.object_id) AS…
Finds where the optimiser’s row estimates are badly wrong, the root of most bad plans. Part 1 works on every version; part 2 (SQL Server 2019 and later) goes operator by operator through the last actual plan and shows the table, index and columns where a filtered statistic would help. Read-only, changes nothing. Tested on…
Run it in the database: columns whose data is lopsided enough that the normal statistics histogram misleads the optimiser, with the CREATE STATISTICS … WHERE statement that would fix it. It only suggests; it creates nothing. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. /* 08_Filtered_Statistics_Candidates.sql – run IN the database: columns whose…
Run it in the database: indexes that duplicate or are covered by another index, and what they cost in writes. It suggests DROP statements but runs none. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. /* 09_Duplicate_and_Overlapping_Indexes.sql – run IN the database: indexes that duplicate or are covered by another index, and what…
Run it in the database: the auto-statistics settings, how fresh and how well sampled every statistic is, and filtered statistics that have fallen behind. A fuller version of Performance 05. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. /* 10_Statistics_Health.sql – run IN the database: how fresh and how good every statistic is,…
Which cardinality estimator each database actually uses and why: compatibility level, LEGACY_CARDINALITY_ESTIMATION, trace flags and query hints, with advice on what to change. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. /* 11_CE_Configuration_Audit.sql – which cardinality estimator (CE) each database uses, and why. Clive Roberts DBA, 10 October 2026. Read-only. Server-wide. SQL Server…
Queries in the plan cache where SQL Server has to convert a column’s data type (usually varchar against nvarchar), which can turn a seek into a scan and ruin row estimates. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. /* 12_Implicit_Conversions.sql – queries in the plan cache where SQL Server has to convert…
Precious Lite 2025 | All Rights Reserved. Precious Lite theme by Flythemes