Requests中出现大量ASYNC_NETWORK_IO等待

七夕活动，网页显示异常：
504 Gateway Time-out The server didn't respond in time.
开发询问数据库是否正常，当时正连接在实例上查询数据，感觉响应确实慢(敲一个命令，要几秒才显示出来)。只分配一个SQL Server身份验证的登录名
1、查看ERRORLOG是否有异常信息

xp_readerrorlog 0,1,null,null,'20170828','20170829','desc'

2、查看连接信息，是否有阻塞、等待类型、连接状态

select spid,kpid,blocked,waittime,waittype,waitresource,dbid,last_batch,status,open_tran 
from sys.sysprocesses where spid>50

在第2步中发现很多waittime>0的连接，且其等待类型全部是ASYNC_NETWORK_IO
3、查看正在执行的语句

SELECT er.session_id
    ,DB_NAME(sp.dbid) [Database]
    ,er.start_time
    ,er.status
    ,er.wait_type
    ,er.wait_time
    ,er.command
    ,er.transaction_isolation_level
    ,er.total_elapsed_time/1000 [total_elapsed_time(s)]
    ,er.estimated_completion_time/1000 [estimated_completion_time(s)]
    ,er.percent_complete
    ,[SQLQuery] = SUBSTRING(qt.Text, (er.statement_start_offset / 2) + 1, ((
            CASE
                WHEN er.statement_end_offset = - 1
                    THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
                ELSE er.statement_end_offset
                END - er.statement_start_offset
            ) / 2) + 1)
    ,[BatchQuery] = qt.Text
    ,Program = program_name 
    ,sp.Hostname
    ,sp.loginame
    ,sp.nt_username
    ,sp.nt_domain
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE er.session_id > 50

View Code

返回100+行ASYNC_NETWORK_IO等待信息(数量持续增加)，它们的语句全部是

(@0 nvarchar(4000),@1 nvarchar(255) output,@2 nvarchar(255) output,@3 nvarchar(255) output,@4 nvarchar(255) output)
SELECT [SevenDayUserInfo].[OpenId], [SevenDayUserInfo].[WxName], [SevenDayUserInfo].[WxHeadUrl]
, [SevenDayUserInfo].[UserId], [SevenDayUserInfo].[UserName], [SevenDayUserInfo].[UserNickName]
, [SevenDayUserInfo].[TotalDice], [SevenDayUserInfo].[TakeOutDice], [SevenDayUserInfo].[SurplusDice]
, [SevenDayUserInfo].[IsBind], [SevenDayUserInfo].[IsSubscribe], [SevenDayUserInfo].[DiceSeat]
, [SevenDayUserInfo].[CreateTime], [SevenDayUserInfo].[Count] FROM [SevenDayUserInfo] 
exec First_SevenDay @0,@1 output,@2 output,@3 output,@4 output

View Code

问题的关键就是执行存储过程的前面有一条返回全表数据的语句！反馈给开发，核实是程序代码封装有误(⊙_⊙)

ASYNC_NETWORK_IO
This wait type is where SQL Server has sent some data to a client through TDS 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.

转载于:https://www.cnblogs.com/Uest/p/7444846.html