Various sources of I/O issues

To achieve a reasonably good performance from SQL Server implementations, careful planning of the I/O subsystem and applying all good practices is crucial. At the beginning of a database application life cycle, I/O issues are undetectable as the application runs against a smaller database. As databases grow, database server performance issue starts being noticed. Tackling these issues later down the road is definitely unproductive and a cumbersome process.

Latency Measurement:

If an application is designed optimally and the I/O subsystem is configured correctly, then Microsoft has a very good recommendation about the I/O latency. These recommendations are well accepted by all the industries experts and can be evaluated against OLTP or DSS implementation (Microsoft TechNet source). Please note that the acceptance level of I/O Latency would slightly vary based on some factors such as random, sequential, and I/O size (8K, 64K, 128K, etc.).

PerfMon Counter	Threshold Value	Data or Log	Type of workload
Average Disk/sec Read & Average Disk/sec Write	1ms - 5ms	Log
	4ms - 20ms	Data	OLTP
	30ms or less	Data	DSS

I/O measurement and using PerfMon:

There are several performance counters and different technique existing that can be used to measure I/O performance, latency and IOPS. Following are some widely used Windows PerfMon counters that are trustworthy.

Measuring of disk latency:

·         Average Disk sec/Read

·         Average Disk sec/Write

·         Average Disk sec/Transfer

Measuring disk throughputs:

·         Disk Read Bytes/sec

·         Disk Write Bytes/sec

·         Disk Bytes/sec

Measuring IOPS:

·         Disk Reads/sec

·         Disk Writes/sec

·         Disk Transfers/sec

Measuring a I/O requests if it splits into multiple requests:

·         Split IO/Sec

When is a disk overwhelmed? Usually when the disk throughput increases, latency also increases more or less. However, when the disk throughput remains almost the same but the latency increases as time passes, it results in disk saturation or I/O bottleneck.

Source of I/O Issues:

There are numerous reasons why a disk experiences bottleneck on a SQL Server. Following are some handful factors:

·         Inadequate memory for the buffer pool.

·         Index fragmentation.

·         Outdated statistics.

·         Improper or non-optimal fill factor.

·         Not using data compression (enterprise edition only).

·         No isolation of Index, Data and Log files.

·         Structure of database schema such as indexes, row width, data types.

·         Not using T-SQL performance based Set-Base technique.

·         Using nested views.

·         Excessive sort operation such as ORDER BY and GROUP BY.

·         Using Implicit Transaction.

·         Using lengthy Transaction.

·         Excessive using of NOLOCK hints.

·         Using CURSOR method.

·         Lack of covering indexes.

·         Using wider key for clustered index.

·         Workload nature - READ oriented vs. WRITE oriented.

·         Non optimal RAID configuration.

·         Volume alignment (also known as sector alignment).

·         NTFS Block size (also known as cluster size or Allocation Unit Size).

·         Suboptimal drivers or firmware used on the host HBAs or storage array.

·         Improper queue depth settings on HBAs.

·         Incorrectly configured multipath software and/or fiber switches.

Using and detecting I/O Issues using my tool:

In my tool, “I/O Response (ms)” represents the overall “I/O Latency” on a SQL Server Instance. The calculation method includes all the drives where data files are placed. The T-SQL code which has been used to calculate the “I/O Response (ms)” in my tool has been extracted from the SSMS “Activity Monitor”. There may be a fraction of a millisecond calculation variation but it will provide you the most critical current I/O and workload state.

Figure#1: Overall I/O Response (ms)

Under the “Database” tab, there is a “Database I/O” tab which calculates I/O operations which has been derived from “sys.dm_io_virtual_file_stats”. This will provide you with a far more granular and drill-down information about I/O which are occurring on various data and log files. To use this feature, multiple clicks on the lightning bolt button are required to activate and to view the current I/O performance data.

Figure#2: I/O Operation on Data and Log files

Read Gurus Articles:

Are I/O latencies killing your performance?

http://www.sqlskills.com/blogs/paul/are-io-latencies-killing-your-performance/

SQL Server Best Practices Article

https://technet.microsoft.com/library/Cc966412