[2020 Single database] SQL Server health check

This SINGLE Database Health Check is a customized service focused on delivering clear pieces of evidence about the status of your database system performance and maintenance strategy.

Our goal is to make it as easy as possible for you to understand.

After the analysis is done and presented to you, we will go over everything on the report until you run out of questions.

All of the checks are non-intrusive, very lightweight and will not affect your production SQL Server.

All data collection can be done during normal business operating hours.

This is about 90% of what will happen.

The remaining 10% will be spent on items discovered during analysis, requiring more analysis.

Here is what a database health check will check for:

Health Check (the list of items verified)

Reliability

Anti-Virus settings
Backups on live DB drivers
Backups compression settings
CLR enabled
Corruption in data
Dangerous SQL Server builds
DBs missing BKPs
DBs owner by non SA account
Deadlocks
Deprecated features in use
File growth options
Full recovery mode without backup logs
High RDP User access
Log files larger than MDF files
Low disk space
Maintenance plans missing
Max DB file size set
Missing alerts
Missing corruption checks
No failsafe operator
DB compatibility setting
System BIOS updates
Orphaned data files
OS updates settings
Page verification not optimal
Priority boost enabled
Remote DAC disable
Simple recovery model usage
SQL agent jobs history settings
SQL agent Jobs without notifications
SQL engine startup settings
SQL Server memory dumps
SQL Server error logs not optimal
SSMS cannot open SQL error logs
SSMS missing updates
System DB on OS drive
User tables in sys DBs
Wait statistics
Window event viewer errors

Security

Accounts with elevated permissions
Backups failing
DB state offline
SQL agent jobs owned by non SA account
SQL services using non SA account
Too many SA users
Workload using SA account

Performance

Active tables Without clustered IX
All DB files on single disk
Auto update statistics ASYNC not optimal
Auto update statistics not optimal
Autoshrink ON
Backups consuming high resources
BKPs over UNC
Change tracking enabled
Check constraints not trusted
CPU schedulers offline
Cursor usage
Data and IXs within single filegroup/file
Data compression not in use
Database have non-aligned indexes
DB BKP compression
DB data file low on available space
DB file layout non optimal
DBCC shrink ran recently
Default cost threshold for parallelism
Default max degree of parallelism
Default tempDB size
Delayed durability not in use
Drive performance
Fill factor changed
Foreign keys not trusted
Handling of large tables
High index fragmentation
High tempDB usage
High VLF counts
Implicit conversion
Instant file initialization access right
Large LDF file
Lock pages in memory setting
Many plans for one query
Missing indexes
MSDB BKPs history not being purged
Multiple SQL Server instances
Objects created with SET Options
Optimize for ad hoc workloads
OS page file not optimal.
OS visual effects not optimal
Over-indexing
Page life expectancy
Queries forcing join hints
Queries forcing order hints
Query store not in use
Single use plans in the SP cache
Slow storage
Slow storage latency by file
Snapshot BKPs occurring
SPs with RECOMPILE
SQL agent jobs starting simultaneously
SQL max memory settings not optimal
Statistics out of date
Storage formatting not optimal
Storage perf warnings and problems
Task scheduler
Tempdb with a sigle data file
Top TSQL by reads
Top TSQL by CPU
Top TSQL by execution count
Top SPs by average variable time
Top SPs by CPU usage
Top SPs By execution count
Top SPs by I/O
Trace flags
Trigger usage
Unnecessary backups
Unnecessary SQL services
Useless IXs
User databases on OS disk
User-created statistics
Wait statistics clearing
Windows power plan

Mark Varnas

Mark Varnas

I love making performance tuning SQL Servers fast and making them more stable. And I channel that obsession into our SQL Managed Services and new content here. When I'm not writing about SQL, I spend time outside hiking, skiing, mountain biking, or trying a new recipe.

Leave a Reply

Your email address will not be published. Required fields are marked *

Call Us Now

Or, Let Us Know How We Can Help

Tight On Time?

Schedule A Call: