One common cause is the use of cursors on a table with many records. Other causes of timeouts are infinite loops, statistics which haven't been updated, hardware that is not sufficient for the size of your database, deadlocks, blocking, poor query design, poor index design, network bottlenecks, etc. This is a complex subject and will require advanced skills on your part to search for and find the answers to your problems. I recently got a very good book on the this subject which you might want to get a copy of as it will tell you far more than I ever could in a forum such as this.
It's called - SQL Server Query Performance Tuning Distilled by Sajal Dam. Really good stuff in here, I've learned a lot of new things just in the couple of days I've had it. Excellent discussions of how to analyze the execution plan and what counters to look at in system monitor and how to find the slow running queries, etc. It's well worth the money.
Since you seem to have a particular stored procedure you know is giving you trouble, you might post it here and we could take a crack at tuning it. But of course if the problem lies outside the code itself, then it is very difficult for us to find the problem. However, I just fixed the code for one of our web programmers and took the execution time down from well over 30 seconds to under a second so it can be done and looking at the particular stored procedure or query is where most of us start to search for the cause of a problem like this.
