sql server I/O硬盘交互

一. 概述

 sql server作为关系型数据库,须求展开数据存储,
那在运作中就会不断的与硬盘进行读写交互。若是读写不能科学快捷的成就,就会现出性能问题以及数据库损坏问题。上边讲讲引起I/O的暴发,以及分析优化。

二.sql server  首要磁盘读写的作为

  2.1 
从数据文件(.mdf)里, 读入新数据页到内存。前页讲述内存时我们领会,若是想要的多寡不在内存中时,就会从硬盘的数据文件里以页面为最小单位,读取到内存中,还包含预读的数量。
当内存中留存,就不会去磁盘读取数据。丰裕的内存能够最小化磁盘I/O,因为磁盘的进度远慢于内存。

  2.2  预写日志系统(WAL),向日志文件(.ldf)写入增删改的日记记录。
用来保安数据业务的ACID。

  2.3  Checkpoint 检查点暴发时,将脏页数据写入到数据文件
,在sp_configure的recovery interval 控制着sql
server多久举办四次Checkpoint,
尽管常常做Checkpoint,这每便爆发的硬盘写就不会太多,对硬盘冲击不会太大。假诺隔长日子两次Checkpoint,不做Checkpoint时性能可能会比较快,但累积了汪洋的修改,可能要发出多量的写,那时性能会受影响。在半数以上据气象下,默许设置是相比较好的,没必要去修改。

  2.4   内存不足时,Lazy
Write发生,会将缓冲区中修改过的多寡页面同步到硬盘的数据文件中。由于内存的空中欠缺触发了Lazy
Write, 主动将内存中很久没有应用过的数据页和推行安插清空。Lazy
Write一般不被常常调用。

  2.5   CheckDB, 
索引维护,全文索引,总结音信,备份数据,高可用一块日志等。

三. 磁盘读写的有关分析

  3.1 sys.dm_io_virtual_file_stats  获取数据文件和日志文件的I/O
总结音讯。该函数从sql server
2008始发,替换动态管理视图fn_virtualfilestats函数。
哪些文件日常要做读num_of_reads,哪些经常要做写num_of_writes,哪些读写日常要等待io_stall_*。为了赢得有意义的多少,必要在长期内对这个数量进行快照,然后将它们同基线数据相比较。

SELECT  DB_NAME(database_id) AS 'Database Name',
        file_id,
        io_stall_read_ms / num_of_reads AS 'Avg Read Transfer/ms',
        io_stall_write_ms / num_of_writes AS 'Avg Write Transfer/ms'
FROM    sys.dm_io_virtual_file_stats(null, null)
WHERE   num_of_reads > 0 AND num_of_writes > 0 

  io_stall_read_ms:用户等待文件,发出读取所用的总时间(阿秒)。

  io_stall_write: 用户等待在该公文中做到写入所用的总时间微秒。

  图片 1

  3.2  windows 性能计数器:  Avg. Disk Sec/Read
那么些计数器是指每秒从磁盘读取数据的平均值

< 10 ms – 非常好
 10 ~ 20 ms 之间- 还可以
 20 ~50 ms 之间- 慢,要求关切
> 50 ms –严重的 I/O 瓶颈

  3.4  I/O  物理内存读取次数最多的前50条

 SELECT TOP 50
 qs.total_physical_reads,qs.execution_count,
 qs.total_physical_reads/qs.execution_count AS [avg I/O],
 qs. creation_time,
 qs.max_elapsed_time,
 qs.min_elapsed_time,
 SUBSTRING(qt.text,qs.statement_start_offset/2,
 (CASE WHEN qs.statement_end_offset=-1
 THEN LEN(CONVERT(NVARCHAR(max),qt.text))*2
 ELSE qs.statement_end_offset END -qs.statement_start_offset)/2) AS query_text,
 qt.dbid,dbname=DB_NAME(qt.dbid),
 qt.objectid,
 qs.sql_handle,
 qs.plan_handle
 from sys.dm_exec_query_stats qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
 ORDER BY qs.total_physical_reads DESC

 3.5 使用sp_spaceused查看表的磁盘空间

  exec sp_spaceused 'table_xx'

图片 2

reserved:保留的上空总量
data:数据选择的长空总量
index_size:索引使用空间
Unused: 未用的空间量

 3.6  监测I/0运行情状 STATISTICS IO ON;

 四  磁盘读写瓶颈的病症

  4.1  errorlog里告诉错误 833

  4.2  sys.dm_os_wait_stats 视图里有雅量等候状态PAGEIOLATCH_* 或
WriteLog。当数码在缓冲区里不曾找到,连接的等候意况就是PAGEIOLACTH_EX(写)
PAGEIOLATCH_SH(读),然后发起异步操作,将页面读入缓冲区中。像
waiting_tasks_count和wait_time_ms相比高的时候,日常要等待I/O,除在反映在数据文件上以外,还有writelog的日记文件上。想要得到有意义数据,须求做基线数据,查看感兴趣的光阴距离。

select wait_type,
waiting_tasks_count,
wait_time_ms ,
max_wait_time_ms,
signal_wait_time_ms
from sys.dm_os_wait_stats
where wait_type like 'PAGEIOLATCH%' 
order by wait_type

  wait_type:等待类型
  waiting_tasks_count:该等待类型的等候数
  wait_time_ms:该等待类型的总等待时间(包涵一个经过悬挂状态(Suspend)和可运行景况(Runnable)费用的总时间)
  max_wait_time_ms:该等待类型的最长等待时间
  signal_wait_time_ms:正在等候的线程从收受信号文告到其初始运行之间的时差(一个经过可运行状态Runnable费用的总时间)
  i/o等待时间==wait_time_ms – signal_wait_time_ms

   五  优化磁盘I/O

   5.1
数据文件里页面碎片整理。 当表爆发增删改操作时索引都会发出碎片(索引叶级的页拆分),碎片是指索引上的页不再抱有大体一而再性时,就会时有发生碎片。比如你询问10条数据,碎片少时,可能只扫描2个页,但零星多时可能要扫描愈多页(前边讲索引时在前述)。

   5.2
表格上的目录。比如:指出每个表都包涵聚集索引,这是因为数量存储分为堆和B-Tree,
按B-Tree空间占用率更高。 丰富使用索引减弱对I/0的必要。

   5.3
数据文件,日志文件,TempDB文件指出存放差别物理磁盘,日志文件放写入速度比较快的磁盘上,例如
RAID 10的分区

        5.4
文件空间管理,设置数据库增进时要按一定大小增进,而不能按百分比,那样防止两回涨高太多或太少所带来的不要求麻烦。提议对相比小的数据库设置两次升高50MB到100MB。下图浮现如果按5%来增加近10G, 倘若有一个应用程序在品味插入一行,可是并未空间可用。那么数据库可能会初步增强一个近10G,
文件的拉长可能会耗用太长的时刻,以至于客户端程序插入查询战败。

  图片 3

       5.5 幸免自动缩小文件,如若设置了此功效,sql
server会每隔半钟头检查文件的利用,假若空闲空间>25%,会自行运行dbcc
shrinkfile 动作。自动收缩线程的会话ID
SPID总是6(未来或者有变) 如下显示自动裁减为False。

   
 图片 4

     图片 5

   5.6 倘使数据库的苏醒方式是:完整。
就须求定期做日志备份,幸免日志文件无限的增强,用于磁盘空间。

    

     

相关文章