sql 2008 express slow on first request? Goes to sleep?

Question

I have read somewhere about SQL express running as a user instance or something.. and as such, the instance/service "goes to sleep" if not used for x time.. (don't know the actual timings etc)

So the scenario is:

1. If my website (in this case) doesn't have anyone using it for "a few hours", SQL Express "seems" to go to sleep.

2. The next time someone comes along (after the pause for however long), the initial response takes quite a few seconds more to action.

3. Subsequent requests directly after the initial one seem very fast.. again until there is a pause "for a few hours" or whatever the timing is?

Any ideas? if so, any examples/directions of what to do?

Thanks!

David.

Answer 1

Yes, there is the so called RANU instance, which is what you get when you specify User Instance=True in the connection string. Read more about this in SQL Server 2005 Express Edition User Instances. I would recommend you stay as far away as possible from anything related to User Instances. They are impossible to debug and troubleshoot when things go wrong, they sometimes have a ramp up time to create the new instance of minutes, and they really offer no advantage in the real world. Besides, they are deprecated in SQL Server Express 2008.

If you're using SQL Express 2008 and you do not specify User Instance=True in your connection string then you do no get an user instance, so probably the first request time comes from the IIS app pool warm up, as other have suggested. It may also occur due to ordinary process workingset attrition that would cause the SQL buffer pools to go cold. You can easily identify whether it is IIS or SQL by monitoring appropriate performance counters on your system.