表的数据量约为30万行

背景环境:

SQL
Server 2005或以上

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

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

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

关于网络协议:

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

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

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

澳门太阳集团城网址 1

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

关于等待事件:

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方式处理数据集,每次只从结果集拉取一条数据,而不是全部获取完毕后再处理。

表的数据量约为30万行。  。客户端所在的服务器有某些性能问题,导致客户端运作缓慢。

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

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.

表的数据量约为30万行。在数据库服务端,就我所知唯一可能的原因就是使用了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的包大小(谨慎一些),参考这里(见如上超链接)。

表的数据量约为30万行。PREEMPTIVE_OS_WAITFORSINGLEOBJECT

Description:

表的数据量约为30万行。表的数据量约为30万行。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对线程的不同处理方式的介绍。

 

You may also like...

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图