Main Menu
CONTACT US

[2022] 145 Point SQL Server Database Health Checklist

  • 107 Comments

SQL Server Healhcheck ilustration.

Our FULL SQL Server health check will cover a complete SQL Server health checklist of items on-premises and cloud-based DBMS services.

Hardware, operating system, SQL server configuration, security (users), availability, load (amount and type of access), operation (maintenance and monitoring plans), disaster recovery, and much more will be analyzed.

Some clients want to check server health (FULL SQL Health Check), while some only want to go deep on analysis for a SINGLE database health check.

Even though it is a single DB health analysis, we still check for best practices and optimal settings in server and instance-level items, covering performance, reliability, and security points.

That is intentional.

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

All the SQL checks are non-intrusive, very lightweight, and will not affect the production SQL Server. Data collection can be done during regular business operating hours.

This is about 90% of what will happen. The remaining 10% will be spent on items discovered during analysis, requiring more investigation. So, we will drill into that more as we find stuff.

Check our SQL Health Check case study!

Here is the complete list of items verified for an on-premise Microsoft SQL Server environment:

MS SQL Server Health Check Checklist

Server level configurations

Ensure that all your SQL Server services are working correctly according to your needs.

A comprehensive methodology will be applied to look at various levels SQL depended on NUMA, Clustering and failover, BIOS, VMWare setup, networking, and few more areas.

It may seem like an overkill to look at all the layers, but Red9 practices prove that comprehensive analysis is useful.  Especially when stability and speed matter.
Check out some of the items below.

Services configurations

Anti-Virus settings
Multiple SQL Server instances
Unnecessary SQL services
SSMS missing updates
System BIOS updates
SQL services using non SA account
Instant file initialization access right
Dangerous SQL Server builds
SQL engine startup settings
CPU schedulers offline

Windows operating system settings

Windows page file configuration
Windows visual effects settings
Inspect task scheduler
Windows power plan optimal
Lock pages in memory setting
Page verification not optimal
Windows updates settings
Multiple RDP sessions actives

Instance parameters and options

Is your SQL Server with the latest SQL Service Pack and Cumulative Updates? How about SQL Server Management Studio? Are the parallelism settings matching the workload? Is deadlocking happening? How heavy is tempDB usage?

Are the features being appropriately utilized? Make sure you are taking advantage of some of the less known features.

We will isolate top server waits since SQL instance starts and for 30-60 min (production workload) to determine the client’s main pains.

When your database is corrupt, hacked, or destroyed – slow queries will not matter. So, that is why we will address the Reliability and Security first.

Security points

Workload using SA account
Accounts with elevated permissions
The CLR integration
Remote DAC disable

Instance options and features

Trace flags usage
SQL max memory settings
Priority boost enabled
Missing alerts
Deprecated features in use
Query store not in use
SQL server blocks, locks and deadlocks
Orphaned data files
DBCC shrink ran recently
Change tracking enabled
Default cost threshold for parallelism
Default max degree of parallelism
Wait statistics

SQL Server tempDB configuration

Default tempDB size settings
TempDB with a single data file

Database properties and level checks

The purpose is to determine if your database’s properties for each of your SQL Server instances are appropriately configured for your particular environment.

How SQL Agent jobs are setup? Is there a Maintenance plan? How are DB backups set up? When was the last corruption check date? Is the transaction log larger than the DB data file?

Our goal is to ensure the highest SQL Server performance and availability of your databases.

Database options and Agent Jobs

Delayed durability not in use
Autoshrink ON
SQL agent jobs starting simultaneously
SQL agent jobs owned by non SA account
SQL agent jobs history settings
SQL agent jobs without notifications
Maintenance plans missing
Missing failsafe operator

Backup Health Checks

Backups failing
Backups on live DB drivers (bkp files on C drive)
MSDB BKPs history not being purged
Snapshot BKPs occurring
Unnecessary backups
Missing corruption checks
DBS missing BKPs
Backups compression settings
Full recovery mode without backup logs
Backups consuming high resources
BKPs over UNC
Simple recovery model usage

Architectural design overview

How is the majority of TSQL coming? Is it ad-hoc SQL queries or stored procs? Are files placed on best matching drives? How often do DB files grow? What causes the delay? Are you using third-party monitoring tools?

What is the read vs. write ratio at the DB file level? This will show if the workload is read or write-intensive, which changes how tuning is done.

We will test drive latencies and review how well DB files are placed across storage drives.

Also, our team will find top used objects: look at the sample data, data types in use, indexes, foreign keys, etc.

Disks and storage configurations

Storage formatting optimal
Drive performance
Slow storage latency by file
Storage perf or I/O warnings and problems
DB data file low on available space
Low disk space
All DB files on single disk

Filegroups and files layout

DB files layout optimal
system or users DBs on OS drive
High Virtual log files (VLF) counts
File growth options
Optimize for ad-hoc workloads
Large LDF file
Log files larger than MDF files
User tables in sys DBs
DB compatibility setting
Objects created with SET Options
Max DB file size set
DBS owner by non SA account
DB state offline
Data compression usage
Database with non-aligned indexes
Data and IXs within single filegroup/file
Filestream for large databases

Top SQL objects and performance checks

What are the tables with the most activity? What are the largest tables?

Are there tables without clustering keys? Are there any foreign keys that are not trusted?

Red9 will review top objects (usually up to 5-10 is sufficient) to see how well data type choices were made, indexing, constraints, triggers, statistics on those tables.

Which TSQL runs most frequently? Takes longest to execute? Which Consumes the most CPU, RAM and I/O (disk)?

Anything that costs too much in resources? If TSQL duration is sometimes fast and sometimes very slow, this is an opportunity for performance tuning.

Is there implicit data conversion happening? Are forced join hints? Are triggers being used for auditing? Are there UDFs in use? If yes, we will review how bad.

Additionally, our team will check how up to date are statistics and fragmentation levels.

The database indexes will be carefully analyzed – Often the fastest way to add speed to DB  performance.

Review top worst performers

Top TSQL by reads
Top TSQL by CPU
Top TSQL by execution count
Top SPs by CPU usage
Top SPs By execution count
Top SPs by I/O

Query plan analysis

Single-use plans for one query
Many plans for one query
Implicit data conversion

Database objects and tables designs

Active tables without clustering keys
Foreign keys or constraints that are not trusted
Tables over-indexed
Triggers usage
UDFs and Cursors usage
Queries forcing join and order hints

Indexes and statistics

Missing indexes
Useless and disabled indexes
Fill factor option
Auto-update statistics
Auto-update statistics asynchronous
User-created statistics

Want a FREE SQL Server health check sample report? Send an email using our contact us form, and say that you saw this post, and I will email it to you.

For fellow DBAs – what did I miss? What can be done better? 

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.

107 Responses

  5. Excellent piece of writing. Please allow me to have a copy of the sample report.
    Regards And thank you.
    Frank

    Reply

  9. Hi
    Excellent and highly apprecieted the way explained and very documented.
    could you please share the sample report

    Reply

  12. Hi, Your inputs regarding MS SQL Server are excellent. Can I receive the sample report? Thanks, Regards

    Reply

  13. Thanks for sharing all the Details with us. Could you please share a sample report ?

    Reply

    1. Hi, Bill. Just checked out link you sent. Pretty cool. I will digging into it deeper. It looks like our SQL Health Check at least checks 95% of these already 🙂

      Your SQL Health Check Sample email is on the way.

      Any questions – let me know! Enjoy!

      Reply

  38. Hi, all! This is a great, helpful article. I’d love to see a sample report if you are offering to still send one out!

    Reply

  41. thanks for great explanation. would you please mail a single script which will help to get the complete report of server and db level.

    Reply

  43. would you please share me sample report pls……
    &
    happy to inform you that you people have done wonderful work & it’s really helpful.

    Reply

  51. Could you please send the sample report and the queries you are using if you have it handy if not thats okay .Thank you!

    Reply

  54. Hello, I have created a simple queries based on your explanations, thank you. Also could you please send the sample report?
    Thanks!

    Reply

Leave a Reply

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