图片 10

O硬盘交互,品质调优

一.概念

  在介绍资源等待PAGEIOLATCH之前,先来了解下从实例级别来分析的各种资源等待的dmv视图sys.dm_os_wait_stats。它是返回执行的线程所遇到的所有等待的相关信息,该视图是从一个实际级别来分析的各种等待,它包括200多种类型的等待,需要关注的包括PageIoLatch(磁盘I/O读写的等待时间),LCK_xx(锁的等待时间),WriteLog(日志写入等待),PageLatch(页上闩锁)Cxpacket(并行等待)等以及其它资源等待排前的。 

  1.  下面根据总耗时排序来观察,这里分析的等待的wait_type 不包括以下

SELECT  wait_type ,
        waiting_tasks_count,
        signal_wait_time_ms ,
        wait_time_ms,
        max_wait_time_ms
FROM    sys.dm_os_wait_stats
WHERE   wait_time_ms > 0
        AND wait_type NOT IN ( 'CLR_SEMAPHORE', 'CLR_AUTO_EVENT',
                               'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
                               'SLEEP_TASK', 'SLEEP_SYSTEMTASK',
                               'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',
                               'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE',
                               'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
                               'BROKER_TO_FLUSH', 'BROKER_TASK_STOP',
                               'CLR_MANUAL_EVENT',
                               'DISPATCHER_QUEUE_SEMAPHORE',
                               'FT_IFTS_SCHEDULER_IDLE_WAIT',
                               'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN',
                               'SQLTRACE_INCREMENTAL_FLUSH_SLEEP' )
ORDER BY signal_wait_time_ms DESC

  下图排名在前的资源等待是重点需要去关注分析:

图片 1

  通过上面的查询就能找到PAGEIOLATCH_x类型的资源等待,由于是实例级别的统计,想要获得有意义数据,就需要查看感兴趣的时间间隔。如果要间隔来分析,不需要重启服务,可通过以下命令来重置

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);  

  wait_type:等待类型
  waiting_tasks_count:该等待类型的等待数
  wait_time_ms:该等待类型的总等待时间(包括一个进程悬挂状态(Suspend)和可运行状态(Runnable)花费的总时间)
  max_wait_time_ms:该等待类型的最长等待时间
  signal_wait_time_ms:正在等待的线程从收到信号通知到其开始运行之间的时差(一个进程可运行状态(Runnable)花费的总时间)
  io等待时间==wait_time_ms – signal_wait_time_ms

一. 概述

 sql server作为关系型数据库,需要进行数据存储,
那在运行中就会不断的与硬盘进行读写交互。如果读写不能正确快速的完成,就会出现性能问题以及数据库损坏问题。下面讲讲引起I/O的产生,以及分析优化。

 

二. PAGEIOLATCH_x

  2.1 什么是Latch

    在sql
server里latch是轻量级锁,不同于lock。latch是用来同步sqlserver的内部对象(同步资源访问),而lock是用来对于用户对象包括(表,行,索引等)进行同步,简单概括:Latch用来保护SQL server内部的一些资源(如page)的物理访问,可以认为是一个同步对象。而lock则强调逻辑访问。比如一个table,就是个逻辑上的概念。关于lock锁这块在”sql server
锁与事务拨云见日”中有详细说明。

  2.2 什么是PageIOLatch 

  当查询的数据页如果在Buffer
pool里找到了,则没有任何等待。否则就会发出一个异步io操作,将页面读入到buffer
pool,没做完之前,连接会保持在PageIoLatch_ex(写)或PageIoLatch_sh(读)的等待状态,是Buffer
pool与磁盘之间的等待。它反映了查询磁盘i/o读写的等待时间。
  当sql
server将数据页面从数据文件里读入内存时,为了防止其他用户对内存里的同一个数据页面进行访问,sql
server会在内存的数据页同上加一个排它锁latch,而当任务要读取缓存在内存里的页面时,会申请一个共享锁,像是lock一样,latch也会出现阻塞,根据不同的等待资源,等待状态有如下:PAGEIOLATCH_DT,PAGEIOLATCH_EX,PAGEIOLATCH_KP,PAGEIOLATCH_SH,PAGEIOLATCH_UP。重点关注PAGEIOLATCH_EX(写入)和PAGEIOLATCH_SH(读取)二种等待。

2.1  AGEIOLATCH流程图

  有时我们分析当前活动用户状态下时,一个有趣的现象是,有时候你发现某个SPID被自己阻塞住了(通过sys.sysprocesses了查看)
为什么会自己等待自己呢? 这个得从SQL server读取页的过程说起。SQL
server从磁盘读取一个page的过程如下:

图片 2

图片 3

  (1):由一个用户请求,获取扫描X表,由Worker x去执行。

  (2):在扫描过程中找到了它需要的数据页同1:100。

  (3):发面页面1:100并不在内存中的数据缓存里。

  (4):sql
server在缓冲池里找到一个可以存放的页面空间,在上面加EX的LATCH锁,防止数据从磁盘里读出来之前,别人也来读取或修改这个页面。

  (5):worker x发起一个异步i/o请求,要求从数据文件里读出页面1:100。

  (6):由于是异步i/o(可以理解为一个task子线程),worker
x可以接着做它下面要做的事情,就是读出内存中的页面1:100,读取的动作需要申请一个sh的latch。

  (7):由于worker
x之前申请了一个EX的LATCH锁还没有释放,所以这个sh的latch将被阻塞住,worker
x被自己阻塞住了,等待的资源就是PAGEIOLATCH_SH。

  最后当异步i/o结束后,系统会通知worker
x,你要的数据已经写入内存了。接着EX的LATCH锁释放,worker
x申请得到了sh的latch锁。

总结:首先说worker是一个执行单元,下面有多个task关联Worker上,
task是运行的最小任务单元,可以这么理解worker产生了第一个x的task任务,再第5步发起一个异步i/o请求是第二个task任务。二个task属于一个worker,worker
x被自己阻塞住了。 关于任务调度了解查看sql server
任务调度与CPU。

 2.2 具体分析

  通过上面了解到如果磁盘的速度不能满足sql
server的需要,它就会成为一个瓶颈,通常PAGEIOLATCH_SH
从磁盘读数据到内存,如果内存不够大,当有内存压力时候它会释放掉缓存数据,数据页就不会在内存的数据缓存里,这样内存问题就导致了磁盘的瓶颈。PAGEIOLATCH_EX是写入数据,这一般是磁盘的写入速度明显跟不上,与内存没有直接关系。

下面是查询PAGEIOLATCH_x的资源等待时间:

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

下面是查询出来的等待信息:

PageIOLatch_SH
总等待时间是(7166603.0-15891)/1000.0/60.0=119.17分钟,平均耗时是(7166603.0-15891)/297813.0=24.01毫秒,最大等待时间是3159秒。

PageIOLatch_EX 总等待时间是(3002776.0-5727)/1000.0/60.0=49.95分钟,   
平均耗时是(3002776.0-5727)/317143.0=9.45毫秒,最大等待时间是1915秒。

图片 4

关于I/O磁盘 sys.dm_io_virtual_file_stats 函数也做个参考

SELECT  
       MAX(io_stall_read_ms) AS read_ms,
         MAX(num_of_reads) AS read_count,
       MAX(io_stall_read_ms) / MAX(num_of_reads) AS 'Avg Read ms',
         MAX(io_stall_write_ms) AS write_ms,
        MAX(num_of_writes) AS write_count,
         MAX(io_stall_write_ms) /  MAX(num_of_writes) AS 'Avg Write ms'
FROM    sys.dm_io_virtual_file_stats(null, null)
WHERE   num_of_reads > 0 AND num_of_writes > 0 

图片 5

  总结:PageIOLatch_EX(写入)跟磁盘的写入速度有关系。PageIOLatch_SH(读取)跟内存中的数据缓存有关系。通过上面的sql统计查询,从等待的时间上看,并没有清晰的评估磁盘性能的标准,但可以做评估基准数据,定期重置,做性能分析。要确定磁盘的压力,还需要从windows系统性能监视器方面来分析。
关于内存原理查看”sql server
内存初探“磁盘查看”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: 用户等待在该文件中完成写入所用的总时间毫秒。

  图片 6

  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'

图片 7

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

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

在写这篇东西的时候我也不是很清楚性能基线,到底要检查点什么,dmv要不要检查,perfmon要检测那先。

 四  磁盘读写瓶颈的症状

  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

所以我决定,对我发的《sql server 性能调优》文章内的 perfmon和dmv做一个总结。来建立自己的性能基线。

   五  优化磁盘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,
文件的增长可能会耗用太长的时间,以至于客户端程序插入查询失败。

  图片 8

       5.5 避免自动收缩文件,如果设置了此功能,sql
server会每隔半小时检查文件的使用,如果空闲空间>25%,会自动运行dbcc
shrinkfile 动作。自动收缩线程的会话ID
SPID总是6(以后可能有变) 如下显示自动收缩为False。

   
 图片 9

     图片 10

   5.6 如果数据库的恢复模式是:完整。
就需要定期做日志备份,避免日志文件无限的增长,用于磁盘空间。

    

     

io

在io中我们要注意哪些性能指标呢?

  1. physical
    disk\disk reads/sec   –这个应该很清楚
    一看就就知道 这个指标是指什么的

  2. physical disk\ disk writes/sec

一打开文章就看到这2个值,而却有阀值,看到阀值很开心,因为不用你去收集值了。

• Less than 10 ms = good performance

• Between 10 ms and 20 ms = slow performance

• Between 20 ms and 50 ms = poor performance

• Greater than 50 ms = significant performance
problem.

接下来就是 sys.dm_os_wait_stats
中的几个wait type

3.
 PAGEIOLATCH_* 

 PAGEIOLATCH_* 系列的wait type 一共有

PAGEIOLATCH_DT   — 破坏,什么是破坏,就是把内存中数据页释放掉
PAGEIOLATCH_EX   — x锁,可以怎么理解,就是排他占用这个锁

PAGEIOLATCH_KP   — 保持,就是保持这个页不被破坏
PAGEIOLATCH_NL   — 没有定义,保留
PAGEIOLATCH_SH   — 在读,数据页的时候就分配这个闩

PAGEIOLATCH_UP   — 在更新的时候分配这个            

根据onlinebook的解释:在任务等待 I/O 请求中缓冲区的闩锁时发生。闩锁请求处于“XX”模式。长时间的等待可能指示磁盘子系统出现问题。

讲的直白一点就是系统在io,入读或写的时候分配的。等待io请求

4.
ASYNC_IO_COMPLETION

根据onlinebook的解释:当某任务正在等待 I/O 完成时出现

这个是等待异步io完成,那么和上面有没有关系呢?答案是没有,上面等待的是io读取出来,或者写入。这个是等待系统的异步io完成是不一样的概念。

5.
IO_COMPLETION

根据onlinebook的解释:在等待 I/O 操作完成时出现。通常,该等待类型表示非数据页 I/O。数据页 I/O 完成等待显示为 PAGEIOLATCH_* waits。

这个就不解释了说的很明白了就是等待非数据页的io完成

6.
WRITELOG

根据onlinebook的解释:等待日志刷新完成时出现。导致日志刷新的常见操作是检查点和事务提交。

这个也不多解释,就是写入日志时候等待的时间。

cpu

7.Processor/
%Privileged Time                          –内核级别的cpu使用率

8.Processor/ %User
Time                                   –用户几倍的cpu使用率

9.Process
(sqlservr.exe)/ %Processor Time    –某个进程的cpu使用率

10.SQLServer:SQL
Statistics/Auto-Param Attempts/sec  
 –试图运行自动参数化次数

11. SQLServer:SQL Statistics/Failed Auto-params/sec       — 自动参数化失败

12. SQLServer:SQL Statistics/Batch Requests/sec      
      — 批处理量

13. SQLServer:SQL Statistics/SQL Compilations/sec    
     — 编译次数

14.  SQLServer:SQL Statistics/SQL Re-Compilations/sec  
 — 反编译次数

15.  SQLServer:Plan Cache/Cache hit Ratio              
             — 执行计划,cache命中率

接下来还是 wait event的

16.signal_wait_time_ms –从发出信号到开始运行的时间差,时间花费在等待运行队列中,是单纯的cpu等待。

下面代码量化的像是signal_wait_time_ms占的比重

SELECT SUM(signal_wait_time_ms) AS TotalSignalWaitTime ,

( SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2)))

/ SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100 )

AS PercentageSignalWaitsOfTotalTime

FROM sys.dm_os_wait_stats

在创建baseline 的时候 完全可以 按这个sql来获取值。

17.SOS_SCHEDULER_YIELD等待

onlinebook的解释:在任务自愿为要执行的其他任务生成计划程序时出现。在该等待期间任务正在等待其量程更新。

完全看不懂,啥叫量程。

直白的说就是:当查询自动放弃cpu,并且等待恢复执行,这个等待就叫做SOS_SCHEDULER_YIELD。

18.CXPACKET等待

onlinebook:当尝试同步查询处理器交换迭代器时出现。如果针对该等待类型的争用成为问题时,可以考虑降低并行度。

直白点就是:处理器之间的一种同步,一般出现在
并发查询,为啥?因为只有并发查询才用多个处理器。

接下来是 sys.dm_os_schedulers 

SELECT scheduler_id ,

current_tasks_count ,

runnable_tasks_count

FROM sys.dm_os_schedulers

WHERE scheduler_id < 255

19.主要是查每个处理器上的任务数和可运行的任务数。

 

内存

20.SQL Server :Buffer Manager

又很多有用的计数器都是这 buffer manager 对象下面,可以帮助发现buffer pool滚筒的问题。

21.buffer cache hit ratio

buffer cache hit ratio一般情况下在oltp中要高于95%,在olap中要高于90%。可惜的是没有关于这个性能指标相关的解释,和这个值是如何影响预读机制的。如果这个指标的值有巨大的下降那么就说明有问题。这个不能说明内存压力和sql server 健康指数。

22.page life expectancy

page life expectancy是页生命周期,也就是一个数据页在内存中的时间。在以前sql
server 2000 4g的内存已经很大了,sql server buffer
pool的大小是1.6g,如果sql
server 从磁盘上读取1.6g的数据也只要5分钟,但是今天64g的内存是主流,如果从磁盘一下子读取50g的内存,会严重的冲击io。当存在大量的查询扫描表,读入新的数据页,导致生命周期值下降也不是不正常的。这个值必须长期的监视来分析问题。

23.Free Pages

free pages是内存中空页的数量,不要接近于0。这个值说明查询能否在其他查询不是放内存的情况下,快速的分配内存的主要依据。如果free pages
很少,页生命周期很短,并且伴随着空页争用(free
list stalls/sec)的情况那么很有可能导致内存压力。

24.Free list stalls/sec

Free list stalls/sec每秒空页等待的数量,如果一段时间内都在0以上那么说明可能存在内存压力。

25.lazy write/sec

lazy write/sec 就是每秒写入磁盘的次数。如果发生量很大并且生命周期很短,free page 很少,但是 free list stall/sec 量很大,那么就是发生内存压力了。

SQL Server:memory Manager

SQL Server:memory
Manager对象内对内存的消费和内存管理的问题提供了很重要参考

26.total server
memory 和 target server memory

这2个计数器代表了当前sql server 使用的总共内存和sql server 想要用的内存。如果 target server memory超过了total server memory,也是内存压力的重要标志。sql
server
会减少内存的需求来接近服务的可用内存,或者通过最大服务器内存配置,所以当内存出现压力问题的时候不应该第一时间去查看这2个计数器

28.memory grants outstanding

该值是现实多少进程已经成功的获取了内存的授权。在一段时间内,业务高峰期,如果该值过低,那么标志可能存在内存压力,特别是 memory grants pending 也比较高的情况下。

29. memory grants pending

该值是有过少进程正在等待内存的授权。如果为非0,那么说明需要调整或者优化负载或者增加内存。

 

结束语

每个需要跟踪的东西我都简单的解释了一下。关于 wait event
是累计计数的,在计算的时候需要相减。

这样跟踪个一天,设置好频率,就能得出性能基线了,可以做成图标,这样通过图形就更容易看出问题了。