I have a Stored Procedure that is called through VB6 for a report. The Reports were working great until yesterday. Now all of the reports that use that sp are timing out. The default time is set to 60. I have adjusted this to 1000 and the report finally displays. When I login to sql using Management Studio I can run this sp and it takes less then a second. All data is being transferred correctly. I am at a loss. Nothing in the Code has been changed.
Any ideas will be greatly appreciated.


Found my problem. I was passing 4 params. I found an article on Parameter Sniffers in 2005 SQL. Once I added another set or params with an existing value it all worked great.

@param1 int,
@param2 int,
@param3 int,
@param4 int,
@paramsniff1 int = 0,
@paramsniff2 int = 0,
@paramsniff3 int = 0,
@paramsniff4 int = 0

set paramsniff1 = @param1 
commented: Awesome Followup +9