SQL Server time-out diagnostics

Connection timed out

For connection timeouts, you can first look at the Logintimers type errors in the connectivity Ring buffer and, if you want to get more detailed information, then use the Grab Kit tool Network Monitor.

Specific methods and statements can refer to the following articles:

https://blogs.msdn.microsoft.com/apgcdsd/2011/11/20/ring-buffersql-server-2008/

http://ultrasql.blog.51cto.com/9591438/1583446

Query timed out

For query timeouts, for versions below SQL Server 2012, using the profiler's tsql_duration template, add "Attention" under "Errors and Warnings", According to the captured attention context to find the corresponding statement; for SQL Server 2012 and later, use extended events to monitor the Sqlserver.attention event directly and output Sql_text directly.

The following is the XE script:

use tempdb   go    if exists  (select * from  sys.objects where name =  ' sqlws_xev_attention ')          drop view sqlws_xev_attention    go    create  view sqlws_xev_attention as    with xevents  (Event_data)      as     (        select event.query (‘.‘)  as event_data from               ((select cast  (xest.target_data as xml)  as target_data                     from  sys.dm_xe_sessions as xes                     inner join sys.dm_xe_session_targets as xest on  ( xes.address = xest.event_session_address)                      where xes.name =  ' Sqlws_ Xevents_attention '  and xest.target_name =  ' ring_buffer ')  as td                 cross apply target_ data.nodes  ('//event[@name = "attention"] ')  as x  (event))     )      select event_data.value  (' (event/@name) [1] ',  ' varchar (max) ')  as  event_name,        event_data.value  (' (event/@timestamp) [1] ',   ' datetime ')  as event_timestamp,        event_ data.value  (' (event/data[@name = "duration"]/value) [1] ',  ' bigint ')  as [duration],        event_ data.value  (' (event/data[@name = "request_id"]/value) [1] ',  ' int ')  as [request_id],         event_data.value  (' (event/action[@name = "Client_app_name"]/value ) [1] ',  ' nvarchar (max) ')  as action_client_app_name,         event_data.value  (' (event/action[@name = "Client_hostname"]/value) [1] ',  ' nvarchar (max) ')  as  action_client_hostname,        event_data.value  (' (event/ action[@name = "database_name"]/value) [1] ',  ' nvarchar (max) ')  as action_database_name,         event_data.value  (' (event/action[@name = "Nt_username"]/value) [1] ',   ' nvarchar (max) ')  as action_nt_username,        event_ data.value  (' (event/action[@name = "session_id"]/value) [1] ',  ' int ')  as action_session_id,         event_data.value  (' (event/action[@name = "Sql_text"]/value) [1] ',  ' nvarchar (max ) ')  as action_sql_text        from xevents     go    if exists  (Select * from sys.server_event_ sessions where name =  ' sqlws_xevents_attention ')          drop event session sqlws_xevents_attention on server    go     create event session sqlws_xevents_attention on server         add event sqlserver.attention  (action  ( Sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.nt_ username, sqlserver.session_id,  sqlserver.sql_text)  where counter <= 100 /* client_app_name !=   ' Microsoft sql server management studio - query '  and counter  <= 100 */)         add target  package0.ring_buffer         with  (Event_retention_mode  = allow_single_event_loss, memory_partition_mode = none)     go     /*    alter event session sqlws_xevents_attention  on server state = start    go    --execute  your workload    with xevents  (Event_Name)     as      (        select event.value  (' (@name ) [1] ',  ' varchar (max) ')  as event_name            from  ((select cast   (Xest.target_data as xml)  as target_data                     from sys.dm_xe_sessions  as xes                     inner join sys.dm_xe_session_targets as xest on  ( xes.address = xest.event_session_address)                      where xes.name =  ' Sqlws_ Xevents_attention '  and xest.target_name =  ' ring_buffer ')  as td                 cross apply target_ data.nodes  ('//event[@name = 'Attention "]  as x  (event))     )     select event_ Name, count (*)  from xevents        group by  Event_name    go    select * from sqlws_xev_attention     go    alter event session sqlws_xevents_attention  on server state = stop    go    */

This article is from the SQL Server deep Dive blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1864004

SQL Server time-out diagnostics