SQL Server等待事件新解

     资源等待类型 

    并行：CXPACKET 

    Buffer：PAGEIOLATCH_X 

   非Buffer：LATCH_X  

   I/O：ASYNC_IO_COMPITION；IO_COMPITION  

  CPU：SOS_SCHEDULER_YIELD

  日志：WRITELOG&LOGBUFFER   
  锁阻塞：LCK_* 

 内存：RESOURCE_SEMAPHORE

 外部：PREEMPTIVE_XXX

 备份：DISKIO_SUSPENDE

等待事件： ASYNC_NETWORK_IO

 清空当前等待事件统计  ：  DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR); 

       说明：  该SQL查询有查出大量的数据行或者是大量数据，如（text 或者 varchar(max) 字段）

 

    等待事件： HADR_SYNC_COMMIT

      说明： 实时备库可能有很耗CPU的sql

 

    等待事件： WRITELOG

      说明： 数据库的写入量比较大

 

    等待事件： LATCH_EX [ACCESS_METHODS_DATASET_PARENT]

     说明：查询没有索引，需要回表查数据

              LATCH_EX：Exclusive Latch，表明数据正在被改变或添加。两个EX Latch不能同时保留在用一个页上

 

      查看sys.dm_os_wait_stats的内容，你会看到LATCH_, PAGELATCH_ 和PAGEIOLATCH_打头的Latch，

        其中LATCH_打头的等待都是用于Non-BUF类型，

        PAGELATCH_打头的Latch应用于各种页，

        PAGEIOLATCH_打头的Latch用于数据从磁盘移到RAM。

  

Other information:

A latch is a lightweight synchronization mechanism that provides synchronization between threads trying to read or change a data structure in SQL Server. There are three types of latches:

• Latches on pages being read from disk (these are covered by the PAGEIOLATCH_XX wait types – see the PAGEIOLATCH_SH wait type for more details)
• Latches on pages already in memory (these are covered by the PAGELATCH_XX wait types – see the PAGELATCH_EX wait type for more details)
• Latches on non-page data structures (i.e. everything else)

The LATCH_SH and LATCH_EX wait types occur when a thread requires access to a non-page data structure (e.g., page buffers in the buffer pool (latch type = BUFFER), or the data structure that represents a database’s data and log files (latch type = FGCB_ADD_REMOVE)).

 

      https://blog.csdn.net/burgess_liu/article/details/19607151