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.
What was done:
- Saved the actual SAN performance metrics using the CrystalDiskMark (The Peak Performance + Mix setting );
- An In-depth investigation allowed us to identify the root problem: the default Windows power plan (Balanced).
- 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.
