Choosing the Right Storage for SQL Server
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.
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.
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.
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 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
select * into #wait_stats from sys.dm_db_wait_stats waitfor delay '00:00:30' SELECT waits.*, waits.wait_time_ms-temp.wait_time_ms as 'ms_difference' FROM sys.dm_db_wait_stats as waits join #wait_stats as temp on waits.wait_type = temp.wait_type and temp.wait_time_ms <> waits.wait_time_ms order by waits.wait_time_ms-temp.wait_time_ms desc
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.
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.