Sql Server Typical Latency for Read Replica

In this article, nosotros will discuss how to resolve I/O issues that is a very important signal for the SQL Server troubleshooting. The storage subsystem is ane of the significant functioning factors for the databases. Detecting and identifying I/O issues in SQL Server tin be a tough task for the database administrators (DBAs). Generally, the underlying reasons for the I/O bug tin can be:

• Misconfigured or malfunctioning disk subsystems
• Insufficient deejay performances
• Applications that generate redundant I/O activities
• Poor designed or unoptimized queries

Analyzing the symptoms should exist a major principle to clarify the underlying reason that causes the I/O problems on SQL Server. Otherwise, we tin can waste product time dealing with irrelevant bug or discussing the issues with system or storage administrators unnecessarily. Wait types give very useful data for SQL Server troubleshooting. The following wait types can point I/O problems, but these await types do not suffice to decide any problem on the disks.

• PAGEIOLATCH_*
• WRITELOG
• ASYNC_IO_COMPLETION

At first, we will briefly describe these wait types and their relations to the I/O problems.

PAGEIOLATCH_*

SQL Server reserves an surface area on the memory to itself, and this area uses to cache data and alphabetize pages to reduce the disk activities. This reserved memory area is called Buffer Pool. The working mechanism of the buffer pool is very simple; the data loads from the disk to the memory when any request has been received for reading or changing, and they procedure in the buffer pool. The data is written to the disk over again when it is modified. In light of this information, PAGEIOLATCH_* occurs when transferring data from deejay to buffer pool. Information technology is very normal to discover some PAGEIOLATCH_* notwithstanding, it indicates a problem when nosotros run into this wait type frequently and more the other await types. PAGEIOLATCH_* does not indicate disk problems past oneself because this await type can occur for a diversity of reasons. For example:

• Outdated statistics or poorly designed indexes can cause to PAGEIOLATCH_* waits because these types of problems cause redundant deejay activities
• Enabling CDC (Change Data Capture) selection can cause extra I/O workload
• Insufficient retentivity can cause PAGEIOLATCH_* problems because SQL Server does not go along the information pages long plenty in the buffer cache. The other sign of this problem is the Page Life Expectancy metric

WRITELOG

When any modification is performed in the database, SQL Server writes this modification to log buffer, and and so information technology writes this buffer data to disk. Therefore, this wait type is related to the concrete disk that contains the log file (ldf). Placing log files (ldf) on as fast and dedicated disks as possible volition exist the right approach to overcome these problems. At the same time, performance statistics of concrete disks that store ldf files should be considered when this problem occurs. The log data is written into the disk sequentially, and the reading process is besides performed sequentially. Due to this working principle, the disks selected for the log files must perform well for the sequential read and write throughput along with the minimum latency.

ASYNC_IO_COMPLETION

This wait blazon occurs when the SQL Server processes backup and restore operations; however, when this performance takes more than time than usual, it might exist a warning for the I/O problems. The BACKUPIO tin can be seen with the ASYNC_IO_COMPLETION so we can consider well-nigh any disk problem.

I/O Stalls

I/O stall time is an indicator that can be used to detect I/O problems. The dm_io_virtual_file_stats is a dynamic management part that gives detailed information about the stall times of the data and log files so it will simplify the SQL Server troubleshooting process. This dynamic direction part takes two parameters first one is database id, and the second ane is the database file number.

SELECT * FROM sys . dm_io_virtual_file_stats ( { database_id | NULL } , { file_id | Cypher } )

We tin execute this dynamic management function like the below for all databases.

select Db . proper name , vfs . * from sys . dm_io_virtual_file_stats ( NULL , NULL ) AS VFS Bring together sys . databases AS Db ON vfs . database_id = Db . database_id

database_id: This column represents the id number of the database, and nosotros can use sys.databases tabular array to obtain all database id numbers.

file_id: This cavalcade represents the id number of the file, and we can utilize sys.master_files table to obtain all database id numbers.

sample_ms: This column shows the duration of the since server restarted.

num_of_reads: This column shows the number of physical reads that occurred since the server restarted.

num_of_bytes_reads: This cavalcade shows the total amount of concrete reads in bytes that occurred since the server restarted.

io_stall_read_ms: This column shows total latency for the read operations in a millisecond.

num_of_writes: This column shows the number of writes that occurred since the server restarted.

num_of_bytes_written: This column shows the total amount of reads in bytes that occurred since the server restarted.

io_stall_write_ms: This cavalcade shows full latency for the write operations in a millisecond.

io_stall: This column shows the total latency time for the I/O operations in a millisecond.

The high stall times indicate I/O problems and busy disk activities. With the help of the following query, we can find out the read, write, and total latency of the database files and so that we can diagnose whatsoever storage problems.

SELECT DB_NAME ( vfs . database_id ) AS database_name , physical_name Every bit [ Concrete Name ] , size_on_disk_bytes / 1024 / 1024. Equally [ Size of Disk ] , CAST ( io_stall_read_ms / ( i.0 + num_of_reads ) AS NUMERIC ( x , i ) ) AS [ Average Read latency ] , Cast ( io_stall_write_ms / ( 1.0 + num_of_writes ) Equally NUMERIC ( 10 , 1 ) ) AS [ Average Write latency ] , Cast ( ( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads + num_of_writes ) AS NUMERIC ( 10 , 1 ) ) As [ Average Full Latency ] , num_of_bytes_read / NULLIF ( num_of_reads , 0 ) AS [ Average Bytes Per Read ] , num_of_bytes_written / NULLIF ( num_of_writes , 0 ) Equally [ Average Bytes Per Write ] FROM sys . dm_io_virtual_file_stats ( Nix , NULL ) As vfs JOIN sys . master_files AS mf ON vfs . database_id = mf . database_id AND vfs . file_id = mf . file_id ORDER BY [ Boilerplate Total Latency ] DESC

The Average Total Latency column represents the total latency about the database files, and we can use the following tabular array as reference to evaluate the disk performance confronting latency.

Excellent	<one ms
Very good	<5 ms
Good	<5 – 10 ms
Poor	< 10 – 20 ms
Bad	< twenty – 100 ms
Very Bad	<100 ms -500 ms
Awful	> 500 ms

Using Performance Monitor to Analyze I/O Issues

Performance Monitor is also known as Perfmon and this tool helps to runway metrics about reckoner resources or installed applications. Particularly, Perfmon assists in analyzing and troubleshooting SQL Server performance problems because It includes some detail counters for SQL Server beside the full general resource counters. Nosotros tin understand that Perfmon plays a key office in the SQL Server troubleshooting according to this caption. When we focus on the I/O counters of the Perfmon, some of them come to the forefront. First of all, nosotros should keep on eye to latency metrics because these values can tell everything nearly the disk operation.

Latency is a performance metric that measures the time gap between requests and responses for the disks. We can employ the following counters to measure the disk latency.

• Avg. Deejay sec/Transfer counter shows the total latency, and these values should exist under the ten milliseconds
• Avg. Disk sec/Read counter shows the read latency
• Avg. Disk sec/Write counter shows the write latency

When nosotros analyze the prototype higher up, this box performs an awful functioning. The average latency is 0.229 seconds, and so it equals 0.229*1000=229 milliseconds.

IOPS (Input/Output operations per second) is a functioning metric for the disks that measures the total input and output operations performed by the disk in one second.

• Disk Reads/sec counter indicates write IOPS
• Deejay Writes/sec counter indicates read IOPS
• Disk Transfers/sec counter indicates the full number of the IOPS this value equals to summing Disk Reads/sec and Deejay Writes/sec counters

The throughput metric indicates how much MB can exist read or written by the disk subsystems per second. The throughput value volition be changed co-ordinate to our disk infrastructure, types, and vendors. For this reason, the exact value could non be given for this counter.

• Disk Bytes/sec counter shows the full throughput of the deejay per 2d
• Deejay Read Bytes/sec counter shows the read throughput
• Deejay Write Bytes/sec counter shows the read throughput

Conclusion

In this commodity, nosotros learned the basic methods that help to diagnose and troubleshoot SQL Server I/O bug. To overcome this type of problem, we need to observe all metrics that tin can be helped to figure out the chief trouble. Understanding the main problem is a very meaning point for the SQL Server troubleshooting.

• Writer
• Recent Posts

Esat Erkec is a SQL Server professional who began his career 8+ years agone as a Software Programmer. He is a SQL Server Microsoft Certified Solutions Skilful.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. Yous can find him on LinkedIn.

View all posts by Esat Erkec

fenwicktheized.blogspot.com

Source: https://www.sqlshack.com/sql-server-troubleshooting-disk-i-o-problems/

Share this post