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:

If my website (in this case) doesn't have anyone using it for "a few hours", SQL Express "seems" to go to sleep.
The next time someone comes along (after the pause for however long), the initial response takes quite a few seconds more to action.
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.

score 2 · Accepted Answer · answered Nov 04 '10 at 18:20

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.

Thanks! yes I think it was a mix of the IIS app pool settings, and the answer below helps.. i.e. SQL express "auto close" setting THANKS TO ALL FOR HELPING! — Dav.id, Nov 05 '10 at 07:56

score 1 · Answer 2 · answered Nov 04 '10 at 09:22

This isn't the database going to sleep, this is the Application Pool in IIS. If no users are connected/using the website, then the application pool will reset and the session(s) will shutdown. Then, when a user comes to the website, it has to restart the website.

score 1 · Answer 3 · edited May 23 '17 at 12:19

1

There is a term called Database Warmup pal, you can find out more here and probably this is your solution

edited May 23 '17 at 12:19

Community

1
1

answered Nov 04 '10 at 09:28

Jahan Zinedine

14,616
5
46
70

Thank you! in this case, the auto-close for SQL Express was the ticket! – Dav.id Nov 05 '10 at 07:57

score 0 · Answer 4 · answered Nov 04 '10 at 09:19

0

Are you sure it's the database going to sleep and not IIS? IIS will unload websites after a certain period of inactivity, and they can be very slow to reload.

answered Nov 04 '10 at 09:19

Will Dean

39,055
11
90
118

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

4 Answers4