A SQL Server health check is a structured review of your database environment covering backup integrity, index health, security configuration, performance baselines, and server-level settings. A thorough sql server health check service will identify misconfigurations, resource bottlenecks, and silent failures before they cause outages or data loss. Based on DBA Services' experience across hundreds of Australian SQL Server environments, more than 90% of servers we assess have at least one critical finding that the internal team was unaware of.

Running SQL Server without regular health checks is like skipping car servicing and hoping nothing breaks. It works, until it doesn't. The difference is that a database failure can mean hours of downtime, corrupted data, or a compliance breach. This checklist covers the six areas that matter most, with practical T-SQL you can run today.

What Does a SQL Server Health Check Actually Cover?

A proper sql database health check is not just running sp_Blitz and calling it done. It covers six core areas: backup and recovery validation, index and statistics health, security and access controls, server configuration, wait statistics and query performance, and database integrity. Each area has its own failure modes, and missing any one of them leaves real risk on the table.

The checklist below is the same framework DBA Services uses when conducting a sql server performance audit for new clients. Work through it systematically and document your findings. You'll almost certainly find something worth fixing.

Are Your Backups Actually Working?

Backups are the single most important thing to verify, and they're also the most commonly neglected. A backup job that completes with a green tick does not guarantee a restorable database. Verify both the backup history and the actual backup files.

Run this query to identify databases that haven't had a full backup in the last 24 hours:

SELECT
    d.name AS DatabaseName,
    MAX(b.backup_finish_date) AS LastFullBackup,
    DATEDIFF(HOUR, MAX(b.backup_finish_date), GETDATE()) AS HoursSinceBackup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b
    ON d.name = b.database_name
    AND b.type = 'D'
WHERE d.database_id > 4  -- exclude system databases
GROUP BY d.name
HAVING MAX(b.backup_finish_date) < DATEADD(HOUR, -24, GETDATE())
    OR MAX(b.backup_finish_date) IS NULL
ORDER BY HoursSinceBackup DESC;

Check the following items systematically:

  1. Full backups completed successfully within the last 24 hours for all production databases
  2. Transaction log backups running at least every 15-60 minutes for databases in FULL recovery model
  3. Backup files physically exist on the target destination (not just recorded in msdb)
  4. At least one restore test completed in the last 30 days
  5. Backup retention meets your RPO requirements and any compliance obligations

A missing transaction log backup chain is a silent killer. If your database is in FULL recovery model but log backups aren't running, you're carrying the overhead of FULL recovery without any of the point-in-time restore benefit.

Is Index Fragmentation Hurting Your Query Performance?

Index fragmentation above 30% can degrade read performance noticeably, particularly on range scans and reporting queries. The fix is straightforward, but many environments either never rebuild indexes or run blanket nightly rebuilds that ignore whether fragmentation is actually a problem.

Use this query to identify your worst offenders:

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    s.avg_fragmentation_in_percent,
    s.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') s
JOIN sys.indexes i
    ON s.object_id = i.object_id
    AND s.index_id = i.index_id
WHERE s.avg_fragmentation_in_percent > 30
    AND s.page_count > 1000
ORDER BY s.avg_fragmentation_in_percent DESC;

The general guidance from Microsoft is:

  • Fragmentation between 5% and 30%: reorganise (ALTER INDEX ... REORGANISE)
  • Fragmentation above 30%: rebuild (ALTER INDEX ... REBUILD)
  • Indexes with fewer than 1,000 pages: fragmentation rarely matters enough to act on

Also check statistics update dates. Outdated statistics cause the query optimiser to generate poor execution plans, which often looks like an index problem but isn't:

SELECT
    OBJECT_NAME(s.object_id) AS TableName,
    s.name AS StatisticName,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE sp.last_updated < DATEADD(DAY, -7, GETDATE())
    AND sp.rows > 100000
ORDER BY sp.last_updated ASC;

What Are the Most Common SQL Server Security Gaps?

Security is one of the areas where a sql health check adds immediate, concrete value. Most environments accumulate security debt quietly. Old logins, overly permissive roles, and SA accounts left enabled are the norm, not the exception.

Check these items during every health review:

  • SA account status: the SA login should be disabled or renamed if it's not required
  • SQL Server logins with sysadmin role: there should be very few, and each one should be justified
  • Orphaned database users: users that exist in a database but have no corresponding server login
  • Databases with TRUSTWORTHY set to ON: this setting is rarely needed and carries real risk
  • Linked server configurations: review what credentials are used and what access they grant

Run this to list all sysadmin logins:

SELECT
    sp.name AS LoginName,
    sp.type_desc AS LoginType,
    sp.is_disabled,
    sp.create_date,
    sp.modify_date
FROM sys.server_principals sp
JOIN sys.server_role_members srm
    ON sp.principal_id = srm.member_principal_id
JOIN sys.server_principals r
    ON srm.role_principal_id = r.principal_id
WHERE r.name = 'sysadmin'
ORDER BY sp.name;

If that list has more than five entries and you can't name every person or service account on it, that's a finding worth addressing immediately.

How Do You Identify SQL Server Performance Bottlenecks?

Wait statistics are the most reliable way to diagnose where SQL Server is spending its time. Rather than guessing, you can query sys.dm_os_wait_stats to see exactly what resource the server is waiting on most.

SELECT TOP 20
    wait_type,
    waiting_tasks_count,
    wait_time_ms / 1000.0 AS wait_time_seconds,
    (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_seconds,
    signal_wait_time_ms / 1000.0 AS signal_wait_seconds
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP',
    'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
    'FT_IFTS_SCHEDULER_IDLE_WAIT', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
    'HADR_WORK_QUEUE', 'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE',
    'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH',
    'RESOURCE_QUEUE', 'SERVER_IDLE_CHECK', 'SLEEP_DBSTARTUP',
    'SLEEP_DCOMSTARTUP', 'SLEEP_MASTERDBREADY', 'SLEEP_MASTERMDREADY',
    'SLEEP_MASTERUPGRADED', 'SLEEP_MSDBSTARTUP', 'SLEEP_SYSTEMTASK',
    'SLEEP_TEMPDBSTARTUP', 'SNI_HTTP_ACCEPT', 'SP_SERVER_DIAGNOSTICS_SLEEP',
    'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'XE_DISPATCHER_WAIT',
    'XE_TIMER_EVENT'
)
ORDER BY wait_time_ms DESC;

Common wait types and what they indicate:

  • PAGEIOLATCH_SH / PAGEIOLATCH_EX: disk I/O pressure, often from missing indexes or insufficient memory
  • **LCKM***: locking and blocking, usually a query design or transaction management issue
  • CXPACKET / CXCONSUMER: parallelism, worth checking your MAXDOP and cost threshold settings
  • SOS_SCHEDULER_YIELD: CPU pressure, look at your most expensive queries first

A well-tuned server should have no single wait type dominating the list. If one wait type accounts for more than 40% of total wait time, that's your starting point.

Key Takeaways

  • A sql server health check service should cover six areas: backups, indexes and statistics, security, server configuration, wait statistics, and database integrity. Missing any one of them leaves real risk unaddressed.
  • Backup validation is not just checking job history. Verify that files exist, restore tests are passing, and log backup chains are intact.
  • Index fragmentation above 30% warrants a rebuild. Below that, focus on statistics freshness before assuming indexes are the problem.
  • Wait statistics from sys.dm_os_wait_stats give you an objective, data-driven view of where SQL Server is actually struggling. Start there for performance investigations.
  • Security debt accumulates quietly. Sysadmin logins, enabled SA accounts, and TRUSTWORTHY databases are findings in the majority of environments we assess.

What to Do Next

Working through this checklist will surface issues. The harder question is prioritising what to fix first and knowing whether what you're seeing is normal for your workload or a genuine risk.

DBA Services provides a professional sql server health check service for Australian organisations running SQL Server on-premises, in Azure, or in hybrid environments. Our sql server performance audit covers all six areas in this checklist and delivers a prioritised findings report with specific remediation steps. We don't hand you a generic script output. You get a senior DBA's assessment of what matters in your environment.

If you'd like us to conduct a health check on your SQL Server environment, contact DBA Services today. Most assessments are completed within five business days and frequently uncover issues that have been quietly affecting performance or reliability for months.