Storage performance is key to the health and performance of a SQL Server database. When storage performance begins to slow down, queries suffer from more waits on disk. These waits can show up as a variety of SQL Server wait types, including WRITELOG and wait types starting with PAGEIOLATCH.
Wait Types
Microsoft® SQL Server® has hundreds of wait types available to describe just about any specific cause for waiting. Several of these wait types are of interest for diagnosing storage-specific performance issues.
The WRITELOG Wait Type
When SQL Server is writing changes to a SQL Server database, all these changes must be written to the transaction log before being written to the data file pages in memory. If writes to the SQL Server database are slow, users will experience poor performance when they take actions within the SQL Server database, possibly to the extent they’ll see their updates running slowly.
PAGEIOLATCH Wait Types
Wait types starting with PAGEIOLATCH indicate SQL Server couldn’t get to the data needed by the query on the server’s storage subsystem. Though a variety of different wait types can be presented, they all have the same underlying cause: the drive servicing the request is unable to handle the load placed on it.
Identifying a Problem
Identifying a hard drive-related Microsoft SQL Server performance problem can feel a little bit like a game of cat and mouse, but emerging patterns can help you identify wait stats problems. A sample query is shown in Example 1.
Example 1: Sample Select
Example 1: Query to help identify storage performance issues
The query results reveal the top wait types on the server. This result set shows the waits that have increased over the last 30 seconds. When these top waits are storage-based, SQL Server is putting too much workload on the disk.
In turn, the overload is taking place either because performance tuning needs to be performed on the database or because the storage subsystem presenting the storage is unable to handle the system’s workload.
When reviewing storage performance problems, basic performance tuning is critical. But if high storage waits continue after normal SQL Server performance tuning, you may need to increase the throughput from your storage subsystem.
In a cloud-based database platform, you can increase the number of IOPs on the disk by adding additional disks (if the database is hosted within Microsoft Azure) or by increasing the capacity of the disks (if the database is hosted within Amazon AWS).
In an on-premises storage system, additional performance capacity may need to be added to the disks behind the databases. In extreme situations, you may need to replace an entire storage array, either with a faster model from the same storage vendor or one from another storage vendor. Before expending both the capital and the staff time for a SAN upgrade, explore all other avenues to fix the performance problem.
Keep Monitoring
Properly monitoring SQL Server should allow you to spot an increase in the I/O performance usage of the server. A database monitor is a critical tool for ensuring the health of your data and the efficiency of your operations.
As you research monitoring tools, take a look at SolarWinds. Their products provide the necessary visibility into your data, keeping your pipeline humming. To get started with a free trial, click here and see how it can improve your database performance.
Denny Cherry is the owner and principal consultant for Denny Cherry & Associates Consulting and has over a decade of experience working with platforms such as Microsoft SQL Server, Hyper-V, vSphere, and Enterprise Storage solutions. Denny’s areas of technical expertise include system architecture, performance tuning, security, replication, and troubleshooting. He currently holds several Microsoft Certifications related to SQL Server for versions 2000 through 2017, including the Microsoft Certified Master as well as being a Microsoft MVP for several years. Denny has written several books and dozens of technical articles on SQL Server management and how SQL Server integrates with various other technologies.
