金沙国际官网_金沙国际平台登录

因为这个金沙国际官网_金沙国际平台登录网站与很多的大型澳门赌场都有合作,金沙国际官网_金沙国际平台登录尽职尽责,高效执行,保持好奇心,不断学习,追求卓越,点击进入金沙国际官网_金沙国际平台登录马上体验吧,所以现在也正式地开始了营业。

您的位置:金沙国际官网 > 数据库 > 资源等待之PAGEIOLATCH,中的回滚

资源等待之PAGEIOLATCH,中的回滚

发布时间:2019-10-25 15:33编辑:数据库浏览(98)

    USE [TestDB]
    GO
    /****** 对象:  Table [dbo].[Person]    脚本日期: 11/23/2008 13:37:48 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Person](
        [PersonId] [nchar](18) NOT NULL,
        [PersonName] [nchar](20) NOT NULL,
     CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
    (
        [PersonId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    

    一.概念

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

    CHECK约束添加规则

    1、CHECK 约束用于限制列中的值的范围。

    2、Check约束通过逻辑表达式来判断数据的有效性,用来限制输入一列或多列的值的范围,在列中更新数据时,所要输入的内容必须满足Check约束的条件,否则将无法正确输入。

    3、如果对单个列定义 CHECK 约束,那么该列只允许特定的值。

    4、如果对一个表定义 CHECK 约束,那么此约束会在特定的列中对值进行限制。

    默认情况下如果执行一个事务中出现错误,则只回滚错误操作语句(就是说这句不执行了,算不上回滚),错误处之前或之后的正确操作语句还是会被提交。如:

    二. 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硬盘交互" 。

    使用SSMS数据库管理工具添加CHECK约束

    1、连接数据库,选择数据库,选择数据表-》右键点击(或者直接点击约束,右键点击,选择添加约束,后面步骤相同)-》选择设计。

    图片 6

    2、选择要添加约束的数据列-》右键点击-》选择CHECK约束。

    图片 7

    3、在CHECK约束弹出框中点击添加。

    图片 8

    4、在CHECK约束弹出框中-》输入CHECK约束表达式-》输入CHECK约束名-》输入CHECK约束描述-》其他可以选择默认。

    图片 9

    5、点击关闭-》点击保存按钮(或者ctrl+s)-》刷新表查看结果。

    图片 10

    Use TestDB
    
    Begin TransAction
        Insert Into Person(PersonId,PersonName)
                    Values('1','Name1')
        Insert Into Person(PersonId,PersonName)
                    Values('1','Name1')
        Insert Into Person(PersonId,PersonName)
                    Values('3','Name3')
    Commit TransAction
    /*
        Select 一下 有'1','Name1'和'3','Name3',
        说明只有第二句的错误被取消了
    */
    

    使用T-SQL脚本添加CHECK约束

    全部回滚的方法1:打开 XACT_ABORT

    当表结构存在时

    添加CHECK约束时首先校验约束是否已存在,如果存在应该是先删除再添加,如果不存在则直接添加。

    语法:

    -- 添加一个默认约束
    use 数据库名;
    go

    if exists(select * from sysobjects where name=约束名)
    alter table 表名 drop constraint 约束名;
    go

    alter table 表名 add constraint 约束名 check(约束规则),constraint 约束名 check(约束规则);
    go

    示例:

    -- 添加一个默认约束
    use testss;
    go

    if exists(select * from sysobjects where name='check1')
    alter table test1 drop constraint check1;
    go

    alter table test1 add constraint check1 check(height>=170 and height<=175);
    go

     图片 11

    图片 12

    本文由金沙国际官网发布于数据库,转载请注明出处:资源等待之PAGEIOLATCH,中的回滚

    关键词: