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.

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

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

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

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.