MS SQL AutoClose

Something that solved one of our big problems AutoClose.

Recently in one of our project we exposed a stateless service with SQL 2005 Express back end. And we ended up with a crazy issue.

Issue is after 5-6 minutes the performance of the service went down. Thought it was in milli seconds, when clients made 100-200 calls, performance hit was visible.

Its like SQL maintains resources for a connection or execution of query, which SQL by default will recycle when there is no activity. This is a good feature, however in our case it proved very costly.

So turned the feature OFF.
create database employees;
alter database employee set AUTO_CLOSE OFF;