SQL Server disk I/O performance analysis

Types of I/O operations in SQL Server:

1. For data that is not cached in memory, the first time you access it, you need to read the data from the data file into memory from the page where you are

2. Before any insert/update/delete is committed, SQL Server needs to ensure that logging can be written to the log file

3. When SQL Server does checkpoint, it is necessary to synchronize the data pages in the memory buffer that have already been modified into the data file of the hard disk, usually checkpoint once a minute. If you modify more, the frequency is higher, and the number of writes is directly related to the amount of data modification that occurred last checkpoint dependency

4. When the SQL Server Buffer pool is under pressure, lazy Writer is fired, actively emptying some long unused data pages and execution plans in memory. If the modifications that occur on these pages have not been written back to the hard disk by checkpoint, Lazy writer writes them back

5. Some special operations, such as DBCC CHECKDB, Reindex, Updata Statistics, backup, etc., will bring large hard disk read and write. These actions should be given to occur in some fixed time periods

database-level I/O Impact: 1.Recovery Interval (sp_configure) controls how often SQL Server is checkpoint

checkpoint pages/sec (Buffer Manager) rises periodically

Mssql:sql Statistics-batch requests/sec: Number of batch batches completed per second

Mssql:databases-active Open in Transactions:sql server, number of things not yet committed

2. Automatic growth and contraction of data/log files

3. Fragmentation in data files: the more pages are fragmented, SQL Server needs to read and write more pages, increasing the volume of hard disk reads and writes

4. Index structure on a table

5. The data files and log files are placed on separate disks. If possible, log files are placed on a disk that is faster to write

6. Whether a data filegroup has multiple files and is placed on a different disk. However, for log files, SQL Server writes only one log file at a point in time, so creating multiple log files on different disks has no performance improvement

System-Level I/O impact: %disk Time: Only observe its curve trend, the value itself has no reference value

%idleTime: The disk is in the idle state percentage. When the disk is in an idle state, the value is 100%. When the disk is full-load operation, the value is 0, so you can reverse%disk time based on this value

Disk bytes/sec: The total number of reads and writes per second (disk throughput). It is necessary to confirm that the maximum read and write speed of the disk is a reference value, and you can see if the disk read/write limit has been reached.

avg.disk sec/transfer: Average time spent on disk per read or write action

Avg.Disk Queue Length: The number of requests that are issued for disk operations that are waiting for disk processing. Theoretically, this value should not be greater than 2 for a long time.

Current disk Queue Length: Number of requests currently waiting to be processed by disk

When the Avg.Disk Queue length is high, you need to observe the Disk bytes/sec and Avg.Disk Sec/transfer which counter has reached its maximum value, respectively.

performance counters in SQL Server

Buffer Manager:

Page reads/sec and page writes/sec: How many pages are read and written per second. Learn how many disk reads and writes are caused by the behavior of the buffer pool

Lazy writes/sec:lazy Writer How many page writes per second to empty the buffer pool

Checkpoint writes/sec: Number of dirty page writes per second from buffer pool to disk

Freespace scans/sec: Find the space that can be used in the heap structure. For tables that do not have a clustered index, SQL Server is stored as a heap. If the value is high, you should build more clustered indexes

Full scans/sec: Every second SQL Server does a total table scan number, the smaller the better

Databases (Log Activity):

Log Flush Wait time: The action that is written to the log, which has been encountered because the disk is too late to respond, can cause the front-end transaction to fail to commit, which can severely affect SQL Server performance. The value should be 0 for most of the time

Log Flush Waits/sec: In Transactions committed per second, how many transactions have been waiting for the log write to complete. Ideally, log writes should be completed immediately and no wait is required.

SQL Server disk I/O performance analysis