Users experiencing Timeout expired errors

spudsoftware
spudsoftware used Ask the Experts™
on
We have an application running in classic asp, sql server 2005 database, server houses both the database and application.  Started out small, but now there are many queries and about 150 users.  Users have began to experience Timeout expired errors when trying to access pages.  The errors are occurring at irregular intervals, and not always on the same query/page.
Im pulling my hair out trying to monitor the server, check database indexes, etc.  But nothing seems to help or point me in the right direction.  The application has been in production for over a year, these timeouts starting occurring on Monday.  If anyone can assist and point me in the right direction that would be great.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
  1. Have your indexes and statistics been rebuilt recently?  I would schedule them for a weekly rebuild -- starting tonight.
  2. Have you monitored the SQL Server using Profiler to indentify long-running queries?  Anything taking over 15 seconds (15000 ms) is a potential timeout waiting to happen when the load increases.  If you see a query repeated that is long, pick that one and improve it -- either the indexes it uses or the query itself.
Not sure how to improve the query?  Post it and we'll follow up.

Author

Commented:
Thanks - I have the tables indexed and rebuild the indexes nightly.  

I did run a trace yesterday and just now implemented some additional indexes that the tuning advisor recommended.  I hope that does the trick...
If not, let me know ... and I'll see what I can suggest.

Author

Commented:
I found one huge query that we had added a subquery to a week or so ago - that was the problem.  Thanks for your help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial