Troubleshoot on timeout query

We have one request to trace on timeout issue on applicaition.
I setup profiler to trace which query has timeout issue


Error and Warnings
- Attention


Stored Procedures
- RPC: Starting
- SP: Starting
- SP: StmtStarting

TSQL
- SQL: BatchStarting
- SQL: StmtStarting


Setting sample table and data
Create table member3
(member_no int,
lastName varchar(20),
firstName varchar(20));
insert into member3 values (1,'tan','tanlast'),(100,'lee','leelast');

Setting timeout environment
begin tran
update member3
set lastname='me'
where member_no=1

Open a second Microsoft SQL Server Management Studio
In connect to server box, Click on Option and change Execution time-out from 0 (unlimited) to 2 seconds
Press connect



In Query Window, type this,

select * from member3
where member_no=100

After run for 2 second, SQL will give the below message

Msg -2, Level 11, State 0, Line 0
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

From SQL Profiler, look for EventClass=Attention which highlight with red color
You can do this to find “Attention”

CTRL+F, In “Find what” column, keying “Attention”



After get the EventClass with “Attention”, look for the SPID. In this example, SPID for “Attention” = 58
Look backward for SPID=58, you will know the above query is face timeout



In a real production environment, lot of the query is running and we difficult to find the backward query. We can do in this way.

Save the trace log in trace file
Open the trace file that you save just now

Filter the SPID from File-> Properties -> Events Selection -> Column Filter -> SPID -> Equals -> Keying in 58 and press ok, ok




The SQL profiler will only show trace with SPID 58, which easy us to search which query cause timeout.