I had a support issue from a client today which turned out to be an interesting one I hadn't come across before so I thought I would share. Thankfully, others had so once I had identified exactly what the issue was google and stack overflow were able to quickly assist.
The scenario that initially bemused me was that fact that running a complex search sproc from SQL Server Management Studio was returning in a couple of seconds but exactly the same parameters fired into the sproc from ASP.NET caused the web app to timeout. Earlier I had even tried extending the default commandTimeout from 30secs to 4mins without success so SQL Server had really got it's knickers in a twist. Further the issue just appeared one day and I also couldn't replicate in the staging environment, which had similar data volumes.
It turns out others had hit the issue before, e.g.
http://stackoverflow.com/questions/9974/query-times-out-from-web-app-but-runs-fine-from-management-studio
It turns out the ADO.NET and Management studio connection/ user contexts differed and in my case, as per the link above, the explicit setting of the arithabort property brought the performance back to expected levels. Further, adding this in one sproc also fixed the issue with other reporting sprocs of the solution. Go figure.