Ive been running a ms sql 2000 database on a 1gz, 512mb ram machine for several months with no real problem.
However lately its been taking upwards of 2 seconds just to connect...
Im on windows 2003 with iis 6 and connecting like so:
SET MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open "Provider=SQLOLEDB.1;UID=x;Password=x;Initial Catalog=x;Data Source=127.0.0.1"
Most queries are opening in a matter of milliseconds, how ever the occasional one will take upto a second to complete. The queries are very simple update and select statements. (UPDATE Stats SET Page_Views = Page_Views + 1, Page_Views_Daily = Page_Views_Daily + 1) etc...
Doesn't sound a like a server (IIS) problem, sounds more like a coding deficiency or a slow connection between the access database server and the web application server assuming they're on different servers.
During execution of slow queries can take help of PROFILER and submit to Index tuning wizard for index recommendations.
Keeping IIS & SQL On same machine might have performance issues.
Also collect Hardware & SQL counters using PERFMON and I suspect this might encountering Paging problems.
The best solution to your paging problem is to either add more RAM to your server, or even better yet, use two servers, each with an appropriate amount of physical RAM.
If your database should get bigger, or your website busier, you may have to adjust the maximum amount of RAM you have assigned SQL Server again because of changed circumstances. This is one of the disadvantages of not using SQL Server dynamic memory allocation.
If you do not have indexes defined on the columns involved in your joins, then that should be the first thing you do to try to increase execution speed. Indexes would not make a difference on small tables, but as the number of records grows they become critical to query efficient.
If it's not practically useful, then it's practically useless.