Basically this means that your SQL server is fast and it has finished the query and waits to deliver the resultset to the application.

This means, that either the network or application is to slow compared to the datasets you are sending.

The last part is crucial, because it does not mean that you should change your network (it could, but it is not where we start looking).

What to do

There are many ways to solve this, from examine the network, changing server properties to examine what queries are executed with a high wait.

All of these approaces are great, but I would recommend starting with an extended event to find the queries with a high wait.

When they are found, then you could see if you need all the rows and columns or it could be rewritten to send fewer data to the application.

If that does not help, then you could look at the network m server properties and so on. But it must start at figuring out what queries that are causing the wait.

To create an extended event and start it, you could use the following code (you might need to change name and filepaths:

    ADD EVENT sqlos.wait_info
                ACTION (    sqlserver.sql_text
                            , sqlserver.client_app_name
                            , sqlserver.username
                            , sqlserver.client_hostname
                            , sqlserver.session_id
                            , sqlserver.session_nt_username
                            , sqlserver.database_id
                            , sqlserver.plan_handle
                where wait_type=99
                --and total_duration>500000
    add target package0.asynchronous_file_target
        (set filename = N'd:\trace\AsyncNetworkIO.xml'
        , metadatafile= N'd:\trace\AsyncNetworkIOMetaData.xml'

alter event session [ASYNC_NETWORK_IO] on server state=START

When you are finished collecting data, then you should stop the extended event

alter event session [ASYNC_NETWORK_IO] on server state=STOP

Then you can read the data from the Extended event

select DATA as xmldata
        , Data.value('(//event/@timestamp)[1]', 'datetime')            as executionTime
        , Data.value('(/event/data[@name="wait_type"]/text)[1]', 'varchar(max)')            as Wait_type
        , Data.value('(/event/data[@name="opcode"]/text)[1]', 'varchar(max)')                as operation
        , Data.value('(/event/action[@name="client_hostname"]/value)[1]', 'varchar(max)')    as ClientHost
        , Data.value('(/event/action[@name="client_app_name"]/value)[1]', 'varchar(max)')    as AppName
        , Data.value('(/event/action[@name="username"]/value)[1]', 'varchar(max)')            as userName
        , Data.value('(/event/action[@name="database_id"]/value)[1]', 'int')                as dbID
        , db_name(Data.value('(/event/action[@name="database_id"]/value)[1]', 'int'))        as dbName
        , Data.value('(/event/action[@name="sql_text"]/value)[1]', 'varchar(max)')            as command
        , 1.*Data.value('(/event/data[@name="total_duration"]/value)[1]', 'bigint')/1000000    as total_seconds
        , 1.*Data.value('(/event/data[@name="duration"]/value)[1]', 'bigint')/1000000        as duration_seconds
        , Data.value('(/event/data[@name="completed_count"]/value)[1]', 'bigint')    as CompletedCount
from (
select CONVERT(xml, event_data) as data
from sys.fn_xe_file_target_read_file(
        , 'd:\trace\AsyncNetworkIOMetaData*.xml'
        , null
        , null
     ) entries
order by duration_seconds desc

And finally you can drop the extended event if you want to

drop event session [ASYNC_NETWORK_IO] on server





Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request


Please sign in to leave a comment.