Conditional timeout issue when executing SP

In the past week, I've been running into a very unusual timeout issue
with SQL 2005 SP2 and the execution of a single, specific stored
procedure. The stored procedure executes in under 1 second and uses
all appropriate indexes when it's run from the SQL Enterprise Manager,
but when the query is executed as part of the loading process of the
website it's used in, the data doesn't load and a SQL timeout error is
encountered. In both circumstances, the query originates from the same
machine.
In the website, the procedure that is timing out isn't the first query
in the loading process to access that database or set of tables. It
seems that the problem is just something with that query itself. There
really isn't anything too out of the ordinary about the query either;
it's just a single SELECT statement with a few joins, two subqueries
(one of which contains a subquery of its own) in the SELECT and one
subquery in the WHERE, and
I tried restarting IIS on the machine just for kicks, but it doesn't
solve the problem. I also tried copying the website application to
another machine, but I still receive the same timeout error on the
same query even when the query continues to execute just fine from the
Enterprise Manager environment on the first machine. To make matters
even more puzzling, the query / stored procedure _usually_ loads
without fuss when I pass in a different value for the single parameter
it takes; sometimes though, the procedure never loads within the
website application even when I try all of the different reasonable
values for that parameter.
The only way to fix the problem that I've found is to either restart
the SQL service or change the number of threads that the service is
using (which seems to issue a "soft restart" of sorts to SQL itself).
Once restarted, the website application loads fine and runs fine for a
few days. However, once a few days have passed, the SQL server again
starts giving timeouts for that query. The timeout errors that _do_
occur at this point are usually given when the single parameter is
different that the last parameter that gave the timeout errors before
the last time the server was restarted. In all reality, everything
just seems so random, and so it's hard to pin down any more details
for sure.
Like I said, this issue has only popped up in the past week or so and
had been running fine for the 6+ months previous. Has anyone else
encountered this issue before or otherwise have any suggestions for
how I can fix the problem? I'd really appreciate anything at this
point because I'm running out of ideas.My guess is that you have parameter sniffing issues. I suggest you spend an hour or two with below.
It is worth your time.
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"amattie" <amattie@.gmail.com> wrote in message
news:57616074-99f3-4c49-9b49-babbddfe11d9@.e23g2000prf.googlegroups.com...
> In the past week, I've been running into a very unusual timeout issue
> with SQL 2005 SP2 and the execution of a single, specific stored
> procedure. The stored procedure executes in under 1 second and uses
> all appropriate indexes when it's run from the SQL Enterprise Manager,
> but when the query is executed as part of the loading process of the
> website it's used in, the data doesn't load and a SQL timeout error is
> encountered. In both circumstances, the query originates from the same
> machine.
> In the website, the procedure that is timing out isn't the first query
> in the loading process to access that database or set of tables. It
> seems that the problem is just something with that query itself. There
> really isn't anything too out of the ordinary about the query either;
> it's just a single SELECT statement with a few joins, two subqueries
> (one of which contains a subquery of its own) in the SELECT and one
> subquery in the WHERE, and
> I tried restarting IIS on the machine just for kicks, but it doesn't
> solve the problem. I also tried copying the website application to
> another machine, but I still receive the same timeout error on the
> same query even when the query continues to execute just fine from the
> Enterprise Manager environment on the first machine. To make matters
> even more puzzling, the query / stored procedure _usually_ loads
> without fuss when I pass in a different value for the single parameter
> it takes; sometimes though, the procedure never loads within the
> website application even when I try all of the different reasonable
> values for that parameter.
> The only way to fix the problem that I've found is to either restart
> the SQL service or change the number of threads that the service is
> using (which seems to issue a "soft restart" of sorts to SQL itself).
> Once restarted, the website application loads fine and runs fine for a
> few days. However, once a few days have passed, the SQL server again
> starts giving timeouts for that query. The timeout errors that _do_
> occur at this point are usually given when the single parameter is
> different that the last parameter that gave the timeout errors before
> the last time the server was restarted. In all reality, everything
> just seems so random, and so it's hard to pin down any more details
> for sure.
> Like I said, this issue has only popped up in the past week or so and
> had been running fine for the 6+ months previous. Has anyone else
> encountered this issue before or otherwise have any suggestions for
> how I can fix the problem? I'd really appreciate anything at this
> point because I'm running out of ideas.