You use the Database Settings page of the Server Properties dialog box to configure server-wide database settings. As shown in Figure 6-7, you can use this page to set index fill, backup and restore options, and recovery intervals for checkpoint execution.
The default index fill determines how much space SQL Server should reserve when it creates a new index using existing data. Setting the fill factor involves a tradeoff—if you set the fill factor too high, SQL Server will slow down when you add data to a table. However, if you set the fill factor too low, this can affect read performance by an amount inversely proportional to the fill factor. For example, a fill factor of 25 percent can degrade read performance by a factor of 4 (or 4 times normal), but the setting makes it possible to perform large updates faster initially. Ideally, you should balance the need to make updates quickly with the need to have good read performance, and then select a fill factor that makes sense for your situation.
Best Practices
The fill factor is used only when an index is created; it is not maintained afterward. This allows you to add, delete, or update data in a table without worrying about maintaining a specific fill factor.
Best Practices
Therefore, the empty space in the data pages can fill up if you make extensive additions or modifications to the data. To redistribute the data, re-create the index and specify a fill factor when you do so. Indexes are discussed more completely in Chapter 9.
By default, the index fill is set at 0, but the valid range is 0 to 100. The setting of 0 is the optimized index fill setting; any other value is an actual fill percentage.
SQL Server handles the optimized setting in much the same way as a fill percentage of 100—SQL Server creates clustered indexes with full data pages and nonclustered indexes with full leaf pages. But the optimized setting of 0 leaves space for growth in the upper level of the index tree, which an index fill setting of 100 does not do. This is the reason why you should use this value only with read-only tables in which you never plan to add data.
If necessary, you can override the default setting when you create indexes, but you have to remember to do this. You can also set a fixed index fill as the default by completing the following steps:
From the Server Properties dialog box, go to the Database Settings page.
Use the Default Index Fill Factor box to set a fill percentage. A low fill factor provides more room for insertions without requiring page splits, but the index takes up more space. A high fill factor provides less room for insertions that do not require page splits, but the index uses less space.
Click OK.
With sp_configure, the related Transact-SQL statement is:
exec sp_configure "fill factor (%)", <integer percentage>You often make SQL Server backups on tape devices. When working with tape devices and the DB-Library, you may want to control whether or not you want to enforce a read/write time-out to wait for a new tape. The options you can use include:
Wait Indefinitely. DB-Library waits until a new tape is found. If you select this option, however, you will not necessarily receive an error message to let you know that you are having backup problems.
Try Once. The DB-Library tries once for a response from SQL Server. If there is no response or no tape is available, it quits and typically generates an error.
Try For…. The DB-Library tries to get a response from SQL Server for a specified number of minutes. If there is no response or no tape is available within the wait time, DB-Library quits and typically generates an error.
You set the time-out period by completing the following steps:
From the Server Properties dialog box, go to the Database Settings page.
To set an indefinite time-out, select the Wait Indefinitely option.
To set the backup process to try once and then quit, select the Try Once option button.
To set the backup process to try for a specified amount of time, select the Try For n Minute(s) option, and then enter the time-out period in the box provided.
Click OK.
As you will learn in Chapter 14, SQL Server has many features to help you back up and restore data. When you write data to tapes using DB-Library, you can specify the number of days to maintain old files. This value is called the retention period, and you set it by completing the following steps:
From the Server Properties dialog box, go to the Database Settings page.
Enter the number of days you want to maintain old files in the Default Backup Media Retention (In Days) box. The minimum value is 0, which specifies that old files are always overwritten. The valid range is 0 to 365.
Click OK.
With sp_configure, the related Transact-SQL statement to set the retention period for backup files is:
exec sp_configure "media retention", <number of days>Database checkpoints flush all cached data pages to the disk, and these checkpoints are done on a per-database basis. In SQL Server, you control how often checkpoints occur using the recovery interval setting. By default, the recovery interval is set to 0, which allows SQL Server to control when checkpoints occur dynamically. This usually means that checkpoints occur about once a minute on active databases. Unless you are experiencing performance problems that are related to checkpoints, you should not change this option.
If you need to set the checkpoint interval manually, you must complete the following steps:
From the Server Properties dialog box, go to the Database Settings page.
Enter the checkpoint time in minutes in the Recovery Interval (Minutes) box. The valid range is 0 to 32,767, and this is a server-wide setting.
Click OK.
With sp_configure, the related Transact-SQL statement is:
exec sp_configure "recovery interval", <number of minutes>