SQL Server Health Check

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

We then document any 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. We can also document everything if you prefer, but this is more time-consuming.  We explain 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?

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-premise 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-Premise 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 course in the new features 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 T-SQL scripts sculpted to your specific situation, this may incur extra time)
  • 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?

How Long Does the on-premise 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.

SQL Server Health Check Testimonials

“Having run a legacy SQL database for 10 years a recent move to a new datacentre led to exceptionally poor performance with the server frequently running at 100% CPU. Sequallity were approached and at short notice John was able to fault find our configuration and provide a complete health check on the system. Within 3 hours he’d managed to get the CPU back down to acceptable levels and then over the next two days carry out a full overhaul on the server and specifically optimise queries he saw suffering from poor performance. Finally he set up maintenance routines to keep the server optimised as we go forward.
Before getting John involved we were at the point of panic buying new SQL cores, at considerable cost, to solve the CPU issues. However having benefited from the three days of consultancy we now have a system running faster than ever before with no need of hardware updates.”

Darren Smith
IT Development Manager
PLUSS
August 2018

Here is the before and after Activity Monitor screenshots. Notice how the CPU has dropped significantly, the Waiting Tasks have disappeared and the throughput has gone from 300 SQL statements per second to over 1000!

SQL Server Health Check – Before and After Activity Monitor Screen Captures

After the maintenance fully kicked in the client reported a 5%-9% average CPU, down from 100% at times and that ain’t bad!