I have a query regarding a website that I am running written in classic ASP, which was attached to an MS SQL 2000 database (and worked fine) but is now attached to an MS SQL 2008 database and keeps timing out.
The thing is that I am testing it against the same wildcard searches and getting varied responces, in as much as at certain times the results are returned and browser sent to the results page in under two seconds, whereas on other occasions the same search takes more than 60 seconds and the server times the page out. (I know how to increase the script timeout value, but don't want the query to take so long)
The situation is that the website is on a dedicated server (2 x 1.8 GHZ Intel Core 2 Duo) 2.5GB RAM, with a 10240Kbps data pipe, the SQL 2008 database is on a shared SQL 2008 Server.
I know that some of the problem here could be the way the database and queries have been designed, and will look at this at a later stage (the site is live so can't take it down) but as the issue isn't constant, I was curious as to whether the issue could be affected by either -
our dedicated server size -
the fact that we are on a shared SQL server?
Please can I have your thoughts.
Many many thanks