Binaryworld - Avoid Auto Close and Auto Shrink. ... [ SQL Server -> Server Operations ]

I was on-site with a client, whose had a server which performing very sluggishly. It was a beefy brute with heaps of memory and processing power, so clearly something was just not what it should have been.

For me step 1 in doing any sort of trouble-shooting is to look at the logs. Yup, always a good place to start because the problem was ticking over at 5-8 entries per second…

Starting up Database ‘Tom’
Starting up Database ‘Dick’
Starting up Database ‘Harry’

So, what’s the problem then? Well, before I answer that, you can find these properties either by looking at the ‘Options’ tab of your database properties, or by running

Click here to copy the following block

SELECT DATABASEPROPERTYEX('DatabaseName','IsAutoShrink')
GO

SELECT DATABASEPROPERTYEX('DatabaseName','IsAutoClose')
GO

If the option is ‘True’ (the T-SQL statement will return 0=false or 1=True), then there’s a performance hit just looking for a place to happen.

Auto_Close

When Auto_Close is set to ON/TRUE, the database is closed and shut down when all processes in the database complete and the last user exits the database, thereby freeing up the resources held by that database. When a new connection calls the database again, it automatically reopens. This option is set to ON/TRUE when using the SQL Server Desktop Edition, but is set to OFF/FALSE for all other editions of SQL Server.

The problem is that most servers sit behind applications that are repeatedly opening and closing connections to your databases, so the overhead of closing and reopening the databases between each connection is, well, “performance abuse”. The amount of memory that is saved by this is insignificant, and certainly does not make up for cost of repeatedly initializing the database.

Admittedly, this option may have advantages on personal desktop scenarios as (when they are closed) you can treat these database files as any other files. You can move them and copy them, or even e-mail them to other users. However, when it comes to a proper server environment these points are fairly irrelevant.
So as far as Auto_Close is concerned, don’t even be tempted. Just Don’t.

Auto_Shrink

The auto_shrink option has it’s uses in scenarios such as development servers and the like where disk space resources are usually limited and hotly contested, but (there’s always a ‘but’) there is a performance cost. Shrinking a database hogs the CPU and takes a long time. Plus, any indexes on the heaps (a table without a clustered index) affected by the shrink must be adjusted because the row locators will have changed. More work for the CPU. Like Auto_Close, this option is set to ON/TRUE for all databases when using SQL Server Desktop Edition, and OFF for all other editions, regardless of operating system.

When this option is set to ON/TRUE, all of a database's files are marked for shrinking, and will be automatically shrunk by SQL Server. This option causes files to be shrunk automatically when more than 25 percent of the file contains unused space. Not a wise option for your production systems which would suddenly suffer a performance hit when SQL decides it’s shrink-time. So, again – just don’t.

Here is a quick script which will run on SQL2000 giving you a list of your databases with the status of these options.

Click here to copy the following block

SET NOCOUNT ON

SELECT [name] AS DatabaseName
, CONVERT(varchar(100),DATABASEPROPERTYEX([Name] , 'Recovery')) AS RecoveryType
, CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoClose')) AS AutoClose
, CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoShrink')) AS AutoShrink
FROM master.dbo.sysdatabases
Order By DatabaseName