Performance MONITOR4: Monitoring the IO performance of SQL Server

The IO performance of SQL Server is affected by the IO latency of the physical disk and the IO operations performed internally by SQL Server. When monitoring disk performance, the primary measure (metric) is IO latency, which is the time delay in creating an IO request from application to disk to complete the IO request. SQL Server is prone to performance problems if the physical disk does not complete the IO request in a timely manner, keeping up with the speed of the request payload. SQL Server internally performs some specific operations and reads and writes to disk, which also affects the performance of the physical hard disk in response to SQL Server IO requests, leaving the query process Pageiolatch or writelog waiting.

One, monitoring the IO performance of physical disk at the system level

1, monitoring the IO delay of physical disk

The IO latency of physical disk is analyzed at the Windows level, relying primarily on the counters of the performance Monitor, with three counters that measure the IO latency of physical Disk:

• avg. disk Sec/transfer: The average time spent on disk per read and write operations
• avg. disk Sec/read: The average time spent on disk per read operation
• avg. disk Sec/write: The average time spent on disk per write operation

avg. Disk sec/(Transfer,read,write), a good reflection of disk's IO speed, the recommended baseline for measuring the IO speed of disk (baseline):

• Soon: <10ms
• General: 10-20ms
• A little slow: 20-50ms
• Very slow: >50ms

2. Analyze the counter values collected by Data collector

is a counter-numeric chart for a server in the production environment, which enlarges the measure of IO latency by 1000 times times, so that the units shown in the chart are Ms.

• %Idle time: Floating around 60%, indicating disk is not very busy
• Avg.Disk Sec/write: Most cases are below 10ms, occasional fluctuations, indicating Disk write latency is relatively low
• Avg.Disk Sec/read: Read delay In most cases is above 40ms, rarely less than 40ms, occasionally peaking, indicating Disk read delay is very high
• Avg.Disk Sec/transfer: The average value of read and write delay is around 30ms, when there is fluctuation, when the%idle time curve does not fluctuate, disk read and write delay fluctuations, indicating that disk read-write delay is not stable

Initial judgment, disk read-write latency is very high, disk IO performance is poor, Io slow

3, monitoring the number of physical disk IO

According to disk's IO number to define disk performance, there is no unified threshold, generally through the monitoring of the value to obtain a trend, set a baseline, if the disk is busy, encounter abnormal valley value, then you need to see if there are parameter sniffing problems and disk IO-intensive queries, Abnormal valley values are generally caused by the amount of data requested by the query statement, which requires performance tuning of the query statement.

Disk performance counters that are frequently used at the system level are PhysicalDisk counters:

• Avg. disk Queue Length: Provides a primary measure of how much disk is blocked, indicating the average length of the IO request queue that disk waits to process during sample interval, that is, the number of IO requests waiting to be processed by disk
• % Idle Time:disk to be able to counter-eject disk's busy level
• Disk (reads/writes/transfers)/sec: Number of Read and write operations per second disk

The queue length fluctuates greatly, and when the%idle time is raised, the number of IO decreases and no significant abnormal valley value is found.

4, monitor the amount of data that physical disk reads and writes

These count values, to monitor the physical disk read and write performance, the significance is small, only as a reference.

• Avg.Disk bytes/(read,write,transfer) indicates the number of bytes of physical disk read from disk to memory, the number of bytes written from memory to disk, and the total number of bytes in the physical disk when read and write operations are performed.
• Disk Bytes/sec: When the physical disk reads and writes, the data is transferred from disk to memory, or from memory to disk byte speed, good disk, its value between 20-40MB, general disk, its value is below 20MB

Second, the impact of SQL Server internal operations on disk IO performance

SQL Server is able to cache data pages loaded from disk, and normally, most operations do not require any physical read operations and do not require disk physical IO participation, but there are some operations that must be done with the physical disk for IO operations. The operations of SQL Server and physical disk for IO interaction:

• For data that is not cached in memory, the first time it is accessed, the data needs to be read from the data file into memory, any data accessed by SQL Server must be cached in memory, and if it is not in memory, SQL Server sends a read request to read the data page from the physical disk into memory. This process is called physical reading, and if the data exists in memory, SQL Server accesses it directly, a process called logical reading.
• Before any modification operations are committed, the pre-write transaction log is logged to the log file, and the data is written to the data file when the checkpoint and LazyWriter run.
• When the checkpoint is executed, the dirty pages in the cache are written to the data file, and the dirty pages refer to the data pages that have been modified after loading the memory, the data in memory is inconsistent with the data in the file, and the physical writes and the internal pressure triggered by the checkpoint are not related to the amount of data modified by the user. The time interval used to control the restore.
• When the buffer pool is out of space and the free buffer list is reduced to a critical value, the lazywriter process actively empties some of the data pages and execution plans that have not been accessed in memory for a long time, and if the data page is dirty, write it to the data file. LazyWriter is related to memory pressure because the free buffer available in memory causes the lazywriter process to perform cleanup operations.
• IO-intensive operations, such as checking database consistency (DBCC CheckDB), rebuilding indexes, updating statistics, database backups, and so on, can bring a lot of disk IO operations

SQL Server reads only the data file, as long as the data is cached in memory, ideally, SQL Server does not perform any physical read operations and does not need to load data from the physical disk into memory, and SQL Server has a direct relationship between the performance of the read operation and the cache capacity of the memory. is also related to the amount of data read by the user.

SQL Server writes are divided into write data files and write log files. The amount of data written to the log file is determined entirely by the amount of data modification, which is not related to the memory pressure; The number of written data files, mainly related to the amount of modification. LazyWriter is related to memory pressure, and once the memory is under pressure, LazyWriter automatically starts, cleans up the cache that is not accessed for the longest time, frees up memory, and increases the amount of free buffer available.

Therefore, SQL Server requests the number of physical disk read operations directly related to memory, the more memory, the more data cached, the number of physical disk read operations will be less, the number of logical read will not be reduced; SQL The number of physical disk write operations requested by the server is directly related to the amount of data modifications performed by the user, and there is little pressure on the memory. When performing a read and write request for a physical disk, the SQL Server query process generates a Pageiolatch wait, indicating that the process is performing a physical read operation, which can be seen from DMV:sys.dm_exec_requests :

Selectr.session_id, r.blocking_session_id asBlocking, R.wait_type asCurrent_wait_type, R.wait_resource, R.last_wait_type, R.wait_time, R.status, r.co Mmand, R.cpu_time,r.reads,r.writes,r.logical_reads, R.total_elapsed_time, R.start_time,db_name(r.database_id) asdatabase_name,SUBSTRING(St.text, R.statement_start_offset/2+1,                    (     Case  whenR.statement_end_offset= -1                                  Then LEN(CONVERT(NVARCHAR(MAX), St.text))                            ELSE(R.statement_end_offset-R.statement_start_offset)/2                        END                     )                )  asindividualquerystatement fromsys.dm_exec_requests RouterAPPLY sys.dm_exec_sql_text (R.sql_handle) asStwhere(R.wait_type<>'Miscellaneous' orR.wait_type is NULL)     andr.session_id> -     andr.session_id<>@ @spid

View Code

pageiolatch Wait: Indicates that the process is loading data from the physical disk into memory, that is, the process is doing a physical read operation, the number of physical reads from the reads field can be seen

writelog Wait: Indicates that the transaction is modifying data, and SQL Server will pre-write transaction log records to the transaction log file

Reference Documentation:

Memory-lazy Writer and Checkpoint

SQL Server Disk Performance Metrics–part 1–the Most important disk performance metrics

Measuring Disk Latency with Windows performance Monitor (Perfmon)

Performance MONITOR4: Monitoring the IO performance of SQL Server