Over the past Christmas holiday week, when the website I work on was experiencing very low traffic, we saw several Request timed out exceptions (one on each day >> 12/26, 12/28, 12/29, and 12/30) on several pages that require user authentication. We rarely saw Request timed out exceptions prior to this very low traffic week.
We believe the timeouts were due to the database that it uses being "spun down" on the SQL Server and taking longer to spin up when a request came in.
There are 2 databases on the SQL Server (SQL Server 2005), one which is specifically for this application and the other for the public facing website and for authentication; so in the case where users were not logged into the application (which definitely could have been for several hours at a time over Christmas week) the application database probably received no requests. We think at this point SQL Server reallocated resources to the other database and then when a request came in, extra time was needed to spin up the application database and the timeout occurred.
Is there a way to tell SQL Server to give a minimum amount of resources to a database at all times?
Best Answer
Make sure the "Auto-Close" property of the database is set to FALSE.
If this is on, then during times of low traffic, it basically takes the db offline. The first query against it attempts to bring it back online, but depending on the size, that might take awhile.
EDIT: If that's not the case, then a few other things to check:
Beyond this, the only thing I can think of (short of upgrading to SQL 2008 Enterprise Edition with Resource Governer) is to optimize your query so that it performs optimally even when the table's aren't cached.
All the normal performance tuning recommendations would of course apply, including proper indexing (preferably covering indexes), etc.