Procedure Time out

Running SQL 2005 SP2, 32 bit, on Win2K3 with 16GB RAM.
I have a procedure that contains a large query composed of 7 inner joins, one
outer join, and one of the inner joins is based off a derived table. Sorry I
cannot post the query due to sensitive info.
During a 3 hour period, it was called about 160 times and it timed out about
100 times.
In running traces, the explain plan was identical when it timed out and when
it did not time out. There were no scans, everything was a clustered index
seek or an index seek with nested loops. The reads during a timeout episode
or a non-timeout episode were all in the same range.
I ran deadlock traces and no deadlocking appeared.
I ran blocking procedures and no extended blocking was occurring.
I was wondering if this fits a potential parameter sniffing description? Or
perhaps there is some corrupt data that I need to look for. Any other ideas?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200801/1
If the plans were the same it is not a parameter sniffing deal. My guess is
that this takes a while to run anyway and is not that far under the limit
for a timeout to begin with. Then a little bit of blocking or heavy resource
usage can push it over the line. Do you call the sp with the owner?
dbo.YourSP
or just YourSP? If not you can get serialization of the calls and if it
takes a while to recompile (which it probably does) that can all play a
part. If you mark that sp for recompile and then execute it how long does it
take to complete normally?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:7dae87b7c2644@.uwe...
> Running SQL 2005 SP2, 32 bit, on Win2K3 with 16GB RAM.
> I have a procedure that contains a large query composed of 7 inner joins,
> one
> outer join, and one of the inner joins is based off a derived table. Sorry
> I
> cannot post the query due to sensitive info.
> During a 3 hour period, it was called about 160 times and it timed out
> about
> 100 times.
> In running traces, the explain plan was identical when it timed out and
> when
> it did not time out. There were no scans, everything was a clustered index
> seek or an index seek with nested loops. The reads during a timeout
> episode
> or a non-timeout episode were all in the same range.
> I ran deadlock traces and no deadlocking appeared.
> I ran blocking procedures and no extended blocking was occurring.
> I was wondering if this fits a potential parameter sniffing description?
> Or
> perhaps there is some corrupt data that I need to look for. Any other
> ideas?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200801/1
>