Server Configuration Maintenance

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_STATISTICSDisplays the current distribution statistics for the specified target on the specified table.

• DBCC SQLPERFProvides statistics about the use of transaction log space in all databases.

• DBCC TRACESTATUSDisplays the status of trace flags.

• DBCC USEROPTIONSReturns the SET options that are active (that is, set) for the current connection.

Exam Alert

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:

• DBCC CHECKALLOCChecks the consistency of disk space.

• DBCC CHECKCATALOGChecks for consistency in system tables.

• DBCC CHECKCONSTRAINTSChecks the integrity of constraints.

• DBCC CHECKDBChecks the allocation and structural integrity of all the objects in a database.

• DBCC CHECKFILEGROUPChecks the allocation and structural integrity of tables in a filegroup.

• DBCC CHECKIDENTChecks the current identity value for a table and, if needed, corrects the value.

• DBCC CHECKTABLEChecks the integrity of the data, index, text, ntext, and image pages.

• DBCC NEWALLOCChecks the allocation of data and index pages. It is equivalent to CHECKALLOC and is used for backward compatibility only.

Note

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.

Exam Alert

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.

Note

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:

• DBCC DBREINDEXRebuilds one or more indexes.

• DBCC INDEXDEFRAGDefragments clustered and secondary indexes of the specified table or view.

• DBCC SHRINKDATABASEShrinks the size of the data files in the specified database.

• DBCC SHRINKFILEShrinks a specified data file or log file.

• DBCC UPDATEUSAGEReports and corrects inaccuracies in the sysindexes table, which may result in incorrect space use reported by sp_spaceused.

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:

• DBCC dllname (FREE)Unloads the specified extended stored procedure DLL from memory.

• DBCC HELPReturns syntax information for the specified DBCC statement.

• DBCC PINTABLEMarks a table to be pinned and does not flush the pages for the table from memory.

• DBCC ROWLOCKIs used for Microsoft SQL Server 6.5, enabling insert row locking operations on tables.

• DBCC TRACEOFFDisables trace flags. (Trace flags are discussed in the next section.)

• DBCC TRACEONEnables trace flags. (Trace flags are discussed completely in the next section.)

• DBCC UNPINTABLEMarks a table as unpinned. Table pages in the buffer cache can be flushed.

Note

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.