Setting Database Properties to Improve Performance

There are a few SQL Server database properties that, when configured correctly, will help increase performance. This posting will talk about them, what there effects are, as well as suggested settings.

The properties that will be discussed are:

• Compatibility Level
• Auto Close
• Auto Shrink
• Recovery Model

Compatibility Level set to SQL Server 2008 (100)

Compatibility Level effects SQL syntax and query parsing, and should have no impact of performance. Setting the Compatibility Level to a value of SQL Server 2008(100) will take advantage of new T-SQL features, which are used in many of the scripts / commands that will appear in this blog.

Setting the Compatibility Level

In order to set the Compatibility Level property:

1. Launch SQL Server Management Studio.
2. In the object explorer, right click on the CMS Master database and select Properties.
3. Select the Options page and make sure that the Compatibility Level is set to SQL Server 2008(100).
4. Click OK.
5. Repeat steps 1-4 for the CMS Core and Web databases.

Auto Close Property set to false

When SQL Server opens a database, resources are allocated to maintain that state. Memory for locks, buffers, security tokens, etc. are all assigned. These operations take time. The Auto Close property dictates how these resources are handled. If it is set to ‘true’ or ‘ON’, then when the last connection is closed these resources are deallocated. This may seem like a good thing, but if a new connection comes in within a short period of time (1/10th of second or quicker), then all of those resources need to be spun up again. Setting the Auto Close property to ‘false’ or ‘OFF’ will prevent this from happening.

Setting the Auto Close property

In order to set the Auto Close property:

1. Launch SQL Server Management Studio.
2. In the object explorer, right click on the CMS Master database and select Properties.
3. Select the Options page and make sure that the Auto Close property is set to false
4. Click OK.
5. Repeat steps 1-4 for the CMS Core and Web databases.

Auto Shrink Property set to false

The Auto Shrink property has the downside of a) it uses a lot of resources when it’s called, and b) you have no control over when it is being called. If you combine Auto Shrink with Auto Growth, you can get into a spiral of constantly growing and shrink the database, taking valuable resources away from other database tasks as well as causing fragmentation issues. If a database or file requires a SHRINK command, it should be done so via a script, command or scheduled Maintenance Plan. Setting the Auto Shrink property to ‘false’ or ‘OFF’ will disable this feature.

Setting the Auto Shrink property

In order to set the Auto Shrink property:

1. Launch SQL Server Management Studio.
2. In the object explorer, right click on the CMS Master database and select Properties.
3. Select the Options page and make sure that the Auto Shrink property is set to false
4. Click OK.
5. Repeat steps 1-4 for the CMS Core and Web databases.

Recovery Model set to Simple

In Simple Recovery Model SQL Server logs minimal amount of information in the transaction log. SQL Server basically truncates the transaction log whenever the transaction log becomes 70 percent full or the active portion of the transaction log exceeds the size that SQL Server could recover in the amount of time which is specified in the Recovery Interval server level configuration.

Setting Recovery Model to Simple has the lowest amount of overhead over Full and Bulk-logged, which is crucial to the performance requirements needed for the CMS databases.

Setting the Recovery Model

In order to set the Recovery Model:

1. Launch SQL Server Management Studio.
2. In the object explorer, right click on the CMS Master database and select Properties.
3. Select the Options page and make sure that the Recovery Model is set to Simple
4. Click OK.
5. Repeat steps 1-4 for the CMS Core and Web databases.