SQL Server Health Check

[2024] 145 Point SQL Server Database Health Checklist

Mark Varnas
113 comments

Our Full SQL Server Health Check covers complete SQL Server health check of on-premises and cloud-based databases, including the analysis of:

  • Hardware
  • Operating system
  • SQL Server configuration
  • Security (users)
  • Availability
  • Load (amount and type of access)
  • Operation (maintenance and monitoring plans)
  • Disaster recovery
  • And many more…

Some clients wish to do a full SQL Server health check, while others prefer a detailed analysis of a single database health check.

Even though it involves a single DB health analysis, we still examine best practices and optimal settings at both server and instance levels, covering aspects of performance, reliability, and security.

That is intentional.

After the SQL Server 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 out our SQL Server Health Check case study!

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

  1. Server-level configuration
  2. SQL Instance configuration
  3. Database properties
  4. Architectural design overview
  5. Performance checks of top SQL objects

Note: Some items in our 145 Point SQL Server Database Health Checklist include several checks within a single item.

Big Data Storage and Cloud Computing Representation. Programming - Maximusnd Zahar

Checklist

1. Server-level configuration

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

A comprehensive methodology will be applied to examine various SQL-dependent levels, including NUMA, clustering and failover, BIOS, VMware setup, networking, and several other areas.

It may seem like 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

Windows operating system settings

2. SQL Server Instance configuration

Is your SQL Server with the latest SQL Service Pack and Cumulative Updates?

How about SQL Server Management Studio (SSMS)? 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 the SQL Server 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 reliability and security first.

Security

SQL instance options and features

SQL Server tempdb config

3. 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 Server Agent jobs are set up?
  • 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 Server Agent Jobs

Backup Health Checks

4. Architectural design overview

How is the majority of T-SQL coming? Is it ad-hoc SQL Server 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

Filegroups and file layout

5. Performance checks of top SQL Server 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, and statistics on those tables.

Which T-SQL runs most frequently? Which one takes the longest to execute? Which consumes the most CPU, RAM, and I/O (disk)?

Is there anything that requires too many resources?

If T-SQL 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 it is.

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 the top worst server resources consumers

Query plan analysis

Database objects and tables design

Indexes and statistics

Article by
Mark Varnas
Founder | CEO | SQL Veteran
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.

113 thoughts on “[2024] 145 Point SQL Server Database Health Checklist”

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

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

    Reply
    • 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
  3. 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
  4. 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
  5. would you please share me sample report pls……
    &
    happy to inform you that you people have done wonderful work & it’s really helpful.

    Reply
  6. 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
  7. 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 Comment

Managed SQL Server services, consulting, and emergency support from expert DBAs to improve performance, predictability, and cost.

Get started with Red9 today.

Contact us

Discover More

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials