September 21, 2009 at 8:33 am
Hello everybody,
I have this stored procedure about 200 lines long that is being called by a VB program through an ADO command object.
The stored procedure calls other SPs which in turn might also call others (there is a 3 level nesting, but nothing really complicated). On top of that it does some simple insert/updates as well.
Sometimes the stored procedure returns a timeout message (Query time out expired) and I can't find out which part of it has the problem or if the problem originates in a nested sp.
i have tried to run Profiler but I can't pinpoint the source, nor do I see anything that is error-like in my trace.
I could of course increase the timeout setting for the query to a higher value but that wouldn't solve my problem - it would just put it away for a while.
I'm pretty confident that I can fix it IF I can find it :-).
The db is an SQL server 2005 express at a remote client location (I have no access to the actual production machine other than through the SSMS). The tables are not large - about 30000 records each all updated with their primary key (which is also clustered) and the timestamp column (used for optimistic concurrency by the Application)
There are about 30 users online.
Any suggestion of how I could find the guilty query or which trace events I should use in the Profiler? (I am profiling through a Developer Edition 2005.)
September 21, 2009 at 8:52 am
First you should probably be tracing statement completion in profiler and limit the trace to a single SPID so you don't have a lot of extraneous "noise". Also, the timeout isn't a DB problem it is a client problem so you won't see a timeout in the trace. The client decides how long it will wait before calling it quits..
You can also write a timing routine into your sproc. Capture start time before calling the sub sprocs and capture the current time after and then store that somewhere..
I have done this numerous times, especially for sprocs that are complex and like 1-2000 lines of code that I need to be able to calculate internal metrics for.
CEWII
September 21, 2009 at 9:08 am
I understand that the timeout thing is a client issue because the client decides when to timeout,
but I still need to find the T-sql part that actually takes that long.
what you propose should work... I wil try it and let you know.
thank you
September 21, 2009 at 9:38 am
I would love to hear how it goes.
CEWII
September 21, 2009 at 9:54 am
🙁
the sproc runs in a transaction so trying to save start/end time + other information to a log table in the DB gets rolledback when the query times out......
remember I have no access to the server's filesystem to save to a text file or something.....
September 21, 2009 at 12:56 pm
Assuming this is a multi-statement query, you could try capturing the Statement completion event within a trace. It's going to return an awful lot of data, so you need to be careful with it, but you should be able to see what the last successful statement within the proc was.
Another option is to pull the execution plan out of the plan cache with a DMV. You won't be able to see specifics as to which statement is running the longest, but you'll be able to look at execution plans and see which one looks the worst.
That's a couple of options that ought to move you closer.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 21, 2009 at 1:06 pm
Table variables... Not subject to transaction.. This is how I've handled that problem in the past..
CEWII
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply