What is a SQL Server Health Check?

We connect to your SQL Server and we run through our SQL Server Health Check list of key performance indicators, taking screenshots and capturing server related meta-data. You may well have a performance issue, or you might just want to check that everything is ok or you’ve got a new intensive code release, how is this going to impact the users and your overall SQL Server performance and throughput?

The SQL Server Health Check Report

The document contains all of our findings.  We prefer to use Exception Reporting, whereby we only document the problems we’ve encountered and the important points to put the focus clearly on the problems, to make it easy for you. Sequallity can also document everything if you prefer, but this is more time-consuming.  The document explains our findings and recommended resolutions in a clear and concise way.

Our clients tend to have regular monthly/quarterly/yearly SQL health checks, depending on the amount of change to their systems.  The documentation we provide is a great point-in-time snapshot for the current state of the system and comes in handy for reference against future SQL health checks. Is it worse or better than the last SQL health check?

This was the contents page from a recent one day SQL health check.   As you can see, we cover a lot of points in just one day. With a one-day health check we can find the worst performing queries, theres just not enough time to investigate to re-code them, you need a two-day SQL health check for that.

SQL Server Health Checklist

We have over 100 points that we check covering configuration, throughput and identifying bottlenecks. Originally, we used SQL Server Profiler but now we use a combination of Profiler, Extended Events, DMVs and Resource Waits among many other native tools provided with Windows and SQL Server. We know how to use these tools so the impact on your system is negligible.

SQL Server Health Check Script

Wait, do you run a huge script against our SQL Server? No, we run individual SQL Server queries typically querying the DMVs and returning data one set at a time, with minimal impact on your Live or DEV or UAT production servers.

SQL Server Health Check Pedigree

Sequallity have provided SQL Server Health Checks to our clients since 2005.  It has been tuned and expanded over many years as SQL Server has evolved. We keep abreast of the new features of SQL Server in order to add value to our recommendations. We are fast approaching 1000 engagements so we have completed hundreds of SQL Server Health Checks since 2005.

What Types of SQL Server Health Check does Sequallity Deliver?

We can perform our health checks on

• On-premises or cloud hosted SQL Server on Windows and on top of Hyper-V or VMWare
• Microsoft Azure SQL Database
• Amazon RDS for SQL Server

On-Premises and Cloud SQL Health Check

The cloud health checks are slightly different, as with the cloud version there is no need to check Windows and its configuration, we concentrate mostly on the SQL queries and maintenance.

New Features of SQL Server

SQL Server has grown tremendously since version 6.0, the first version we ever used.  There are many new features.  We give training courses in the New Features of SQL Server so we can suggest an upgrade path or using a new feature that may benefit you, like Columnstore Indexes, wow, they’re great for OLTP reporting and page compression, now available in SQL Server Standard. Great if you’ve got spare CPU and a disk bottleneck. SQL Server 2016 Standard Service Pack 1 onwards is amazing, it’s amazing. It’s so good I said it twice there.

Changes

You don’t change the environment do you? No, we certainly do not.  However, if you want us to make changes for you on your behalf, we would do, as we know what we’re doing and what the potential impact could be.  As long as you tell us that’s ok, then we’re ok with this.

What are the Typical Points You Investigate?

It does vary from system to system as we drill closer into the problem(s). Typically, we are trying to align your SQL Server to best practice. With years of monitoring and watching how SQL Server works under stress and then a new lightened workload, we know the beats of SQL Server and what it should be doing. Seeing the impact of a change like Cost Threshold for Parallelism can bring to a SQL Server system is the best.

These are just some of the points we check:

• CPU/Ram/Memory/Network
• Benchmarking Performance
• Windows, SQL Applications, SQL Server and Database Configuration
• Optimal Disk Set-Up and Disk Space plus for Virtualisation
• External and Internal Fragmentation
• System Databases
• Most Intensive Queries by CPU, Reads/Writes and Duration
• Indexes, Statistics and Maintenance (we can also create maintenance plans and/or T-SQL scripts sculpted to your specific situation, this may incur extra time, but it will be worth it!)
• Observations or Recommendations about High Availability, Disaster Recovery and Optimal Backup and Restore
• Windows and SQL Server Error Log Analysis
• Security Analysis
• Blocking, Deadlocking and Timeouts
• Future Capacity
• Edition Suitability, could you save money with Standard licenses?
• New Features applicable to your database system (In-memory OLTP databases for data consumption)

This is an evolving process we are always adding to, building and re-shaping.

T-SQL Code Recommendations

We can look at your T-SQL code, and recommend new ways of tuning your SQL queries to make the whole system faster. Tuning and optimisation are our core skills, you understand how to tune a car, you understand cars right? We tune SQL.

How Long Does the on-premises SQL Server Health Check Take?

Normally, it can take two days to analyse all the above and more. However, if you are on a budget, we can fit a slimmed down SQL Server health check into one day. Here, on a 1-day SQL health check, we only point out the slower queries, we do not recommend any performance fixes for the queries. On a 2-day SQL health check, we analyse and suggest alternative methods or improvements with re-coding or new indexes, if possible.

How Long Does the cloud SQL Server Health Check Take?

With a Microsoft Azure SQL Database or Amazon RDS for SQL Server Health Check, the two days are spent identifying the poorly performing queries, timeouts, blocking and deadlocking and documenting and recommending re-coding to avoid such situations.

On-site or Remote SQL Server Health Check?

The SQL Server Health Check can be performed on-site or remotely. If you’re in the UK and not too far from our offices then on-site suits us down to the ground.  We also train in SQL Server and other data related technologies so we explain fully the problems and solutions.