Analyzing I/O performances in SQL Server
One of the most common reason behind
performance degradation is deficient amount of storage subsystem performance.
There are lot of reasons for low performance in storage, but calculating it and
knowing what needs to be controlled and handled is always an essential task.
Therefore, there are three major measures, which are quite necessary for
improving performance of I/O subsystem. In the following section, we are going
to discuss these three metrics and proceed further with analyzing I/O
performance for SQL server.
Description of Three Metrics
Latency
●
It is the first
metric, which is the time required by I/O to complete and is considered as service or response time.
●
The working of this measurement starts working when the OS gets
initialized, sends request to the disk controller, and stops when drive
finishes the request processing.
●
The Reads are
finished when OS receives data and writes are accomplished when drive informs
OS, which has received the data.
●
Depending upon the caching policy and hardware support, the data might
still be present in DRAM cache
for writes. Thus, write-back caching is more faster than that of write-through
caching. However, this will require a backup of battery for a disk controller.
Input/Output Operations per Second (IOPS)
●
Understand the
concept of this second metric with help of following example:
“Suppose
there is a constant latency of 1 ms. It means that a drive will be able to
process one thousand IOs in a second with a 1 queue depth”
This denotes that as many IOs are
appended in the queue, correspondingly the latency also gets increased.
●
One of the major
advantage of flash storage is that it will be able to read/write multiple NAND
channels in parallel format, which will be in absence of electro-mechanical
rotating parts for slowing down disk access.
●
If a person is knowing queue depth and size of transferring then,
he/she can transfer IOPS to MB/sec and then MB/sec to latency.
●
IOPS is equal to depth divided by the latency and
IOPS itself is not considered as size of transferring for a single disk.
Sequential Throughput
●
It is the rate
with which one can transfer data in the server. Its unit is MB/sec or GB/sec
and if the value is in MB/sec then, it is equal to IOPS times of transfer size.
●
In an enterprise
storage, sequential disk often gets short-changed both by storage vendors and
administrators.
●
Sequential
throughput is vital for many database activities related to the server. This
activity can involve backups and restores, creation and rebuilds of indexes,
etc.
Workload Metrics of the SQL Server I/O
Talking about performance of the SQL
server and I/O, there are multiple facts that users should monitor over time.
One should be aware about read vs write ratio for their workload of all files
of a user database and tempdb. Remember that, this ratio is not unique for all
types of the SQL server and workloads.
The server user should view typical rate
of I/O for sequential throughput and IOPS. One can determine IOPS by read/sec
and write/sec of the Windows Performance Monitor (PerfMon) and on the other
hand, one can view sequential throughput by disk read bytes/sec and disk write
bytes/sec. One should operate PerfMon to calculate average disk sec/read and
average sec/write, which are read and write latency at the level of disk.
Conclusion
Analyzing I/O performance for SQL server
is a best practice to maintain the performance speed of the server. Each and
every server user must be aware about the metrics used for measuring the
performance of the application. This will also help in monitoring the server
performance and hence, reduce any interruption while working.
