Key Symptoms :
Stored procedure runs fine in under a second on the server.
Same parameters in the .NET application cause a timeout however more peculiarly, the timeout started occurring after a previous successful execution and played this way for a while and now occurs during the first one.
The underlying data has not grown or changed to affect the complexity of the operation.
The stored procedure worked fine in the .NET application and then stopped working without any modification to the stored procedure. It continues to work fine on the server completing in under a second.
The stored procedure essentially builds a short list of integers into a temporary table. This process involves 2 very small tables (<500 records) and happens quickly. This list is then used to do a more complex cross database select which seems to be the sticking point. (removing this command allows the stored procedure to run fine albeit without results)
Keep in mind this stored procedure did work on the .NET client for a while.
Now when I paste in an earlier version of the stored procedure there is no problem at all. The difference between the older and newer version was in the way the list of integers is built, the final select command is the same and the list of integers in the temporary table is the same list of integers as in the not working version.
I don't think my problem is with my stored procedure rather that some how it is executing differently from the server as opposed to the client.
Things I have tried :
Restarted SQL server.
Restarted Client development machine.
Any other suggestions would be appreciated!