Mustafa EL-Masry

How to analyze Storage Subsystem Performance in SQL Server

February 23, 2017 by

Introduction

To improve performance, it is common for DBAs to search in each aspect except analyzing storage subsystem performance even though in many times, issues are, in fact, caused by poor storage subsystem performance. Therefore, I want to give you some tools and recommendation that you can use it to prevent your storage subsystem from being a performance issue for you.

In this article, I will cover how to measure and analyze your storage subsystem performance and how to test your storage subsystem including

  1. Main metrics for storage performance
  2. Operating System Tools to measure storage performance
  3. SQL Server Tools to measure storage performance
  4. Using SQL Server to test storage performance

Main metrics for storage performance:

In this section I will introduce the three main metrics for the most storage performance issues as follows:

  1. Latency

    Each IO request will take some time to complete this latency is measured in milliseconds (ms) and should be as low as possible

  2. IOPS

    IOPS means IO operations per second, which means the amount of reading or write operations that could be done in one second. A certain amount of IO operations will also give a certain throughput of Megabytes each second, so these two are related

  3. Throughputs

    The most common value from a disk manufacturer is how much throughput a certain disk can deliver. This number usually expressed in Megabytes / Second (MB/s), and it is simple to believe that this would be the most important factor

Operating System Tools to measure storage performance.

Two main basics Microsoft tools dedicated to analyzing the measure storage subsystem performance in Windows are windows performance monitor and windows resource monitor.

Windows Performance Monitor is a Microsoft Management Console (MMC) that you can use to monitor application and hardware performance in real time. It also provides a disk-level disk performance monitor. For more details, look it here Overview of Windows Performance Monitor


Figure 1 – Windows Performance Monitor

Microsoft Windows Resource Monitor displays information about the use of hardware (CPU, memory, disk, and network) and software (file handles and modules) resources in real time. However, it differs from Windows Performance Monitor as it provides you with information on the file-level


Figure 2 – Windows Resource Monitor

SQL Server Tools to measure storage performance.

Another tool you can use to measure your storage subsystem performance is a set of SQL Server diagnostic T-SQL queries. I will use here one of the SQL Server tools; it is called DMV T-SQL { SQL Server Dynamic Management views } It consists of built-in views in SQL Server that we can use to measure our storage subsystem performance. This tools will let you collect some very useful data about your storage subsystem performance from a SQL Server perspective.

Check Drive level latency:

By this query, we will able to check the drive latency information for reads and writes, in milliseconds and based on my experience I found that the Latency above 20-25ms is usually a problem

  • brilliant: < 1ms
  • great: < 5ms
  • good quality: 5 – 10ms
  • Poor: 10 – 20ms
  • horrific: 20 – 100ms
  • disgracefully bad: 100 – 500ms
  • WOW!: > 500ms


Figure 3 – Drive level latency

Check I/O utilization by database

This query will give us good indicator about which database is using the most IO resources on my server.


Figure 4 – Microsoft Databases I/O utilization

Check I/O Statistics by file for the current database:

This query helps you characterize your workload better from an I/O perspective for this database. Also, it helps you to determine whether you have an OLTP or DW/DSS type of workload


Figure 5 – I/O Statistics by file one Database

Using SQL Server to test storage performance.

There are different methods you can use it to test and benchmark your storage subsystem like Microsoft DiskSpd and CrystalDiskMark (for more information about DISKSPD tool check this article SQL Server performance – measure Disk Response Time). We can also use SQL Server directly to test storage subsystem performance.

Flush dirty pages: we might have in the buffer pool

Flush the buffer cache

Flush the buffer cache will flush everything out of your buffer pool which forces you to read the data from your storage subsystem when you run a query later on

Turn on Statistics IO and Statistics time: important for some of the calculations we’ll do later.

Fetching the data Count: Select row counts from any table with an index hint that forces it to do a clustered index scan if there’s clustered index or table scan if there isn’t as an example:

  • • Check the messages results: If we go to the messages tab, we can see the number of logical reads and the physical reads and the readahead reads.


Figure 6 – Fetching the data Count:

Calculate sequential read throughput from IO and time statistics: We will do it using this formula:

8 (KB/page) * (physical reads + read-ahead reads)/(elapsed time in ms)

the result will be 514 MB/sec

Calculate the elapsed time in seconds: We will do it using this formula:

Table size in MB/read rate (MB/s) = Elapsed time in seconds

The result about 35.39 seconds

In the end, the idea here is that you can run this test and then actually see in real life how long it takes to do a sequential read in a whole bunch of data of your IO subsystem and you can see how much sequential throughput you can get out of it when you run this test.

Conclusion

When you face any performance issue, keep in your mind the relation between performance and subsystem storage and try to use the previous tools in this article, not just for solving the performance problem but to proactively head off potential problems in the future as well

See more

To get 3 free licenses to a SQL Server monitoring tool, download ApexSQL Monitor and fill out this simple survey

References

Mustafa EL-Masry

Mustafa EL-Masry

Mustafa EL-Masry is a Senior database consultant and one of the experts in Database performance tuning in the Middle East.

Currently, he is working as a Senior consultant production DBA and Development DBA in many projects in multiple government sectors. He is a Top SQL Server blogger in the Middle East, founder of the community mostafaelmasry.com, and is the second Arabic author on Microsoft MSDN in SQL Server.

Based on his current position, he solved fairly interesting problems on fairly large databases and highly sensitive performance cases.

View all posts by Mustafa EL-Masry
Mustafa EL-Masry
SQL Server performance tuning

About Mustafa EL-Masry

Mustafa EL-Masry is a Senior database consultant and one of the experts in Database performance tuning in the Middle East. Currently, he is working as a Senior consultant production DBA and Development DBA in many projects in multiple government sectors. He is a Top SQL Server blogger in the Middle East, founder of the community mostafaelmasry.com, and is the second Arabic author on Microsoft MSDN in SQL Server. Based on his current position, he solved fairly interesting problems on fairly large databases and highly sensitive performance cases. View all posts by Mustafa EL-Masry

435 Views