Very Strange Database Problem Regarding Stored Proc

[Darrell Elliott]

I have two database servers and two IIS web servers, one database and one web server are in a test environment and the other two are in production. I have a stored procedure that looks up a person by potentially all sorts of criteria and the stored procedure works as it should in test and in production, well, most of the time in production. Same stored procedure and the same code.

What happens on the production server is this: you can use all of the other criteria too look up the results and they work immediately, except one parameter of the criteria. Sometimes when you search by that criteria, the error that I receive is a SqlClient error from IIS that the script timed out. I have tested the stored procedure directly on the production server, with the criteria in question, and I get the results within two seconds. But if I run the process in my code, it times out.

What I have done, that doesn't make any sense to me is this: When this isn't working, I copy the stored procedure from the test server over to the production server and THEN, everything works as it should.

As far as I can tell, both database servers are set up the same with all of the same settings. If anyone can lend a thought, that would be very appreciated. I can also post a copy of the stored procedure here if that would be of any help.

Thanks in advance,

[rpresser]

I would recommend updating statistics and/or rebuilding indexes.

[Darrell Elliott]

Post by rpresser
I would recommend updating statistics and/or rebuilding indexes.

Thanks for the tip on that. I'm running sp_updatestats on the schema. I know that we rebuild indexes once a week on Thursdays, so I will give this a try and see what happens. I will post back no matter if this works or doesn't.

Thanks again.

[Erland Sommarskog]

Post by Darrell Elliott
I have two database servers and two IIS web servers, one database and
one web server are in a test environment and the other two are in
production. I have a stored procedure that looks up a person by
potentially all sorts of criteria and the stored procedure works as it
should in test and in production, well, most of the time in production.
Same stored procedure and the same code.
What happens on the production server is this: you can use all of the
other criteria too look up the results and they work immediately, except
one parameter of the criteria. Sometimes when you search by that
criteria, the error that I receive is a SqlClient error from IIS that
the script timed out. I have tested the stored procedure directly on the
production server, with the criteria in question, and I get the results
within two seconds. But if I run the process in my code, it times out.
What I have done, that doesn't make any sense to me is this: When this
isn't working, I copy the stored procedure from the test server over to
the production server and THEN, everything works as it should.
As far as I can tell, both database servers are set up the same with all
of the same settings. If anyone can lend a thought, that would be very
appreciated. I can also post a copy of the stored procedure here if that
would be of any help.

I would have helped to see the code. As well as the output from "SELECT
@@version".

In the meanwhile, there are two articles on my web site that you may
find helpful:

1) This article describes how to tackle this kind of searches:
http://www.sommarskog.se/dyn-search-2008.html

2) This article explains some of the possible reasons for the differences
you are seeing and gives some performance tips in general:
http://www.sommarskog.se/query-plan-mysteries.html

If you want me to shot it in the dark, I say add OPTION (RECOMPILE)
after the query.