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 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 into @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 into @name

　　end

　　close dbname

　　deallocate dbname

　　go

 

五. 和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

 

六. 硬盘压力测试：

　　SQLIO 下载地址：http://www.microsoft.com/en-us/download/details.aspx?id=20163#QuickInfoContainer 

      SQLIO 已经升级成 DiskSPD。 在上面的链接中下载readme.pdf, 该文件中有下载地址。  Diskspd-v2.0.17.zip 解压之后如下图：

 　　

　　UsingDiskspdforSQLServer.docx里面有详细的使用说明和分析方法。

　

---

---

 

转载于:https://www.cnblogs.com/yuzg/p/5466333.html