Diagnostic points for SQL Server I/O issues

I. When does SQL Server deal with disks:

1. The data that SQL needs to access is not in the buffer pool, the first time it is accessed, the page from which the data resides is read from the data file into memory. (Read only)

2. Before Insert/update/delete commits, the logging buffer needs to be written to the log file of the disk. Write

3. When checkpoint, you need to synchronize the dirty data pages that have been modified in the buffer pool to the data files on the disk. Write

4. When the buffer pool is out of space, lazy writer is triggered to empty some of the memory's long unused data pages and execution plans. If the modifications on these pages have not been written back to the hard drive by the checkpoint, Lazy writer writes them back. Write

5. DBCC CheckDB, Reindex, Update Statistics, database backup and other operations, will bring a relatively large hard disk read and write. (Read/write)

Two. Which SQL configurations have an impact on I/O:

1. (Recovery Interval, default 60 sec) (Checkpoint pages/sec + Avg. Disk Queue Length + Batch requests/sec)

2. Automatic growth and automatic contraction of data files and log files. For the build database, avoid auto-growth and auto-shrinking.

3. Degree of page fragmentation in the data file (Clustered index): DBCC SHOWCONTIG (' table_name ')-Avg. page Density (full) fragment many, read/write more pages (set statistic S IO on--logical reads)

4. Index structure on the table: the storage management of the clustered Index table and heap table is different.

5. Data compression: Can reduce I/O, but consumes CPU and memory resources.

6. The data files and log files are placed on different hard disks, and the logs should be placed on the hard disk with faster writing, such as RAID10

7. The data file can have multiple files placed on different hard disks, and SQL Server writes the new data proportionally to all the files with the remaining space, according to the size of the remaining space for each file in the same filegroup. The log files are different, and only one log file is written at a point in time. Therefore, it is not helpful to build log files on different hard disks.

Three. Diagnosis of operating system I/O problems:

1. Before judging the SQL I/O problem, look at the Windows level I/O is OK. If you are busy, confirm that SQL is not the cause.

2. LogicalDisk and PhysicalDisk:

%idle Time:

%disk Time: =%disk Read Time +%disk write time

%disk Read Time

%disk Write Time

AVG. Disk Sec/read

AVG. Disk Sec/write: Very good: <10ms General: 10-20ms A little slow: 20-50ms very slow:> 50ms

AVG. Disk Bytes/transfer

AVG. Disk Queue Length: Should not be >2 for a long time (SAN disk is different)

AVG. Disk Read Queue Length

AVG. Disk Write Queue Length

Disk bytes/sec: Good: 20-40MB General: 10-20MB

Disk Read bytes/sec

Disk Write bytes/sec

Disk transfers/sec

Disk reads/sec

Disk writes/sec

Current Disk Queue Length

Four. SQL Server Internal analysis:

1. Check sys.dm_exec_requests or sys.dm_os_wait_stats:

Select Wait_type,

Waiting_tasks_count,

Wait_time_ms

From Sys.dm_os_wait_stats

where wait_type like ' pageiolatch '--pageiolatch_ex (write) pageiolatch_sh (read) mainly reflects I/O waits on the data file

ORDER BY Wait_type

2. Find out which file in that database always does I/O, whether it is a data file or a log file, read frequently, or often write:

Select Db.name as database_name, F.fileid as file_id, f.filename as file_name,

I.num_of_reads, I.num_of _bytes_read, I.io_stall_read_ms,

I.num_of_writes, I.num_of_bytes_written, I.io_stall_write_ms,

I.io_stall, I.size_on_disk_bytes

From Sys.database db inner join sys.sysaltfiles F on db.database_id=f.dbid

INNER JOIN Sys.dm_io_virtual_file_stats (null,null) I on I.database_id=f.dbid and I.file_id=f.fileid

Select database_id, file_id, Io_stall, Io_pending_ms_ticks, scheduler_address-Check every pending I/O request

From Sys.dm_io_virtual_file_stats (null,null) t1, sys.dm_io_pending_io_requests as T2

where T1.file_handle=t2.io_handle

--check which table in buffer pool and how mang size of it

declare @name nvarchar (100)

declare @cmd nvarchar (1000)

DECLARE dbname cursor FOR

Select name from master.dbo.sysdatabases

Open dbname

FETCH NEXT from dbname to @name

While @ @fetch_status = 0

Begin

Set @cmd = ' Select b.databse_id, Db=dbname (b.database_id), p.object_id, p.index_id, Buffer_count=count (*) from ' + @name + '. Sys.allocation_units A, '

+ @name + '. Sys.dm_os_buffer_descriptions b, ' + @name + '. Sys.partitions p

where a.allocation_unit_id=b.allocation_unit_id

and a.container_id=p.hobt_id

and b.database_id=db_id ("' + @name + ')

Group by b.database_id, p.object_id, p.index_id

Order by b.database_id, Buffer_count Desc '

EXEC (@cmd)

FETCH NEXT from dbname to @name

End

Close dbname

Deallocate dbname

Go

Five. SQL-related counters:

1. Buffer Manager:

Page reads/sec and Page writes/sec

Lazy writes/sec

Checkpoint writes/sec

Readahead pages/sec

2. Access Methods:

Freespace scans/sec

Page splits/sec

Page allocations/sec

Workfiles/sec

Worktables/sec

Full scans/sec

Index searches/sec

3. Database (Log Activity)

Log flushes/sec

Log Bytes flushed/sec

Log Flush Wait Time

Log Flush Waits/sec

Six. Hard Drive pressure test:

SQLIO:

　

Diagnostic points for SQL Server I/O issues