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.
