Case study – Slow storage affecting SQL Server performance

Problem Summary:

Queries take a longer time than usual in processing.

Suddenly, the queries that were fast before became slower.

There are many reasons why a SQL server may be running slow. Finding the root cause of a slow server is complicated because of the extensive list of potential culprits.

Detecting the issue

Most of the time, SQL Server’s poor performance is related to I/O (Disk) performance. Unfortunately, it can be challenging to work out what’s causing the problem without a significant investment of time and effort.

We use a comprehensive methodology for identifying the root causes of your faults or problems. When conduct quickly and efficiently, it can save you a great deal of trouble, time, and resources.

The SAN admin’s been telling you everything is fine and that it must be a SQL Server problem? Well, maybe.  When we compared how SAN theoretically advertised maximum performance compared to what I was seeing, those two numbers never match – but that’s OK.  The real question is, how far apart are we?

Based on the image below, we can see that this server storage has troubles. High latency is present under low disk usage 7MB/s. It is not the performance expected from a tier 1 disk.

Figure 1- High response time (latency) under low disk usage.

What was done:

  1. Saved the actual SAN performance metrics using the CrystalDiskMark (The Peak Performance + Mix setting );
  2. An In-depth investigation allowed us to identify the root problem: the default Windows power plan (Balanced).
  3. After fixing the OS profile to “High Performance”, we tested the disk performance again and compared it with the baseline results. As expected, we confirm the issues’ root.

Comparing the results

Before:

After:

In short,  there are many tactics and methods to ensure that your storage systems perform adequately, and are capable of handling peak load requirements.

Whatever SQL Server issues you are experiencing, our SQL experts can quickly and safely resolve your database issues.

Our SQL support and emergency tasks team that is available 24 hours per day, 365 days per year.

See more SQL Server Performance Optimization examples here.

We tune slow SQL Servers every day. For over a DECADE now. Contact us!

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.

Leave a Reply

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