SQL Server I/O 问题的诊断分析

 

一. SQL Server 什么时候和磁盘打交道:

1. SQL 需要访问的数据没有在Buffer pool中,第一次访问时需要将数据所在的页面从数据文件中读取到内存中。(只读)

2. 在insert/update/delete提交之前, 需要将日志记录缓存区写入到磁盘的日志文件中。(写)

3. Checkpoint的时候,需要将Buffer pool中已经发生修改的脏数据页面同步到磁盘的数据文件中。(写)

4. 当Buffer pool空间不足的时候, 会触发Lazy writer, 主动将内存中的一些很久没有使用过的数据页面和执行计划清空。如果这些页面上的修改还没有被检查点写回硬盘, Lazy writer 会将其写回。(写)

5. DBCC checkDB, Reindex, Update Statistics, database backup等操作, 会带来比较大的硬盘读写。(读/写)

 

二. 哪些SQL 配置会对I/O有影响:

1. ( Recovery Interval, 默认60秒)(Checkpoint pages/sec + Avg. Disk Queue Length + Batch Requests/sec) 

2. 数据文件和日志文件的自动增长和自动收缩。对于生成数据库,要避免自动增长和自动收缩。

3. 数据文件中的页面碎片程度 (Clustered index) :  dbcc showcontig('table_name') -- avg. Page Density(full)  碎片多,读取/写入的页面多(set statistics io on -- logical reads)

4. 表上的索引结构: 聚集索引的表和堆表的存储管理不同。

5. 数据压缩: 可以减少I/O, 但会消耗CPU和内存资源。

6. 数据文件和日志文件分别放在不同的硬盘上,日志要放在写入速度较快的硬盘上, 如RAID10

7. 数据文件可以有多个分别放到不同硬盘上的文件, SQL server会将新数据按照同一个文件组的每个文件剩余空间的大小, 按比例写入到所有有剩余空间的文件中。  而日志文件则不同, 在一个时间点只会写一个日志文件。 所以在不同的硬盘上建日志文件对性能没有什么帮助。

 

三. 操作系统I/O问题的诊断:

1. 在判断SQL I/O问题之前,先看看Windows层面I/O是否正常。 如果很忙,再确认是不是SQL造成的。

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:   很好:<10ms    一般:10-20ms   有点慢:20-50ms   非常慢:> 50ms

  Avg. disk bytes/transfer

  Avg. disk queue length: 不应该长时间>2  (SAN 盘就不同)

  Avg. disk read queue length

  Avg. disk write queue length

  Disk Bytes/sec:  好:20-40MB   一般:10-20MB

  Disk Read Bytes/sec

  Disk Write Bytes/sec

  Disk Transfers/sec

  Disk Reads/sec

  Disk Writes/sec

  Current Disk queue length

 

四. SQL Server 内部分析:

 1. 检查sys.dm_exec_requests 或者 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(写)   PAGEIOLATCH_SH(读) 主要反映数据文件上的I/O等待

  order by wait_type

2. 找出那个数据库哪个文件总做I/O,是数据文件还是日志文件, 经常读,还是经常写:

select db.name as dbname,
       f.fileid as [fileid],
       f.filename as [filename],
       convert(numeric(5,2),i.io_stall_read_ms/(num_of_reads+1.0))   AS 'Avg Read ms/Transfer',
       convert(numeric(5,2),i.io_stall_write_ms/(num_of_writes+1.0)) AS 'Avg Write ms/Transfer',
       i.num_of_reads,
       i.num_of_bytes_read/1024/1024 as num_of_mb_read,
       i.io_stall_read_ms,
       i.num_of_writes,
       i.num_of_bytes_written/1024/1024 as num_of_mb_write,
       i.io_stall_write_ms,
       i.io_stall,
       i.size_on_disk_bytes
  from sys.databases 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
--order by i.num_of_reads desc
--order by i.num_of_writes desc

 check every pending I/O request

select database_id,
       file_id,
       io_stall,
       io_pending_ms_ticks,
       scheduler_address  
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

  

五. 和SQL相关的计数器:

  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

 

六. 硬盘压力测试:

  可以使用 DiskSPD

posted on 2020-12-08 18:45  湖东  阅读(217)  评论(0编辑  收藏  举报