Several maintenance activities should be performed regularly. Over time, a database becomes fragmented and is likely to become much larger than needed. You might want to shrink database files because setting them to automatically shrink causes a performance hit on the system.
You should configure maintenance plans for most of the databases on a server to ensure that they are operating optimally. You can use maintenance plans to create a workflow of the maintenance tasks required to make sure that your database performs well, is regularly backed up in case of system failure, and is checked for inconsistencies.
You can use the Maintenance Plan Wizard to create core maintenance plans and to initiate maintenance workflows. After you create plans by using this wizard, you can make modifications and additions to these plans manually, which gives you quite a bit of flexibility. In the SQL Server 2005 database engine, maintenance plans create a job that performs these maintenance tasks automatically at scheduled intervals.
Using the Database Console Command (DBCC)
A number of DBCC operations should be performed periodically. You can effectively use many of the DBCC options in troubleshooting. The following options can provide valuable data to help you determine how to improve performance:
DBCC SHOW_STATISTICS displays the current statistic distribution, and DBCC SQLPERF provides information about available log space. All DBCC operations will be supported going forward, unlike the dynamic views sys.dm_db_index_operationalstats and sys.dm_db_index_physicalstats, which provide the same information but may not be supported in the next version.
DBCC Validation Operations
After status information has been generated, the next task in information retrieval is obtaining validation data, which can also give you insight into a server. The validation options represent tools that can reveal database storage problems and also provide the mechanisms to modify and fine-tune the environment. To validate various objects on a server, you can use the following:
Use of CHECKALLOC is unnecessary if CHECKDB is used first. CHECKDB contains a superset of options that includes all the functionality provided by CHECKALLOC.
Some of these functions are CPU and disk intensive. You should exercise caution about the time of day you perform a DBCC CHECKDB operation. Other functions can also affect the server and temporarily increase system overhead.
On the 70-431 exam, you might see a question that provides a series of the CHECK options and expects you to know the granularity of what is being checked by the operation. Therefore, be familiar with each of the options in the previous list.
DBCC DBREPAIR was never used often, and has been removed from the product. It is recommended that you use DROP DATABASE to drop damaged databases. DBCC DBREPAIR is no longer supported.
DBCC Maintenance Operations
Regular maintenance is needed in all database environments. Data and index pages become fragmented. Data may become corrupt, and file sizes may need to be adjusted. Regular maintenance helps you optimize the server environment. The DBCC options that you use in maintenance processes are as follows:
Miscellaneous DBCC Operations
Some DBCC options do not directly fit into any of the categories described so far in this chapter. Listed as miscellaneous options, these DBCC operations can provide assistance, help to free and better use resources, and provide some tracking mechanisms:
The DBCC ROWLOCK option is somewhat antiquated. Row-level locking is enabled by default in SQL Server 2005, where the locking strategy is row locking with possible promotion to page or table locking. DBCC ROWLOCK is included in SQL Server 2005 for backward compatibility. In a future version of SQL Server, DBCC ROWLOCK may not be supported.
Setting Alerts to Automate Problem Identification
SQL Server Agent can monitor performance conditions and fire alerts when specific thresholds are met. This can help in notifying an administrator when disk space is running low or when applications are unresponsive. You can also set up alerts to fire based on criteria within the data. You are not likely to run into this on the 70-431 exam, but it is a useful feature of SQL Server.