SQL Express DB performance – auto-close evil

A quote from the original post:

By default, Express Edition creates databases with the Auto Close option enabled. This option allows SQL Server to close the physical operating system files for the database when all connections to the database are closed. The advantage here is that individual workstation installations can have database files copied around as easily as document files. This is not so advantageous in a server setup, as the overhead of opening the files and starting up the database can cause poor response times.

To check this setting for an individual database, right click the database in Management Studio, choose Properties, go to the Options page, and verify the state of the Auto Close option (it should be at the top of the list). You’ll almost certainly want to set this to False for every database on a dedicated server, with the possible exception of extremely seldom used databases.

See the original post here.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: