CONTACT US

[2023] 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:

 

Checklist

Server level configuration

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

Windows OS updates settings
SQL services using non-SA account
Instant file initialization (IFI) access right
Dangerous SQL Server builds
SQL engine startup settings
CPU schedulers offline

SQL Server memory dumps

SQL Server error logs not optimal

Windows operating system settings

Windows OS page file configuration
Windows OS visual effects settings
Inspect Windows Task Scheduler
Windows OS Power Plan optimal
Lock pages in memory (LPM) setting
Page verification not optimal
Windows OS updates settings
Multiple RDP sessions

Window event viewer errors

SQL Instance configuration

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

Workload using SA account
Accounts with elevated permissions
CLR
Remote DAC

SQL instance options and features

Trace flag usage
SQL max RAM memory settings
Priority boost enabled
Missing alerts
Deprecated features in use
Query store not in use
SQL deadlocks
Orphaned data files
DBCC shrink ran recently
Change tracking (CDC) enabled
Default cost threshold for parallelism
Default max degree of parallelism
Wait statistics

Auto update statistics ASYNC not optimal

SQL Server tempdb config

tempdb configuration best practices

Database properties

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 SQL Agent Jobs

Delayed durability
Auto shrink ON
SQL Agent jobs starting simultaneously
SQL Agent jobs owned by non-SA account

SQL database owned by non-SA account

SQL objects owned by non-SA account
SQL Agent jobs history settings
SQL Agent jobs without notifications
Maintenance plans missing
Missing failsafe operator

Log files larger than MDF files

Backup Health Checks

Backups failing
Backups on drives where DB files are and backups on C:\
MSDB history not being purged
Snapshot backups occurring
Unnecessary backups
Missing corruption checks
Databases missing backups
Backups compression
Full recovery mode without log backup

Backups 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 configuration

Storage formatting
Drive performance
Slow storage latency by file
Storage performance or I/O warnings
Data files low on available space
Low disk space
All db files on single disk

Filegroups and files layout

DB files layout optimal
System or user databases placed on C:\
High Virtual Log Files (VLF) counts
DB file growth options
Optimize for ad-hoc workloads
LDF file too large
LDF file larger than MDF
User tables in system databases
DB compatibility setting
Objects created with SET Options
Maximum db file size is set
Database owner is non-SA account
DB state offline
Data compression not in use
Non-aligned indexes
Data and indexes within single filegroup or file
Filestream usage for large databases

System DB on OS drive

Performance checks of top SQL objects

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.

Investigate top worst server resources consumers

Top TSQL by disk reads
Top TSQL by CPU
Top TSQL by execution count
Top SPs by CPU usage
Top SPs By execution count
Top SPs by disk reads

Top SPs by average variable time

Backups consuming too many resources

Check constraints not trusted

Page life expectancy

Query plan analysis

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

SPs with RECOMPILE

Database objects and tables design

Active tables without clustering keys
Foreign keys or constraints not trusted
Triggers usage
Cursors usage
Queries forcing join and order hints

UDF usage

Indexes and statistics

Missing indexes
Useless and disabled indexes

Over-indexing
Fill factor
Auto-update statistics
Auto-update statistics asynchronous
User-created statistics

Statistics out of date

Elevate your SQL Servers with our managed services, performance tuning, and consulting services, tailored for companies seeking optimal database solutions.

Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

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 *