葡京网投哪个正规 > 新葡亰-数据 > 解决会话等待产生的系统问题,性能调优

原标题:解决会话等待产生的系统问题,性能调优

浏览次数:74 时间:2019-10-31

一.概述 

  与网络I/O相关的等待的主要是ASYNC_NETWORK_IO,是指当sql server返回数据结果集给客户端的时候,会先将结果集填充到输出缓存里(ouput cache),同时网络层会开始将输出缓存里的数据打包,由客户端接收。如果客户端接收数据包慢,sql server没有地方存放新数据结果时,这时任务进入ASYNC_NETWORK_IO等待状态。

  1. 从实例级别查看ASYNC_NETWORK_IO

   葡京网投哪个正规 1

   平均耗时: 46366950.0/43014737.0=1.077ms, 最大等待时间:~40秒。

  2. 重现ASYNC_NETWORK_IO等待

     为了演示ASYNC_NETWORK_IO 现象,我们需要输出一个大结果集。当sql server内存完全被使用后,大量的数据填充到缓存里,此时sql server没有地方存放新数据结果,进入等待状态。

-- 一次查询100000条数据输出到客户端
SELECT TOP 100000 * FROM PUB_Stock WITH(nolock)

  监听到的会话如下:

  葡京网投哪个正规 2

  使用dbcc inputbuffer 查询64结果如下:

    葡京网投哪个正规 3

  3.分析与解决

    这个等待出现的问题强调以下几点:

    (1) 客户端没有把数据及时取走,调整sqlserver 的配置一般情况下是不是有什么大的帮助。

    (2) 网络层可能是问题的原因。  解决:1是减少对客户端大量数据输出。 2是加大sqlserver 的network packe size,从一定程度上优化网络转输的性能,但会增加内存的开销(建议小于设置小于8kb)。

    network packe size是客户端与sqlserver通信的每个数据包大小有关系。network packe size设置的数据包存放于内存功能组件的connection类别里。默认是4kb设置,输入输出缓存会放在buffer pool里,如果改成了8kb 或更大,输入输出缓存会放在multi-page里 关于内存可查看sql server 内存初探。 设置network packe size 可以由sp_configure控制。客户端应用程序可以覆盖此值如在.net 里配置如下。

Data Source=(local);Initial Catalog=AdventureWorks;"Integrated Security=SSPI;Packet Size=512

    演示将 net work packe size设置成6050字节

USE AdventureWorks2012 ;  
GO  
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'network packet size', 6500 ;  
GO  
RECONFIGURE;  
GO 

   也可以能过界面来配置

  葡京网投哪个正规 4

    (3) 应用程序端性能问题,也会导致sql server里的ASYNC_NETWORK_IO等待。

      sqlserver 的网络层将结果集打包好发向客户端以后,要等到客户端确认收到,才会接着发下一个包。

    (4) 分布式锁

      如果长时间看到ASYNC_NETWORK_IO,同时在sqlserver内部又造成了阻塞,并且该等待持续了很久,就该怀疑是否是分布式的死锁。

  总结:当遇到ASYNC_NETWORK_IO等待,需要检查应用程序自己的健康状况,也要检查应用是否有必要向sql server 申请这么大的结果集返回,一般来讲sqlserver 本身没有什么问题。

背景环境:

等待分类与解决基本流程:

二. 其它网络I/O等待

  这里还有其它几个NET_WAITFOR_PACKET,PROXY_NETWORK_IO,EXTERNAL_SCRIPT_NETWORK_IOF。
  2.1 NET_WAITFOR_PACKET: 在msdn中解释是 网络读取过程中,连接正在等待网络数据包时出现。

    实际级等待如下图所示:
    葡京网投哪个正规 5   
2.2 后面二个proxy_network_io,external_script_network_iof。在生产环境下没有数据。在msdn中也没有找到相应解释。只能通过字面含义去解释。

SQL Server 2005或以上

 

Select * from 某个表,表的数据量约为30万行,在执行语句时通过观察sys.dm_exec_requests中的wait_type列发现是ASYNC_NETWORK_IO等待,在本地MSSQL2012上测试时发现了PREEMPTIVE_OS_WAITFORSINGLEOBJECT等待,在本地2008R2测试时发现只有ASYNC_NETWORK_IO等待。

葡京网投哪个正规 6

可以使用如下语句查询相关等待的等待时间:

 

select 
 session_id,
 db_name(database_id) as "db_name",
 status,
 wait_type,
 wait_time,
 text
from sys.dm_exec_requests cross apply sys.dm_exec_sql_text(sql_handle) 
where session_id>50

步骤1.定位问题

系统等待往往能直观的反映出系统问题。通过一些常见的等待类型,同样可以找到系统瓶颈,结合性能计数器往往定位更准确。

如:系统中存在大量IO类等待,那么可能表示你的磁盘或内存是语句运行缓慢的原因,也是系统的瓶颈所在。

关于网络协议:

常见的等待类型

      • CXPACKET : 当尝试同步查询处理器交换迭代器时出现。如果针对该等待类型的争用成为问题时,可以考虑降低并行度。
      • IO_COMPLETION :   在等待 I/O 操作完成时出现。通常,该等待类型表示非数据页 I/O。
      • PAGEIOLATCH_ : 在任务等待 I/O 请求中缓冲区的闩锁时发生。
      • PAGELATCH_ : 在任务等待不处于 I/O 请求中的缓冲区闩锁时发生。
      • LCK_ :等待闩锁时出现。
      • ASYNC_NETWORK_IO : 当任务被阻止在网络之后时出现在网络写入中。验证客户端是否正在处理来自服务器的数据。 
      • OLEDB :当 SQL Server 调用 Microsoft SQL Native Client OLE DB 访问接口时出现。该等待类型不用于同步。而是用于指示调用 OLE DB 访问接口的持续时间 
      • WRITELOG :等待日志刷新完成时出现。导致日志刷新的常见操作是检查点和事务提交。 

 

 

 

 

 

了解到:shared memory协议开启时,使用本机名登录会优先使用shared memory协议,因此此协议只适用于本地连接。

步骤2.分析

问题与解决

可以通过如下SQL获取所有非系统会话的网络协议使用情况:

CXPACKET 

CXPACKET 这个等待可以简单理解成CPU相关的等待,主要发生在并行计划中。由于并行计划需要协同多个task同时工作,那么“协同”分配等等操作的时候出现的就是这个等待。

如果 CXPACKET 在你系统中是最为严重的等待,这时候一般的表现是你的CPU很高。

葡京网投哪个正规 7

 

解决方案:适当调整并行度

 

 

葡京网投哪个正规 8

 

 

 一般建议系统如果超过32个CPU 那么设置成8或者4,如果系统中都是特别短小且频繁的语句建议设置成1(取消语句并行,要慎重真的符合你的场景才好)

    并行开销的阀值,主要控制SQL优化器何时选用并行计划,建议默认值,此值设置的越小优化器越容易选择并行计划。

    并行度的设置是针对实例级别的设置(2016中可以对单独数据库设置)

select 
 session_id,
 most_recent_session_id,
 net_transport,
 auth_scheme,
 client_net_address,
 client_tcp_port,
 local_net_address,
 local_tcp_port 
from sys.dm_exec_connections

IO类

  IO_COMPLETION和PAGEIOLATCH_和WRITELOG 这三个等待是最为常见的和磁盘相关的等待。他们的不同点是 IO_COMPLETION  主要针对非数据页 I/O ,如备份操作所需的磁盘交互。PAGEIOLATCH_ 是数据页相关的磁盘等待。WRITELOG 是日志相关。

  如果系统中这三个等待是主要等待,说明系统磁盘存在压力或已经成为瓶颈。

  这里用PAGEIOLATCH_ 为例进行说明

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

    PAGEIOLATCH_的相关等待:

 

PAGEIOLATCH_DT

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

PAGEIOLATCH_EX

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

PAGEIOLATCH_KP

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

PAGEIOLATCH_NL

仅供内部使用。

PAGEIOLATCH_SH

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

PAGEIOLATCH_UP

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

     怎么来理解这个官方解释呢? 首先明确一点,操作系统CPU操作的任何数据都是从内存中读取的,也就是说读取数据要经过这样的一条路:

这里的PAGEIOLATCH_ 就是发生在, 磁盘中 ——>  内存中 

  以读取为例:要读取的数据页不在内存中,所以就要去磁盘上读取这部分数据页,去磁盘读取数据的时候就会产生PAGEIOLATCH_的相关等待,如果磁盘压力大,长时间不能反回数据,那么PAGEIOLATCH_的时间也会越长,语句执行的时间也会越长。

葡京网投哪个正规 9

 

注 : 当你的系统出现大量的 PAGEIOLATCH_ 类等待,说明你磁盘可能存在压力(磁盘速度不能满足当前业务需求)或你的内存不够用,不能缓存业务常用数据而经常要与磁盘交互!

 

 

WRITELOG  和磁盘有关的另一个等待状态,正在等待写日志记录,意味着写入速度也明显跟不上。而速度跟不上一般有两种情况:磁盘压力大响应时间长或真的速度不能满足读写需要。

 

葡京网投哪个正规 10

PAGELATCH_ 

PAGELATCH_和 上面讲述的PAGEIOLATCH_  看似很像,但中间少了 IO 这个关键。

磁盘中——>内存中 的等待为PAGEIOLATCH_   而 内存中——> 最终使用 的等待为 PAGELATCH_

 当数据已经在内存中的时候SQL SERVER 想要使用这个数据页就要给这个数据页加锁。

当等待中出现很多PAGELATCH_ 等待,那么可以说明:

  1. SQL Server没有明显的内存和磁盘瓶颈。
  2. 应用程序发来大量的并发语句在修改同一张表格里的记录,而表格架构设计以及用户业务逻辑使得这些修改都集中在同一个页面,或者数量不多的几个页面上。这些页面有的时候也被称为Hot Page。这样的瓶颈通常只会发生在并发用户比较多的、典型的OLTP系统上。
  3. 这种瓶颈是无法通过提高硬件配置解决的,只有通过修改表格设计或者业务逻辑,让修改分散到尽可能多的页面上,才能提高并发性能。

 

TempDB造成的 PAGELATCH_(其实也是一种Hot Page),这里简单的看一个例子:

葡京网投哪个正规 11

 

    系统中存在大量的 PAGELATCH_UP等待那么是什么成为了Hot Page 呢?为什么说和TempDB有关呢?

     葡京网投哪个正规 12

 

     等待资源 “2:X:X: ”开头是TempDB,系统中存在大量且高并发的语句使用临时表和表变量,所以引起TEMPDB瓶颈。请参见:TempDB的诊断和优化。

 

从查询结果可以大致推断出本地SSMS作为一个客户端如果使用TCP/IP协议也是要走网卡的,而且执行结果显示了登录使用的协议以及登录验证方式还有使用的端口号。使用shared memory协议的连接不通过socket通信的方式获取数据,而是直接通过系统总线从共享内存读取。

LCK_ 

 LCK_类型中的所有很多,如果这种等待在系统中大量存在,可以说明,系统语句间的相互阻塞严重。如大家都知道的当你update一张表的时候,你的select会被阻塞直到update完成。这里就不过多介绍场景了,主要看一下解决此类等待的主要方法:

    1. 语句优化,让语句执行的更快,减少等待时间。
    2. 采用批量操作代替循环方式。
    3. 尽量减少事务的长度。
    4. 尝试降低事务隔离级别。
    5. 上述都不能缓解...请选用读写分离。

 

 LCK_类型中包含:(这里不做详细解读了)

LCK_M_RIn_NL

当某任务正在等待获取当前键值上的 NULL 锁以及当前键和上一个键之间的插入范围锁时出现。键上的 NULL 锁是指立即释放的锁。有关锁兼容性矩阵,请参阅 sys.dm_tran_locks

LCK_M_RIn_S

当某任务正在等待获取当前键值上的共享锁以及当前键和上一个键之间的插入范围锁时出现。有关锁兼容性矩阵,请参阅 sys.dm_tran_locks

LCK_M_RIn_U

任务正在等待获取当前键值上的更新锁以及当前键和上一个键之间的插入范围锁。有关锁兼容性矩阵,请参阅sys.dm_tran_locks

LCK_M_RIn_X

当某任务正在等待获取当前键值上的排他锁以及当前键和上一个键之间的插入范围锁时出现。有关锁兼容性矩阵,请参阅 sys.dm_tran_locks

LCK_M_RS_S

当某任务正在等待获取当前键值上的共享锁以及当前键和上一个键之间的共享范围锁时出现。有关锁兼容性矩阵,请参阅 sys.dm_tran_locks

LCK_M_RS_U

当某任务正在等待获取当前键值上的更新锁以及当前键和上一个键之间的更新范围锁时出现。有关锁兼容性矩阵,请参阅 sys.dm_tran_locks

LCK_M_RX_S

当某任务正在等待获取当前键值上的共享锁以及当前键和上一个键之间的排他范围锁时出现。有关锁兼容性矩阵,请参阅 sys.dm_tran_locks

LCK_M_RX_U

当某任务正在等待获取当前键值上的更新锁以及当前键和上一个键之间的排他范围锁时出现。有关锁兼容性矩阵,请参阅 sys.dm_tran_locks

LCK_M_RX_X

当某任务正在等待获取当前键值上的排他锁以及当前键和上一个键之间的排他范围锁时出现。有关锁兼容性矩阵,请参阅 sys.dm_tran_locks

LCK_M_S

当某任务正在等待获取共享锁时出现。有关锁兼容性矩阵,请参阅 sys.dm_tran_locks

LCK_M_SCH_M

当某任务正在等待获取架构修改锁时出现。有关锁兼容性矩阵,请参阅 sys.dm_tran_locks

LCK_M_SCH_S

当某任务正在等待获取架构共享锁时出现。有关锁兼容性矩阵,请参阅 sys.dm_tran_locks

LCK_M_SIU

当某任务正在等待获取共享意向更新锁时出现。有关锁兼容性矩阵,请参阅 sys.dm_tran_locks

LCK_M_SIX

当某任务正在等待获取共享意向排他锁时出现。有关锁兼容性矩阵,请参阅 sys.dm_tran_locks

LCK_M_U

当某任务正在等待获取更新锁时出现。有关锁兼容性矩阵,请参阅 sys.dm_tran_locks

LCK_M_UIX

当某任务正在等待获取更新意向排他锁时出现。有关锁兼容性矩阵,请参阅 sys.dm_tran_locks

LCK_M_X

当某任务正在等待获取排他锁时出现。有关锁兼容性矩阵,请参阅 sys.dm_tran_locks

关于等待事件:

ASYNC_NETWORK_IO 

  此等待状态出现在SQLServer已经把数据准备好,但是网络没有足够的发送速度跟上,所以SQLServer的数据没地方存放。

  1. 出现这种情况一般不是数据库的问题,调整数据库配置不会有大的帮助。
  2. 网络层的瓶颈当然是一个可能的原因:对此要考虑是否真有必要返回那么多数据?
  3. 应用程序端的性能问题,也会导致SQLServer里的ASYNC_NETWORK_IO等待。如果见到了这个类型的等待,就要检查应用程序的健康状况,也要检查应用是否有必要想SQLServer申请这么大的结果集。
  4. 程序返回结果集的方式 。

ASYNC_NETWORK_IO

This wait type is where SQL Server has sent some data to a client through TDS.aspx) and is waiting for the client to acknowledge that is has consumed the data, and can also show up with transaction replication if the Log Reader Agent job is running slowly for some reason.

这个等待类型表示SQL Server正在通过TDS向客户端传送请求的数据,也可能表示事务复制的日志读取代理由于某些原因运作缓慢。

(Books Online description: “Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server.”)

(联机丛书的解释:当任务由于被阻塞于网络时出现,证明客户端正在接收服务端的数据)

Other information:

This wait type is never indicative of a problem with SQL Server, and the vast majority of the time it is nothing to do with the network either (it’s very common to see advice stating that this is a network issue). A simple test for network issues is to test the ping time between the SQL Server and the client/application/web server, and if the ping time is close to the average wait time, then the wait is because of the network (which may just be the normal network latency, not necessarily a problem).

这个等待类型表示并非SQL Server的问题,绝大多数情况下也与网络问题无关(很多时候大家都认为是网络问题),一个简单的测试方式是从客户端ping一下服务端,如果延迟接近sys.dm_exec_requests中wait_time的平均值则证明确实与网络相关(很多时候都只是正常的网络延迟,并不是网络故障)。

There is usually nothing that you can do with your SQL Server code that will affect this wait type. There are a few causes of this on the client side, including:

  • The client code is doing what is known as RBAR (Row-By-Agonizing-Row), where only one row at a time is pulled from the results and processed, instead of caching all the results and then immediately replying to SQL Server and proceeding to process the cached rows.
  • The client code is running on a server that has performance issues, and so the client code is running slowly.
  • The client code is running on a VM on a host that is configured incorrectly or overloaded such that the VM doesn’t get to run properly (i.e. slowly or coscheduling issues).

针对此等待事件一般无需对SQL代码做什么改动,引发此问题的原因基本都是由于来源于客户端,例如:

  。客户端代码使用RBAR方式处理数据集,每次只从结果集拉取一条数据,而不是全部获取完毕后再处理。

  。客户端所在的服务器有某些性能问题,导致客户端运作缓慢。

  。客户端运行在配置错误或者过载的虚拟机上,总之也是服务器本身的问题。

On the SQL Server side, the only possibility I know of for causing this is using MARS (Multiple Active Result Sets) with large result sets.

You can demonstrate this wait type easily by running a query with a large result set through SSMS on the SQL Server itself, with no network involved.

在数据库服务端,就我所知唯一可能的原因就是使用了MARS的大结果集引起的。(其实就是因为结果集太大)

你可以很轻易的通过在数据库服务器上使用本机名登录的方式,运行一个获取大结果集的查询,来验证这个等待事件是否会出现。

Some other things you can try:

  • Look for incorrect NIC settings (e.g. TCP Chimney Offload enabled) with the help of your network/system administrator. Whether some settings should be enabled or not depends on the underlying OS version. See this post for some more details.
  • Consider increasing the TDS packet size (carefully) – see this post for more details.

其他的一些尝试:

  。是否有其他的网络设置错误,联系你的网络管理员修改一些注册表中的网络参数,一些参数在某些OS版本中是否应该被启用参考这里(见如上超链接)。

  。考虑增加TDS的包大小(谨慎一些),参考这里(见如上超链接)。

PREEMPTIVE_OS_WAITFORSINGLEOBJECT

Description:

This wait type is when a thread is calling the Windows WaitForSingleObject.aspx) function for synchronization with an external client process that is communicating using that object.

(Books Online description: N/A --表示联机丛书没有说明)

这个等待事件表示一个线程正在向外部客户端进程同步某个对象的数据,因此出现此种等待。一般此种等待出现在SQL Server 2012及以上的版本,以前用ASYNC_葡京网投哪个正规,NETWORK_IO代替。

Other information:

This wait type is commonly seen in conjunction(同时出现) with ASYNC_NETWORK_IO, depending on the network transport used to communicate with the client, so to troubleshoot, follow the same steps as for ASYNC_葡京正网网投,NETWORK_IO.

Note that when a thread calls out to Windows, the thread changes from non-preemptive (SQL Server controls the thread) to preemptive (Windows controls the thread) mode. The thread’s state will be listed as RUNNING, as SQL Server doesn’t know what Windows is doing with the thread.

这种等待事件一般与ASYNC_NETWORK_IO等待事件一起出现,取决于连接所使用的网络传输类型,因此解决步骤参考ASYNC_NETWORK_IO的解决方式。

注意,当一个连接线程被从SQL Server控制(非抢占式)到被Windows控制(抢占式)的后,线程的状态就会变为running,此时SQL Server并不知道windows在对此线程做什么。

关于抢占式与非抢占式的区别,参考官网博客中关SQL OS与Windows OS对线程的不同处理方式的介绍。

 

本文由葡京网投哪个正规发布于新葡亰-数据,转载请注明出处:解决会话等待产生的系统问题,性能调优

关键词:

上一篇:MySql学习笔记,数据表的基本操作

下一篇:SQLite数据库中的存储类型汇总,Server中常见的数据类型