As part of your performance audit, you need to examine each database located on your server and examine some basic database settings. When compared to some of our other performance audit tasks, you will find this audit one of the easiest. For convenience, you may want to consider photocopying a copy of the above chart, producing one copy for each database that you will be auditing.
As a part of our database settings audit, we will be taking a look at two different types of settings: database options and database configuration settings. As in previous sections of our performance audit, we will only focus on those database settings that are directly related to performance, ignoring the rest.
Both database options and database configuration settings can be viewed or modified using Enterprise Manager (my preference, as it is easier) or modified with the ALTER DATABASE command. In addition, for the database options only, you can also use the sp_dboption system stored procedure to view and modify them, but Microsoft is trying to phase this command out, and discourages its use (as of SQL Server 2000).
The first section of the database settings performance checklist focuses on database options, and the second section focuses on database configuration settings.
Viewing Database Options
In this section, we will only be taking a look at six of the many database options that in one way or another can affect performance. The best way to view the current settings is to use Enterprise Manager, following these steps (These steps assume you are using SQL Server 2000):
In Enterprise Manager, display all of the databases for your server.
Right-click on the database you want to examine and select “Properties.”
From the Properties dialog box, select the “Options” tab.
From this screen, you can see all of the relevant database options. Note that not every database option can be seen here, but all the ones that we are interested are all listed here. Let’s take a look at the performance-related ones and see how they affect SQL Server’s performance.
Auto_Close
This database option is designed for use with the Desktop version of SQL Server 7.0 and 2000, not for the server versions. Because of this, it should not be turned on (which it is not, by default). What this option does is to close the database when the last database user disconnects from the database. When a connection requests access to the database after it has been closed, then the database has to be reopened, which takes time and overhead.
The problem with this is that if the database is accessed frequently, which is the most likely case, then the database may close and reopened often, which puts a large performance drag on SQL Server and the applications or users making the connection.
As part of your audit, if you find this option turned on, and you are not using the desktop version of SQL Server, then you will need to research why it was turned on. If you can’t find the reason, or if the reason is poor, turn this option off.