您的位置:新葡亰496net > 网络数据库 > 新葡亰496net:问题的诊断分,资源等待之PAGEIOL

新葡亰496net:问题的诊断分,资源等待之PAGEIOL

发布时间:2019-06-16 03:53编辑:网络数据库浏览(181)

    一. 概述

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

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

     

     

    一.概念

      在介绍资源等待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
    

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

    新葡亰496net 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  主要磁盘读写的行为

      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,  索引维护,全文索引,统计信息,备份数据,高可用同步日志等。

    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等操作, 会带来比较大的硬盘读写。(读/写)

     

     

    二. 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的过程如下:

    新葡亰496net 2

    新葡亰496net 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秒。

    新葡亰496net 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 
    

    新葡亰496net 5

      总结:PageIOLatch_EX(写入)跟磁盘的写入速度有关系。PageIOLatch_SH(读取)跟内存中的数据缓存有关系。通过上面的sql统计查询,从等待的时间上看,并没有清晰的评估磁盘性能的标准,但可以做评估基准数据,定期重置,做性能分析。要确定磁盘的压力,还需要从windows系统性能监视器方面来分析。 关于内存原理查看”sql server 内存初探“磁盘查看"sql server I/O硬盘交互" 。

    三. 磁盘读写的相关分析

      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: 用户等待在该文件中完成写入所用的总时间毫秒。

      新葡亰496net 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'
    

    新葡亰496net 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 配置会对I/O有影响:

    确定思路... 1

    所以我决定,对我发的《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, 文件的增长可能会耗用太长的时间,以至于客户端程序插入查询失败。

      新葡亰496net 8

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

         新葡亰496net 9

         新葡亰496net 10

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

        

         

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

    wait event的基本troubleshooting. 1

    io

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

    1. physical diskdisk 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的解释:等待日志刷新完成时出现。导致日志刷新的常见操作是检查点和事务提交。

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

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

    虚拟文件信息(virtual file Statistics)... 3

    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.主要是查每个处理器上的任务数和可运行的任务数。

     

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

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

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

    性能指标... 4

    内存

    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,那么说明需要调整或者优化负载或者增加内存。

     

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

    执行计划缓冲的使用... 8

    结束语

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

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

     

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

    总结... 9

     

     

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

    性能调优很难有一个固定的理论。调优本来就是处理一些特殊的性能问题。

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

    2. LogicalDisk and PhysicalDisk: 

    通常一旦拿到一个服务器那么就先做一下性能检查。查看整个数据库是运行在什么样的状况下的。

    新葡亰496net,  %idle time: 

    分析收集的数据想像这种情况是否合理。

      %disk time: = %disk read time  %disk write time

    确定思路

    一个数据库操作的时间都是执行时间 等待时间,在无法估计执行时间的时候看要看看等待时间。

    那么等待时间分为锁等待时间和资源等待时间。

    那么就先用 sys.dm_os_wait_stats动态性能视图,查看主要的状况。如果pageiolatch_sh等待很大,那么就说明,session在等待buffer pool的页。当一个session要select一些数据,但是刚刚好,这些数据并没有在buffer pool 中,那么sql server 就会分配一些缓存这些缓存是属于buffer pool 的,用来存放从磁盘读取出来的数据,在读取的时候都会给这些缓存上latch(可以看成是锁)。当存在io瓶颈的时候,那么磁盘上的数据不能立即读到buffer pool 中就会出现等待latch的情况。这个可能是io过慢,也有可能是在做一些多余的io造成的。

    那么接下来查看sys.dm_io_virtual_file_stats 性能视图来确定哪个数据库造成了怎么大的延迟。并且通过physical disk avg.disk reads/sec和physical diskavg.disk writes/sec来确定到底数据库有多少io负载。

    接下来通过 sys.dm_exec_query_stats 查看执行计划,通过查看高物理读的sql和执行计划看看有没有优化的空间。如添加索引,修改sql,优化引擎访问数据的方法。

    有可能,sql 语句已经不能再优化,但是性能还是不行,往往这种sql是报表查询类的sql,会从磁盘中读取大量数据,很多数据往往在buffer pool 找不到那么就会发生大量的pageiolatch_sh等待。这时,我们就要看看是否是内存不足照成的,用perfmon 查看 page life expectancy(页寿命长度),free list stalls/sec(等待空页的次数)和Lazy writes/sec。 page life expectancy 波动很厉害,free list stalls/sec 一直大于0,Lazy writes/sec 的量也很大,那么就说明buffer pool 不够大。但是也有可能是sql 写的不严谨,select了很多没必要的数据。

     

    在上面的troubleshooting 过程中,很容易走入一个误区,sys.dm_io_virtual_file_stats 和一些性能指标,就会很容易断定说io有问题,需要额外的预算来扩展io的性能,但是扩展io是比较贵的。io性能不理想很有可能miss index或者buffer pool的压力造成的。如果单纯的添加物理设备,但是没有找到根本原因,当数据量增长后,依然会出现相同的问题。

     

      %disk read time

    wait event的基本troubleshooting

     

    wait statistics 是SQLOS跟踪得到的

    SQLOS 是一个伪操作系统,是SQL Server 的一部分,有调度线程,内存管理等其他操作。

    SQLOS比windows调度器更好的调度sql server 线程。SQLOS的调度器间的交互,会比强占式的系统调度又更好的并发性

     

    当sql server 等待一个sql 执行的时候,等待的时间会被sqlos捕获,这些时间都会存放在 sys.dm_os_wait_stats性能视图中。各种等待时间的长度,并且和其他的性能视图,性能计数器结合,可以很明显的看出性能问题。

     

    对于未知的性能问题sys.dm_os_wait_stats 用来判断性能问题是很好用的,但是在服务器重启或者dbcc 命令清空 sys.dm_os_wait_stats后会很好分析,时间一长就很难分析,因为等待时间是累计的,搞不清楚哪个是你刚刚执行出来的时间。当然可以考虑先捕获一份,当sql 执行完后,再捕获一份,进行比较。

     

    查看wait event,得到的信息只是实际性能问题的其中一个症状,为了更利用wait event 信息,你需要了解资源等待和非资源等待的区别,还有需要了解其他troubleshooting信息。

     

    在sql server中有一部分的sql是没问题的,可以使用一下sql 语句查看说有的 session的wait event

    SELECT DISTINCT

    wt.wait_type

    FROM sys.dm_os_waiting_tasks AS wt

    JOIN sys.dm_exec_sessions AS s ON wt.session_id = s.session_id

    WHERE s.is_user_process = 0

    因为很大一部分是正常的,所以提供了一个sql 来过滤正常查询操作

    SELECT TOP 10

    wait_type ,

    max_wait_time_ms wait_time_ms ,

    signal_wait_time_ms ,

    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,

    100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )

    AS percent_total_waits ,

    100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )

    AS percent_total_signal_waits ,

    100.0 * ( wait_time_ms - signal_wait_time_ms )

    / SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits

    FROM sys.dm_os_wait_stats

    WHERE wait_time_ms > 0 -- remove zero wait_time

    AND wait_type NOT IN -- filter out additional irrelevant waits

    ( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',

    'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',

    'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',

    'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',

    'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',

    'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',

    'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',

    'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',

    'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',

    'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',

    'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',

    'RESOURCE_QUEUE' )

    ORDER BY wait_time_ms DESC

    检查wait event一般只关注前几个等待信息,查看高等待时间的等待类型。

    CXPACKET:

         表明并发查询的等待时间,通常不会立刻产生问题,也可能是因为别的性能问题,导致CXPACKET等待过高。

    SOS_SCHEDULER_YIELD

         任务在执行的时候被调度器中断,被放入可执行队列等待被运行。这个时间过长可能是cpu压力造成的。

    THREADPOOL

         一个任务必须绑定到一个工作任务才能执行,threadpool 就是task等待被绑定的时间。出现threadpool过高可能是,cpu不够用,也可能是大量的并发查询。

    *LCK_**

         这中等待类型过高,说明可能session发生堵塞,可以看sys.dm_db_index_operational_stats 获得更深入的内容

    PAGEIOLATCH_,IO_COMPLETION,WRITELOG*

         这些往往和磁盘的io瓶颈关联,根本原因往往都是效率极差的查询操作消费了过多的内存。PAGEIOLATCH_*和数据库文件的读写延迟相关。writelog和事务日               志文件的读写相关。这些等待最好和sys.dm_io_virtual_file_stats 关联确定问题是发生在数据库,数据文件,磁盘还是整个实例。

    *PAGELATCH_**

         在buffer pool 中非io等待latch。PAGELATCH_* 大量的等待通常是分配冲突。当tempdb中大量的对象要被删除或者创建,那么系统就会对SGAM,GAM和PFS的分配发生冲突。

    *LATCH_**

         LATCH_*和内部cache的保护,这种等待过高会发生大量的问题。可以通过 sys.dm_os_latch_stats 查看详细内容。

    ASYNC_NETWORK_IO

         这个等待不完全表明网络的瓶颈。事实上多数情况下是客户端程序一行一行的处理sql server 的结果集导致。发生这种问题那么就修改客户端代码。

    简单的解释了主要的等待,减少在分析wait event 的时候走的弯路。

    为了确定是否已经排除问题可以用DBCC SQLPERF('sys.dm_os_wait_stats', clear)清除wait event。也可以用2个wait event 信息相减。

      %disk write time

    虚拟文件信息(virtual file Statistics)

    通常,当使用wait event 分析问题的时候,都为认为很想io的性能问题。但是wait event 并不能说明io是怎么发生的,所以很有可能会误判

     

    这就是为什么要使用sys.dm_os_latch_stats 查看的原因,可以查看累计的io统计信息,每个文件的读写信息,日志文件的读写,可以计算读写的比例,io等待的次数,等待的时间。

    SELECT DB_NAME(vfs.database_id) AS database_name ,

    vfs.database_id ,

    vfs.FILE_ID ,

    io_stall_read_ms / NULLIF(num_of_reads, 0) AS avg_read_latency ,

    io_stall_write_ms / NULLIF(num_of_writes, 0)

    AS avg_write_latency ,

    io_stall / NULLIF(num_of_reads   num_of_writes, 0)

    AS avg_total_latency ,

    num_of_bytes_read / NULLIF(num_of_reads, 0)

    AS avg_bytes_per_read ,

    num_of_bytes_written / NULLIF(num_of_writes, 0)

    AS avg_bytes_per_write ,

    vfs.io_stall ,

    vfs.num_of_reads ,

    vfs.num_of_bytes_read ,

    vfs.io_stall_read_ms ,

    vfs.num_of_writes ,

    vfs.num_of_bytes_written ,

    vfs.io_stall_write_ms ,

    size_on_disk_bytes / 1024 / 1024. AS size_on_disk_mbytes ,

    physical_name

    FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs

    JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id

    AND vfs.FILE_ID = mf.FILE_ID

    ORDER BY avg_total_latency DESC

    查看是否读写过大,平均延时是否过高。通过这个可以知道是否是io的问题。

    如果数据文件和日志文件是共享磁盘队列的,avg_total_latency 比预期的要高,那么就有可能是io的问题了

     

    如果当前的数据库是用来归档数据到比较慢的存储中,可能会有很高的PAGEIOLATCH_*和io_stall那么我们就需要确定怎么高的等待是否属于归档的线程,因此在troubleshooting的时候要注意你的服务器的类型。

    如果你的磁盘读写比例是1:10,而且又很高的 avg_total_latency 那么就考虑把磁盘队列换成 raid5,为io读提供更多的主轴。

     

      Avg. disk sec/read

    性能指标

    在最开始的troubleshooting,性能指标是非常有用的。也可以用来验证自己的判断是否正确。

    PLA 是一个很好的性能日志分析工具. 可惜没有中文版,当然可以去codeplex 下载源代码自己修改。这个工具内嵌了性能收集集合,也就是通常要收集的一些性能指标。也内嵌了阀值模板,可以在性能指标收集完之后做分析。

     

    sql server 对自己的性能指标有对应的性能视图 sys.dm_os_performance_counters。对于性能指标有些是累计值,因此需要做2个快照,相减计算结果。

    DECLARE @CounterPrefix NVARCHAR(30)

    SET @CounterPrefix = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'

    THEN 'SQLServer:'

    ELSE 'MSSQL$'   @@SERVICENAME   ':'

    END ;

    -- Capture the first counter set

    SELECT CAST(1 AS INT) AS collection_instance ,

    [OBJECT_NAME] ,

    counter_name ,

    instance_name ,

    cntr_value ,

    cntr_type ,

    CURRENT_TIMESTAMP AS collection_time

    INTO #perf_counters_init

    FROM sys.dm_os_performance_counters

    WHERE ( OBJECT_NAME = @CounterPrefix   'Access Methods'

    AND counter_name = 'Full Scans/sec'

    )

    OR ( OBJECT_NAME = @CounterPrefix   'Access Methods'

    AND counter_name = 'Index Searches/sec'

    )

    OR ( OBJECT_NAME = @CounterPrefix   'Buffer Manager'

    AND counter_name = 'Lazy Writes/sec'

    )

    OR ( OBJECT_NAME = @CounterPrefix   'Buffer Manager'

    AND counter_name = 'Page life expectancy'

    )

    OR ( OBJECT_NAME = @CounterPrefix   'General Statistics'

    AND counter_name = 'Processes Blocked'

    )

    OR ( OBJECT_NAME = @CounterPrefix   'General Statistics'

    AND counter_name = 'User Connections'

    )

    OR ( OBJECT_NAME = @CounterPrefix   'Locks'

    AND counter_name = 'Lock Waits/sec'

    )

    OR ( OBJECT_NAME = @CounterPrefix   'Locks'

    AND counter_name = 'Lock Wait Time (ms)'

    )OR ( OBJECT_NAME = @CounterPrefix   'SQL Statistics'

    AND counter_name = 'SQL Re-Compilations/sec'

    )

    OR ( OBJECT_NAME = @CounterPrefix   'Memory Manager'

    AND counter_name = 'Memory Grants Pending'

    )

    OR ( OBJECT_NAME = @CounterPrefix   'SQL Statistics'

    AND counter_name = 'Batch Requests/sec'

    )

    OR ( OBJECT_NAME = @CounterPrefix   'SQL Statistics'

    AND counter_name = 'SQL Compilations/sec'

    )

    -- Wait on Second between data collection

    WAITFOR DELAY '00:00:01'

    -- Capture the second counter set

    SELECT CAST(2 AS INT) AS collection_instance ,

    OBJECT_NAME ,

    counter_name ,

    instance_name ,

    cntr_value ,

    cntr_type ,

    CURRENT_TIMESTAMP AS collection_time

    INTO #perf_counters_second

    FROM sys.dm_os_performance_counters

    WHERE ( OBJECT_NAME = @CounterPrefix   'Access Methods'

    AND counter_name = 'Full Scans/sec'

    )

    OR ( OBJECT_NAME = @CounterPrefix   'Access Methods'

    AND counter_name = 'Index Searches/sec'

    )

    OR ( OBJECT_NAME = @CounterPrefix   'Buffer Manager'

    AND counter_name = 'Lazy Writes/sec'

    )

    OR ( OBJECT_NAME = @CounterPrefix   'Buffer Manager'

    AND counter_name = 'Page life expectancy'

    )

    OR ( OBJECT_NAME = @CounterPrefix   'General Statistics'

    AND counter_name = 'Processes Blocked'

    )

    OR ( OBJECT_NAME = @CounterPrefix   'General Statistics'

    AND counter_name = 'User Connections'

    )OR ( OBJECT_NAME = @CounterPrefix   'Locks'

    AND counter_name = 'Lock Waits/sec'

    )

    OR ( OBJECT_NAME = @CounterPrefix   'Locks'

    AND counter_name = 'Lock Wait Time (ms)'

    新葡亰496net:问题的诊断分,资源等待之PAGEIOLATCH。)

    OR ( OBJECT_NAME = @CounterPrefix   'SQL Statistics'

    AND counter_name = 'SQL Re-Compilations/sec'

    )

    OR ( OBJECT_NAME = @CounterPrefix   'Memory Manager'

    AND counter_name = 'Memory Grants Pending'

    )

    OR ( OBJECT_NAME = @CounterPrefix   'SQL Statistics'

    AND counter_name = 'Batch Requests/sec'

    )

    OR ( OBJECT_NAME = @CounterPrefix   'SQL Statistics'

    AND counter_name = 'SQL Compilations/sec'

    )

    -- Calculate the cumulative counter values

    SELECT i.OBJECT_NAME ,

    i.counter_name ,

    i.instance_name ,

    CASE WHEN i.cntr_type = 272696576

    THEN s.cntr_value - i.cntr_value

    WHEN i.cntr_type = 65792 THEN s.cntr_value

    END AS cntr_value

    FROM #perf_counters_init AS i

    JOIN #perf_counters_second AS s

    ON i.collection_instance   1 = s.collection_instance

    AND i.OBJECT_NAME = s.OBJECT_NAME

    AND i.counter_name = s.counter_name

    AND i.instance_name = s.instance_name

    ORDER BY OBJECT_NAME

    -- Cleanup tables

    DROP TABLE #perf_counters_init

    DROP TABLE #perf_counters_second

    主要收集一下性能指标:

    • SQLServer:Access MethodsFull Scans/sec

    • SQLServer:Access MethodsIndex Searches/sec

    • SQLServer:Buffer ManagerLazy Writes/sec

    • SQLServer:Buffer ManagerPage life expectancy

    • SQLServer:Buffer ManagerFree list stalls/sec

    • SQLServer:General StatisticsProcesses Blocked

    • SQLServer:General StatisticsUser Connections

    • SQLServer:LocksLock Waits/sec

    • SQLServer:LocksLock Wait Time (ms)

    • SQLServer:Memory ManagerMemory Grants Pending

    • SQLServer:SQL StatisticsBatch Requests/sec

    • SQLServer:SQL StatisticsSQL Compilations/sec

    新葡亰496net:问题的诊断分,资源等待之PAGEIOLATCH。• SQLServer:SQL StatisticsSQL Re-Compilations/sec

     

    这里又2个 Access Methods 性能指标,说明了访问数据库不同的方式,full scans/sec 表示了发生在数据库中索引和表扫描的次数。

    如果io出现瓶颈,并且伴随着大量的扫描出现,那么很有可能就是miss index 或者sql 代码不理想照成的。那么多少次数到多少时可以认为有问题呢?在通常状况下 index searches/sec 比 full scans/sec 高800-1000,如果 full sacans/sec过高,那么很有可能是miss index 和多余的io操作引起的。

     

    Buffer Manager 和 memory manager 通常用来检测是否存在内存压力,lazy writes/sec,page life expectancy ,free list stalls/sec 用来佐证是否处于内存压力。

    很多网上的文章和论坛都说,如果Page Life expectancy 低于300秒的时候,存在内存压力。但是这只是对于以前只有4g内存的服务器的,现在的服务器一般都是32g以上内存5分钟的阀值已经不能在说明问题了。300秒虽然已经不再适用,但是我们可以用300来作为基值来计算当前的PLE的阀值 (32/4)*300 = 2400那么如果是32g的服务器设置为2400可能会比较合适。

     

    如果PEL一直低于阀值,并且 lazy writes/sec一直很高,那么有可能是buffer pool压力造成的。如果这个时候full scans/sec值也很高,那么请先检查是不是miss index 或者读取了多余的数据。

     

    general statisticsprocesses blocked,lockslock waits/sec和lockslock wait time(ms)如果这3个值都是非0那么数据库会发生堵塞。

     

    SQL Statistics 计数器说明了sql 的编译或者重编译的速度,sql compilations/sec和 batch requests/sec 成正比,那么很有可能大量sql 访问都是 ad hoc方式无法通过执行计划缓冲优化它们,如果 SQL Re-compilations/sec 和 batch requests/sec 成正比,那么应用程序中可能又强制重新编译的选项。

     

    memory managermomory grants pending 表示等待授权内存的等待,如果这个值很高那么增加内存可能会有效果。但是也有可能是大的排序,hash操作也可能造成,可以使用调整索引或者查询来减小这种状况。

    **

    **

      Avg. disk sec/write:   很好:<10ms    一般:10-20ms   有点慢:20-50ms   非常慢:> 50ms

    执行计划缓冲的使用

    执行计划缓冲是sql server 的内部组件,可以使用 sys.dm_exec_query_stats 查询,下面有个sql查询物理读前十的计划

    SELECT TOP 10

    execution_count ,

    statement_start_offset AS stmt_start_offset ,

    sql_handle ,

    plan_handle ,

    total_logical_reads / execution_count AS avg_logical_reads ,

    total_logical_writes / execution_count AS avg_logical_writes ,

    total_physical_reads / execution_count AS avg_physical_reads ,

    t.text

    FROM sys.dm_exec_query_stats AS s

    CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t

    ORDER BY avg_physical_reads DESC

    在执行计划里面的这些值可以看出哪些查询物理io操作很频繁,也可以和wait event 和虚拟文件结合分析有问题的io操作。

    我们也可以使用sys.dm_exec_query_plan()查看存在内存里面的执行计划。

    这里又2本书深入的讲述了查询执行计划:《SQL Server 2008 Query performance tuning distilled》,《Inside Microsoft SQL Server 2008:T-SQL Querying》。

    sys.dm_exec_query_stats还用来查询 cpu时间,最长执行时间,或者最频繁的sql

    在sql server 2008中加入了2个额外的列,query_hash,query_plan_hash用来聚合相似的sql的。对于ad hoc 过大的服务器可以用来分析相似的sql,不同的编译的总数。

     

      Avg. disk bytes/transfer

    总结

    上面各个部分都讲了一个思维,一个思路。要想性能调优调的好,那么就先系统体系结构,你要清楚如前面说的miss index 一旦发生,那么不知会影响io,还会影响内存和cpu。接下来要会分析,从一开始的简单的性能统计信息,往下分析,用其他统计信息排除问题,得到性能问题的真正原因。

    文章来源于:Troubleshooting SQL Server: A Guide for the Accidental DBA 如果看不懂的或者想更深入了解的,可以看原文。

     

      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

    1. 找出那个数据库哪个文件总做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 下载地址: 

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

       新葡亰496net 11

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

     



     

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496net:问题的诊断分,资源等待之PAGEIOL

    关键词: