We are calling store procedure from vb.net client following are the observation.
1) sqlserver 2005 takes huge amount of time (more than 1 hour) to execute procedure. The procedure has select statement using join statement. (Generally this procedure executes and populate data in 5 seconds when it is called from .net client.)
2) If same procedure is executed from sql server management studio, procedure executes and populate data in 5 seconds (works as per expectation)
3) If we recompile procedure (i.e. open procedure in alter mode in management studio and click on execute button) , the procedure executes and populate data as per expectation.
4) This is intermittent issue on production machine. Most of the time procedure is executed and populate data as per expectation when called from vb.net client. But it does not give expected outout in some scenario. The frequency for occurring the issue
is once in 15 days and there is no pattern in which the issue is occurring.
Can any one help to resolve the issue.
Thanks in advance!!!
View Complete Post