Once you're familiar with maintenance-plan architecture and sqlmaint's utility switches, you can start manually creating maintenance tasks to gain greater control over which options execute under what conditions. You can also dynamically generate sqlmaint switches in code and execute existing plans after slightly modifying the options. For example, you could alternate backup locations every day so that you store your backups on two hard disks instead of one. You can implement maintenance tasks as scheduled jobs just like the wizard does, you can call them from T-SQL through the xp_sqlmaint extended stored procedure, or you can call the sqlmaint utility from a batch file or an application.
Creating a Maintenance Plan
The easiest way to create a maintenance plan is to run the Database Maintenance Plan Wizard in Enterprise Manager. To start the wizard, expand the Management node, right-click on Database Maintenance Plans, and select the New Maintenance Plan option. The first screen lets you select databases for the plan to operate on. You can select databases individually or use one of the predefined multidatabase options: All databases, All system databases, or All user databases. Using these options is convenient but isn't suitable for every environment because the maintenance needs of each database on a server often differ vastly. I usually create one plan that uses the All system databases option on every server and another plan that uses All user databases on development and staging servers. For production environments, I create a unique plan for each mission-critical database. However, the All databases and All user databases options provide one big benefit: Any new database you add to the server is included in the plan. So, for example, if the plan includes backups, you don't need to worry about the new databases; they'll automatically be backed up.
The next few steps in the wizard let you define maintenance-plan actions such as optimizations, integrity checks, database backups, and transaction log backups. Let's examine the options for each action. Figure 2 shows options for defining database optimizations. Selecting the first option, Reorganize data and index pages, tells SQL Server to rebuild all indexes on all tables. Depending on the existing fragmentation, regular index rebuilding can reduce the size of data and index pages. Regular defragmentation by rebuilding indexes helps speed database queries. Because defragmentation decreases the number of data and index pages in the database, SQL Server has to perform fewer reads during data retrievals. You can choose to rebuild indexes with the original index fill factor or specify a new fill factor. If, for example, you select to leave 10 percent of each page as free space, the sqlmaint utility executes DBCC DBREINDEX (TableName,", 90, sorted_data_ reorg) for each table in the database. The amount of space you can recover depends on existing fragmentation and the fill factor you use. Because index rebuilding results in table locks, reindexing a database can interfere with regular database activities, so make sure to schedule this activity when database use is lowest. Creating or rebuilding an index is a logged operation on databases that use the Full or Bulk-Logged Recovery model, so when you rebuild all of a database's indexes, you can expect the size of the next transaction log backup to be very large—sometimes close to the size of a full backup.
Because SQL Server automatically updates index statistics when it rebuilds an index, the second optimization option, Update the statistics used by the query optimizer, is available only when you don't choose to reorganize data and indexes. Selecting this option is usually unnecessary because unless you change the default database setting for the AUTO_UPDATE_STATISTICS option, SQL Server periodically updates index statistics. I recommend updating database statistics as a part of the maintenance plan only when the AUTO_UPDATE_STATISTICS option is off.
You should use the third option, Remove unused space from database files, only in special scenarios (such as when disk space is a concern) because database shrinking typically isn't recommended. When you shrink a database, SQL Server must move all used pages to the beginning of the data files and release the free space back to the OS, causing a lot of CPU and disk I/O activity. Then, as soon as your database starts growing again, SQL Server must expand the same data files. But because the files no longer occupy contiguous space in the OS, your data pages become fragmented.
Database integrity checks are the second plan action you can set up in the wizard. On the Integrity tab, which Figure 3 shows, you can specify whether you want to check just the data or both the data and the indexes. When you select the Attempt to repair any minor problems check box, the sqlmaint utility includes the REPAIR_FAST clause in the DBCC CHECKDB command. Be aware that SQL Server can make database repairs only when the database is in single-user mode. The maintenance plan tries to set the database to single-user mode before running the repair command, but if any users are in the database, the statement fails and the scheduled job reports an error and doesn't execute. Once the database is in single-user mode, no other users can connect to the database while the DBCC command with REPAIR_FAST is running, so users might not be able to use the database for a couple of minutes for each gigabyte of data in your database. Also, no other scheduled jobs can run until sqlmaint sets the database back to multiuser mode. So although BOL recommends including the repair option, you need to evaluate your database's usage patterns and schedule maintenance for times you're sure the database isn't in use or run the repair option manually if the DBCC command reports an error.
Selecting Perform these tests before backing up the database or transaction log results in execution of the DBCC CHECKDB command before every full backup or log backup. If the DBCC check finds a problem, sqlmaint won't proceed with the backup. This behavior is designed to prevent backups of a possibly corrupted database, but something as simple as a corrupt index or sqlmaint being unable to get into single-user mode will leave backups undone and put data at risk. If you choose this option, make sure you monitor the status of the integrity-check job so that you can fix the database immediately if necessary and resume performing backups.
The other consideration for this option is the frequency of integrity checks. Integrity checks are resource-intensive; they result in table locks and therefore can affect database and server performance and interfere with the applications accessing the database. You should perform them only when the database usage is at a minimum. Unfortunately, the wizard doesn't let you choose to perform integrity checks before full backups but not before log backups. This limitation makes this option unsuitable for production databases that use frequent log backups. I've seen a large production database with this option turned on performing integrity checks and log backups every hour. The DBCC command took about 20 minutes to finish, so the database server was running resource-intensive integrity checks about one-third of the time.
Prev. page
1
[2]
3
next page 
