USEFUL SITES :
Write for Us
Each Database Needs to Be Audited
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):
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.
Auto_Create_Statistics
When auto_create_statistics is turned on (which it is by default), statistics are automatically created on all columns used in the WHERE clause of a query. This occurs when a query is optimized by the Query Optimizer for the first time, assuming the column doesn't already have statistics created for it. The addition of column statistics can greatly aid the Query Optimizer so that it can create an optimum execution plan for the query.
If this option is turned off, then missing column statistics are not automatically created, when can mean that the Query Optimizer may not be able to produce the optimum execution plan for the query, and the query's performance may suffer. You can still manually create column statistics if you like, even when this option is turned off.
There is really no down-side to using this option. The very first time that column statistics are created, there will be a short delay as they are created before the query runs for the first time, causing the query to potentially take a little longer to run. But once the column statistics have been created, each time the same query runs, it should now run more efficiently than if the statistics did not exist in the first place.
As part of your audit, if you find this option turned off, you will need to research why it was turned off. If you can't find the reason, or if the reason is poor, turn this option on.
