SQL Server timeout diagnosis

Connection timeout

For connection timeout, you can first check the LoginTimers type error in Connectivity Ring Buffer to analyze, and then use the packet grabbing tool network monitor if you want more detailed information.

For specific methods and statements, please 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 timeout

For the query timeout, for the version below SQL Server 2012, add "Attention" under "Errors and Warnings" on the basis of Profiler's TSQL_Duration template, and find the corresponding statement according to the captured Attention combined with context; for SQL Server 2012 and above, directly use extended events to monitor sqlserver.attention events and directly output sql_text.

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 duration > 5000) 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 * /

Tuning suggestion

For connection time-consuming, of course, it is necessary to find the specific reason, whether it is a network problem or a verification problem; for query timeout, it is mostly caused by statement performance problems, such as blocking, not using a reasonable index, too much output data and so on. For temporary problem resolution, you can increase the configuration value in the connection configuration or in the statement-level parameter property in the program. Of course, we should find out the root cause of the problem and solve it in time.