SQL Server Storage Monitoring

Use the following query (by Paul Randal)  to break the I/O workload by disk:

SELECT

    --virtual file latency

    ReadLatency =

              CASE WHEN num_of_reads = 0

                     THEN 0 ELSE (io_stall_read_ms / num_of_reads) END,

       WriteLatency =

              CASE WHEN num_of_writes = 0

                     THEN 0 ELSE (io_stall_write_ms / num_of_writes) END,

       Latency =

              CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)

                     THEN 0 ELSE (io_stall / (num_of_reads + num_of_writes)) END,

  --avg bytes per IOP

       AvgBPerRead =

              CASE WHEN num_of_reads = 0

                     THEN 0 ELSE (num_of_bytes_read / num_of_reads) END,

       AvgBPerWrite =

              CASE WHEN io_stall_write_ms = 0

                     THEN 0 ELSE (num_of_bytes_written / num_of_writes) END,

       AvgBPerTransfer =

              CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)

                     THEN 0 ELSE

                           ((num_of_bytes_read + num_of_bytes_written) /

                           (num_of_reads + num_of_writes)) END,

            

       LEFT (mf.physical_name, 2) AS Drive,

       DB_NAME (vfs.database_id) AS DB,

       vfs.*,

       mf.physical_name

FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS vfs

JOIN sys.master_files AS mf

       ON vfs.database_id = mf.database_id

       AND vfs.file_id = mf.file_id

--WHERE vfs.file_id = 2 -- log files

-- ORDER BY Latency DESC

-- ORDER BY ReadLatency DESC

ORDER BY WriteLatency DESC

PerfMon counters:

• Physical Disk Object: Avg. Disk Queue Length represents the average number of physical read and write requests that were queued on the selected physical disk during the sampling period. If your disk queue length frequently exceeds a value of 2 during peak usage of SQL Server, then you might have an I/O bottleneck.
• Avg. Disk Sec/Read is the average time, in seconds, of a read of data from the disk. Any number:
• Less than 10 ms = good performance
• Between 10 ms and 20 ms = slow performance
• Between 20 ms and 50 ms = poor performance
• Greater than 50 ms = significant performance problem.
• Avg. Disk Sec/Write is the average time, in seconds, of a write of data to the disk. Please refer to the guideline in the previous bullet.
• Physical Disk: %Disk Time is the percentage of elapsed time that the selected disk drive was busy servicing read or write requests. A general guideline is that if this value is greater than 50 percent, it represents an I/O bottleneck.
• Avg. Disk Reads/Sec is the rate of read operations on the disk. You need to make sure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond 85 percent capacity.
• Avg. Disk Writes/Sec is the rate of write operations on the disk. Make sure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond 85 percent capacity.

Summary for Performance Tuning Using SQL Server DMVs Book / Chapter 6: Physical Disk Statistics and Utilization

Chapter 6: Physical Disk Statistics and Utilization

• It is vital that, before even installing SLQ Server, you gain a good understanding of the I/O capacity of your disk subsystem, using tools such as SQLIO.
• sys.dm_db_partition_stats helps us find the largest tables and indexes, especially those that are subject to heavy updates, monitor the effectiveness of their partitioning scheme, or investigate the need to upgrade/configure the disk I/O subsystem appropriately. It returns one row per table partition; if the table is not partitioned it will return one row for the table. Note that when querying this view, we filter out system objects which reach to around 100 for each database.
• The query in Listing 6.1 will provide the total number of rows in all clustered indexes and heaps on a given SQL Server instance. System objects such as sys.dm_db_partition_stats are updated asynchronously, for performance reasons, so the counts may not be completely up to date.
• Rebuilding indexes can bloat transaction logs and, when using differential backups, it means that all the pages in the index/tables will have changed since the last full backup … P230.
  https://social.msdn.microsoft.com/Forums/en-US/5146912a-395c-4129-8aeb-4f82b949bdfc/does-reorganizing-indexes-bloat-transaction-logs-and-differential-backups-as-rebuilding-them-the?forum=sqlkjmanageability

• The occurrence of page splits (which happens due to inserting new records in full data pages, which in turn causes fragmentation) can be minimized to some degree, though not avoided altogether, by setting the appropriate fill factor for the clustered table (e.g. 90%) thus allowing space for new data on each page. In fact, a common cause of fragmentation is rebuilding clustered tables and indexes and forgetting to set the fill factor appropriately. By default, the fill factor will be 0 (meaning zero spare space). This can end up causing a lot more subsequent fragmentation than was resolved by rebuilding! … P233.
• Many designers persist in the habit of using GUIDs for surrogate keys, and clustering on them. GUIDs are random in nature, and tend not to be created sequentially and, as a result, insertions of data into the middle of the table are common which causes pages splitting and fragmentation in P233-235 there are examples to illustrate this.
• With heaps, the storage engine inserts all rows at the end of the table, in order of arrival. This makes inserting into a heap super-fast. As such, many people use heaps as a place to drop rows (for instance, when logging operations, and even when loading data using bulk copy), while avoiding the performance impact of index maintenance. On the contrary reading from a fragmented heap, is a performance nightmare.
• Diagnosing I/O Bottlenecks: start with PerfMon counters such as PhysicalDisk Object: Avg. Disk Queue Length and Avg. Disk Reads/Sec, which can help you work out the number of I/Os per disk, per second, and how many physical I/O requests are being queued, on a given disk. And also use sys.dm_os_wait_stats DMV and look if you can find PAGEIOLATCH_EX or PAGEIOLATCH_SH among the top waits, this indicates that many sessions are experiencing delays in obtaining a latch for a buffer, since the buffer is involved in physical I/O requests … P239
• sys.dm_io_virtual_file_stats DMF gives cumulative physical I/O statistics, indicating how frequently the file has been used by the database for reads and writes since the server was last rebooted. It also provides a very useful metric in the form of the "I/O stall" time, which indicates the total amount of time that user processes have waited for I/O to be completed on the file in question. Ultimately, high stall rates could simply indicate that the disk I/O subsystem is inadequate to handle the required I/O throughput Note that this DMF measures physical I/O only. Logical I/O operations that read from cached data will not show up here. Explanation of the columns it returns are in P240-241.
• Investigating physical I/O and I/O stalls: the writers store in a temporary table the result of the query in listing 6.13, which captures the baseline disk I/O statistics from sys.dm_io_virtual_file_stats, then wait for 10 seconds (or depending on the workload on your server), then they run the query in listing 6.15 which compares the current statistics with the baseline. And they are suggesting that, in any event, it is certainly worrying to see that the stall times on a drive are substantially greater than the elapsed time on that same drive … P243-246.
• sys.dm_io_pending_io_requests DMV returns a row for each currently pending I/O request at the file level. More than two or three pending process could indicate an issue. If you regularly observe a high number of pending I/O requests on a single drive, you should consider moving some of the files onto a separate drive, on a different access channel. Using the query in Listing 6.16, we can view the file name, the status, and how long the operation has been waiting.
• In an optimized system the read:write ratio should ideally be close to 50:50. In reality there are almost always more reads than writes. A higher ratio than around 80:20, start to suspect non-optimal queries, or insufficient cache memory to avoid physical disk access. If you find that the read:write ratio is 50:50 based on counts (NUMBER of read operations versus write operations), and 99:1 based on data (SIZE or amount of data read versus written), this indicates that you are reading a lot of data to write a little data, which could be caused by inefficient database code, perhaps allowing users to search in a very inefficient manner, resulting in table scans. Bear in mind that this includes only actual writes to the files, or reads from the file, and will not reflect data read from the cache, or written to the cache and not yet flushed to the disk. Listing 6.17 calculates this based on the AMOUNT (size) of data. Listing 6.18 does the same, but also slice and group on the drive letter. If those queries results something to worry about, the next step is to obtain the read:write ratios for this database in terms of the number of read and write operations. If this method reveals a ratio much closer to 50:50 then we know that reads are reading a disproportionately high amount of data. You can find this using query in listing 6.19. Listing 6.21 gets number of reads and writes at the table level, but note that the returned counts include logical operations also.
• Listing 6.22 demonstrates how to get an overview of tempdb utilization.

Reference: http://www.amazon.com/Performance-Tuning-Server-Dynamic-Management/dp/1906434476

Summary for Accidental DBA Book / Chapter 2: Disk I/O Configuration

Chapter 2: Disk I/O Configuration

Random versus sequential I/O

• SQL Server employs a read-ahead mechanism that can read a number of contiguous pages, up to 128 pages on Standard Edition and 1,024 pages on Enterprise Edition, in a single I/O operation … P45.
• Sequential I/O (is any operation where the blocks can be read from, or written to, disk without having to reposition the disk head on the drive) can benefit from the read-ahead mechanism. Unlike Random I/O where the disk head on the drive has to change positions on the platter, incurring seek latency as a part of the operation, which reduces the performance and number of operations in comparison to sequential I/O … P45.
• Read operations in general, especially in OLTP systems, are random I/O operations … P46.

Choosing the Right RAID Level

• RAID technology is used to achieve the following objectives:
• Increase levels of I/O performance, measured in Input/Output Operations Per Second (IOPS).
• Increase levels of I/O throughput, measured in Megabytes Per Second.
• Increase storage capacity available in a single logical device.
• Gain data redundancy … P46.
• RAID 0: strips the data across multiple drives, allowing the read and write operations to be shared amongst the drives inside the array. This level of RAID provides the best performance for both read and write operations, but provides no redundancy or protection against data loss … P48.
• RAIN 1: provides protection against the loss of data from a single disk by mirroring the writes to a second disk, but doesn't provide added write performance to the system. RAID 1 can be used for storing a single transaction log because of sequential nature of the operations on the transaction logs, yet having multiple transaction log files will have the effect of random I/O, because of the movement of the disk head to perform operations against each of the files being written to sequentially.  … P49.
• RAID 5: is commonly known as "striping with parity;" the data is striped across multiples disks, as per RAID 0, but parity data is stored in order to provide protection from single disk failure. The minimum number of disks required for a RAID 5 array is three. RAID 5 provides redundancy with minimal reduction in storage capacity, Striping the data across multiple disks improves read performance, but the need to maintain parity data incurs a performance penalty for writes. For heavy read but low write databases, RAID 5 can be optimal for the data files. RAID 5 is not recommended for the transaction log files, due heavy write activity … P50, P51.
• RAID 6: is an extension of RAID 5 but, instead of a single distributed parity bit, it uses double-distributed parity bits. RAID 6 has a performance penalty similar to RAID 5 for write operations … P51, P52.
• RAID 10: It provides redundancy by first mirroring each disk, using RAID 1, and then striping those mirrored disks, with RAID 0, to improve performance. Cost is the problem in this configuration … P53.
• NTFS format allocation unit sizes: the 4 K default for NTFS is good for file servers and the operating system drives, but not database data files which perform better using a 64 K allocation unit … P55.
• The two most common tools used for benchmarking storage configurations for SQL Server are SQLIO and IOmeter. Of the two, IOmeter is the most flexible, and can generate mixed I/O workloads that more closely reflect what might be generated by SQL Server. IOmeter also has a graphical user interface that is used for configuring the tests and monitoring their progress. … P56.
• SQLIOSim is a tool by Microsoft to tests the storage using the same disk operations that SQL Server would perform. This tool should be used to validate that the I/O subsystem functions correctly under heavy loads, but it should not be used for performance benchmarking the configuration … P57.

Workload considerations

Data files

• The appropriate disk configuration for the data files of a database depends heavily on the read-to-write ratio for the database. SQL Server tracks the I/O usage of the database files for an instance and makes this information available in the sys.dm_io_virtual_file_stats Dynamic Management Function … P57.
• For a database that is primarily read-only, RAID 5 or RAID 6 can offer good read performance, while also maximizing the available storage. RAID 5 or 6 arrays are commonly used for data warehouses, or for storing data where write latency doesn't impact overall system performance. For OLTP implementations of heavy-write databases, RAID 1+0 provides the best performance … P58.

Log files

• Since the transaction log is written to sequentially, RAID 1 can be used in most situations.
• Having the log files for multiple highly transactional databases on the same physical disks can result in write I/O bottlenecks, often shown by high WRITELOG waits in sys.dm_os_wait_stats, and by high io_write_stall_ms values in sys.dm_io_virtual_file_stats() for the transaction log file … P58.

Special considerations for tempdb

• As a general rule, the tempdb database files should be physically separate from the user data files and transaction log files, on a dedicated disk array. Since tempdb is a writeheavy database, RAID 1 or RAID 1+0 are usually the configurations best able to support the concurrent workload of tempdb … P59.
• Creating multiple files for tempdb is highly recommended as this will reduce contention on pages when allocations are made. Generally create one file per processor, but not more than eight unless there is still contention … P60.

Diagnosing Disk I/O Issues

• A primary tool for investigating disk I/O issues is PerfMon and specifically the Physical Disk\Disk sec/Reads and Physical Disk\Disk sec/Writes counters. The key for performance is having the lowest latency possible and the guideline latency values for each of these counters are as follows:
• Less than 10 ms = good performance
• Between 10 ms and 20 ms = slow performance
• Between 20 ms and 50 ms = poor performance
• Greater than 50 ms = significant performance problem … P65.

Common Disk I/O Problems

• The first step to resolve I/O issues it to make sure the reason is not missing indexes or poorly written queries.
• The following misconfigurations are at the heart of many of the disk I/O issues: sizing for capacity instead of I/O performance, incorrect workload isolation, incorrect partition alignment, and incorrect bandwidth using SAN configurations … P66.
• Use WMI query to investigate possible disk partition misalignment
  wmic partition get BlockSize, StartingOffset, Name, Index
  If the StartingOffset value is not evenly divisible without a remainder, or decimal result, by the stripe unit size being used by the RAID controller, then the disk is misaligned. Fixing the misalignment is easy, yet a destructive operation that erases all the data on the disk. If you are using Windows 2008 or newer, you can format your drive through Disk Management GUI with a 64K allocation unit. If you are using an older OS, you should use DISKPART … P69. 

Reference: http://www.amazon.com/Troubleshooting-SQL-Server-Guide-Accidental/dp/1906434786