Creating and Implementing a Maintenance Strategy for Database Servers

As a DBA, it is your responsibility to ensure that your SQL Server 2005 solutions are reliable, robust, and performing-and a million other things as well. Many techniques and tools can aid you in the everyday running, maintenance, and documentation required for the daily administration of a SQL Server 2005 instance.

The more professional organizations that use SQL Server adopt proactive management techniques to mitigate the impact on business that unpredictable performance problems, database corruptions, faulty hardware, and other such problems can cause.

Although it will depend on your infrastructure and environmental circumstances, generally most organizations perform a number of maintenance tasks:

• Maintenance tasks that must be performed on a regular schedule:

  • System database backups (including master, msdb, and potentially model)

  • User database backups

  • User database transaction log backups

    Note 

    Chapter 3, “Optimizing and Implementing a Data Recovery Plan for a Data-base,” covered database backup strategies in depth.

  • Database consistency checks

  • Index maintenance

  • Fill-factor maintenance

  • Statistics maintenance

  Note 

  Chapter 2, “Optimizing the Performance of Queries,” covered index maintenance in depth.

• Tasks that respond to periodic, predictable, or critical events:

  • Database events (such as the transaction log becoming full)

  • Database performance conditions (such as the transaction log becoming 90 percent full)

  • Security events (such as unauthorized access to data)

  • Critical events (such as the SQL Server instance hanging)

• Data maintenance tasks:

  • Importing data from other data sources

  • Refreshing data from production to development SQL Servers

  • Executing SQL Server Integration Services (SSIS) packages

  • Archiving data into historical tables

  • Generating summary and/or denormalized data

  • Replication tasks

• Tasks that ensure data quality:

  • Data sampling for testing/review

  • Execution of user-defined procedures to automatically produce key metrics and documentation

• Common ad hoc tasks:

  • Creating and delegating login accounts

  • Creating and delegating database user accounts

  • Resetting passwords

The core tasks, of course, are your database backups, database consistency checks, and index maintenance tasks. We have already covered backup strategies in Chapter 3, so in this chapter we will concentrate more on the database consistency checks and the index maintenance tasks.

Don’t forget that all of these tasks can benefit from automation, so we will also cover the SQL Server Agent. We have already covered how to create alerts via the SQL Server Agent in Chapter 4, “Designing a Strategy to Monitor a Database Solution,” so we will concentrate more on its scheduling capabilities in this chapter.

Note 

Automation also reduces the risk of problems caused by human error.

Also, don’t forget the importance of documentation. Organizations that carefully plan and document their various automated processes and procedures minimize the downtime incurred in the event of database disaster recovery situations, or when there is a need to rollback a configuration change.

Note 

Again, documenting your SQL Server 2005 solution (and, in particular, maintaining a run book) is important. Microsoft has an article titled “Contents of a Run Book” that you can find at http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/harag0a.mspx.

Running Database Consistency Checks

The database console commands (DBCCs) are perhaps the must important set of commands a DBA should understand. The DBCC consistency check commands form the basis of a proactive database maintenance solution, coupled with an appropriate reindexing and backup strategy.

You might recall that back in the SQL Server 6.x days DBAs had to run the DBCCs on a fairly regular basis to keep those minor inconsistencies under control, before they became major problems. However, SQL Server’s storage engine was reengineered for version 7.0.

So, the storage engine is fairly rock solid. In other words, database consistency errors tend to be caused more by hardware, rather than the storage engine. In fact, we have come across only one instance of a serious database corruption in the past five years or so-and it was due to a RAID controller failing! But that does not mean to say that you should not run your DBCCs.

The million-dollar question is, how often you should run these DBCC checks? And that is a very difficult question to answer. Perhaps you should tackle it from a different perspective: what is the length of your maintenance window? In most cases, your SQL Server 2005 solution is being used only on weekdays during business hours, so you probably have a large maintenance window. So why not run them daily?

For SQL Server 2005 solutions that are running in a 24/7 environment, the maintenance window does not exist. However, you should still schedule the DBCC checks to run every so often, probably during a time when the SQL Server 2005 solution is least busy. The great part about SQL Server 2005 is that the DBCC check commands (DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE) utilize an internal read-only database snapshot that the storage engine creates, which prevents blocking and concurrency problems that these commands traditionally used to create.

Note 

Because these DBCC check commands rely on database snapshots, you need to ensure that your tempdb system database has been properly capacity planned and optimized.

However, in certain instances, SQL Server 2005 cannot take advantage of internal database snapshots because they either are not required or can’t be created. A table lock will be used instead. The following situations are when an internal snapshot will not be used:

• Against the master system database when SQL Server 2005 is running in single-user mode

• Against a database set to single-user mode (other than master)

• Against a read-only database

• Against a database set to emergency mode

• Against the tempdb system database

• When the TABLOCK option is used

• Additional restrictions include the following:

• A read-only file group is being used.

• The FAT file system is being used.

• The volume does not support “alternate streams.”

• The volume does not support “named streams.”

Although the DBCCs have been around since the Sybase days and there is some similarity, you really should think of them as being particular to Microsoft SQL Server.

Warning 

Be careful of using your knowledge of the DBCC and related commands based on earlier versions of SQL Server. We highly recommend you read the “DBCC” topic in SQL Server 2005 Books Online.

In the following sections, you’ll look at the more important and relevant DBCCs available in SQL Server 2005.

DBCC CHECKALLOC

The DBCC CHECKALLOC command checks the consistency of the various internal structures (allocation units, GAM pages, IAM pages, IAM chains, SGAM pages, PFS pages) that describe the allocations of pages and extents within the database.

The syntax for the DBCC CHECKALLOC command is as follows:

 DBCC CHECKALLOC [         ( 'database_name' | database_id | 0       [ , NOINDEX       | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]         )     [ WITH         {           [ ALL_ERRORMSGS ]           [ , NO_INFOMSGS ]           [ , TABLOCK ]           [ , ESTIMATEONLY ]         }     ] ]

The REPAIR_ALLOW_DATA_LOSS option tries to repair any errors that were detected. As the option implies, some data loss could occur with the use of this option. The other repair options effectively do nothing and are included for backward compatibility and/or consistency.

Warning 

Microsoft recommends using the REPAIR options as a last resort. Restoring from database backups is the recommended strategy to use. If you need to use a REPAIR option, Microsoft recommends running DBCC CHECKDB without any REPAIR option to determine the REPAIR option to use. Of course, it goes without saying that you should back up the database before using the REPAIR_ALLOW_DATA_LOSS option, but we’ll say it anyway!

The ESTIMATEONLY option shows the estimated amount of tempdb space needed to run the DBCC CHECKALLOC command.

The following example shows the DBCC CHECKALLOC command being run against the UnitedNations database:

 DBCC CHECKALLOCK ('UnitedNations) ;

This has the following output:

 DBCC results for 'UnitedNations'. *************************************************************** Table sys.sysrowsetcolumns                Object ID 4. Index ID 1, partition ID 262144, alloc unit ID 262144 (type    In-row data). FirstIAM (1:147). Root (1:108). Dpages 7. Index ID 1, partition ID 262144, alloc unit ID 262144 (type    In-row data). 9 pages used in 0 dedicated extents. Total number of extents is 0. *************************************************************** Table sys.sysrowsets                Object ID 5. Index ID 1, partition ID 327680, alloc unit ID 327680 (type    In-row data). FirstIAM (1:167). Root (1:16). Dpages 0. Index ID 1, partition ID 327680, alloc unit ID 327680 (type    In-row data). 2 pages used in 0 dedicated extents. Total number of extents is 0. *************************************************************** …     Object ID 1115151018, index ID 2, partition ID    72057594038714368, alloc unit ID 72057594044612608 (type    In-row data), index extents 0, pages 8, mixed extent pages 8.     Object ID 1147151132, index ID 0, partition ID    72057594038583296, alloc unit ID 72057594043170816 (type    In-row data), data extents 0, pages 2, mixed extent pages 2. The total number of extents = 47, used pages = 271, and    reserved pages = 336 in this database.        (number of mixed extents = 31, mixed pages = 208) in this database. CHECKALLOC found 0 allocation errors and 0 consistency    errors in database 'UniteNations'. DBCC execution completed. If DBCC printed error messages,    contact your system administrator.DBCC execution    completed. If DBCC printed error messages, contact your    system administrator.

DBCC CHECKCATALOG

The DBCC CHECKCATALOG command checks the system catalog (system tables) of a database. The DBCC CHECKCATALOG command also is run during the execution of the DBCC CHECKDB command.

The syntax for the DBCC CHECKCATALOG command is as follows:

 DBCC CHECKCATALOG [         (         'database_name' | database_id | 0         ) ]     [ WITH NO_INFOMSGS ]

The following example shows the DBCC CHECKCATALOG command being run against the UnitedNations database:

 DBCC CHECKCATALOG ('UnitedNations') ;

This has the following output:

 DBCC execution completed. If DBCC printed error messages,    contact your system administrator.

DBCC CHECKCONSTRAINTS

The DBCC CHECKCONSTRAINTS command checks the table-level constraints in a database. The DBCC CHECKCONSTRAINTS command constructs and executes a query for all FOREIGN KEY constraints and CHECK constraints on a table.

Note 

The DBCC CHECKCONSTRAINTS command checks the integrity of only FOREIGN KEY and CHECK constraints. It does not check the integrity of the data structures of a table.

The syntax for the DBCC CHECKCONSTRAINTS command is as follows:

 DBCC CHECKCONSTRAINTS [         (         'table_name' | table_id | 'constraint_name' | constraint_id         ) ]     [ WITH         [ { ALL_CONSTRAINTS | ALL_ERRORMSGS } ]     [ , ] [ NO_INFOMSGS ]     ]

Tip 

By default, only the enabled constraints are checked. Use the ALL_CONSTRAINTS option to run the DBCC CHECKCONSTRAINT command against disabled constraints as well.

The following example shows the DBCC CHECKCONSTRAINT command being run against the UnitedNations database:

 DBCC CHECKCONSTRAINTS ('UnitedNations') ;

This has the following output:

 DBCC execution completed. If DBCC printed error messages,    contact your system administrator.

DBCC CHECKDB

The DBCC CHECKDB command checks the logical and physical integrity of the database. At a high level, the DBCC CHECKDB command performs the following tasks:

• Primitive checks of critical system tables

• Allocation checks of the database

• Logical checks of critical system tables

• Logical checks of all tables

• Service Broker checks

• Metadata cross-checks

• Indexed view checks

• XML index checks

The DBCC CHECKDB command is effectively a superset of the following DBCCs:

• DBCC CHECKALLOC

• DBCC CHECKCATALOG

• DBCC CHECKTABLE

Consequently, there is no need to run them at the same frequency as the DBCC CHECKDB command.

The syntax for the DBCC CHECKDB command is as follows:

 DBCC CHECKDB [     [ ( 'database_name' | database_id | 0         [ , NOINDEX         | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]         ) ]     [ WITH         {             [ ALL_ERRORMSGS ]             [ , NO_INFOMSGS ]             [ , TABLOCK ]             [ , ESTIMATEONLY ]             [ , { PHYSICAL_ONLY | DATA_PURITY } ]         }     ] ]

The ESTIMATEONLY option shows the estimated amount of tempdb space needed to run the DBCC CHECKDB command.

The TABLOCK option use locks instead of the internal database snapshot to run the DBCC CHECKDB command.

You can use the PHYSICAL_ONLY option to reduce the amount of time taken by the DBCC CHECKDB operation (which by default is quite exhaustive and comprehensive). The PHYSICAL_ ONLY option (as the name implies) skips all the logical checks and performs only the physical operations:

• DBCC CHECKALLOC

• Reads and audits every allocated page

Tip 

You can use the PHYSICAL_ONLY option to detect any data corruption caused by hardware failures. Of course, page checksums need to be turned on for the database.

The DATA_PURITY option validates every column value in all rows of the tables in the database so as to ensure that the values stored in the columns fall within the column’s domain, based on the datatype. For databases created on SQL Server 2005, these checks are enabled by default and cannot be disabled.

Tip 

The data purity validation checks are not enabled automatically for SQL Server 2000 databases that have been upgraded to SQL Server 2005. Consequently, you should run DBCC CHECKDB WITH DATA_PURITY on the upgraded database.

It is critical that you understand the various options available for repairing corrupt databases:

REPAIR_ALLOW_DATA_LOSS The REPAIR_ALLOW_DATA_LOSS option tries to repair all found errors.

Warning 

The REPAIR_ALLOW_DATA_LOSS option can cause some data loss so should be considered a last resort.

Tip 

We recommend restoring your database solution from your latest backups in preference to risking data loss through the REPAIR_ALLOW_DATA_LOSS option.

REPAIR_FAST The REPAIR_FAST option performs no repairs.

Note 

The REPAIR_FAST option has been included for backward compatibility only.

REPAIR_REBUILD The REPAIR_REBUILD option performs both minor, quick repairs and time-consuming repairs without any risk of data loss.

When performing repair operations, don’t forget that the database needs to be in single-user mode for any of these repairs to run. This can simply be done via the ALTER DATABASE statement, for which the syntax is as follows:

 ALTER DATABASE database_name <set_database_options>::= SET {     { <optionspec> [ ,...n ] [ WITH <termination> ] } } <db_user_access_option> ::=     { SINGLE_USER | RESTRICTED_USER | MULTI_USER } <termination> ::= {     ROLLBACK AFTER integer [ SECONDS ]   | ROLLBACK IMMEDIATE   | NO_WAIT }

The following example shows the UnitedNations database being set to single-user mode, where any incomplete transactions will be rolled back immediately:

 USE master ; GO ALTER DATABASE UnitedNations SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;

The following example shows the DBCC CHECKDB command being used to check the UnitedNations database:

 DBCC CHECKDB ('UnitedNations') ;

This has the following output:

 DBCC results for 'UnitedNations'. Service Broker Msg 9675, Level 10, State 1: Message Types analyzed: 14. Service Broker Msg 9676, Level 10, State 1: Service Contracts analyzed: 6. Service Broker Msg 9667, Level 10, State 1: Services analyzed: 3. Service Broker Msg 9668, Level 10, State 1: Service Queues analyzed: 3. Service Broker Msg 9669, Level 10, State 1: Conversation Endpoints analyzed: 0. Service Broker Msg 9674, Level 10, State 1: Conversation Groups analyzed: 0. Service Broker Msg 9670, Level 10, State 1: Remote Service    Bindings analyzed: 0. DBCC results for 'sys.sysrowsetcolumns'. There are 639 rows in 6 pages for object 'sys.sysrowsetcolumns'. DBCC results for 'sys.sysrowsets'. There are 99 rows in 1 pages for object 'sys.sysrowsets'. DBCC results for 'sysallocunits'. There are 295 rows in 3 pages for object 'sysallocunits'. ... DBCC results for 'sys.queue_messages_1977058079'. There are 0 rows in 0 pages for object "sys.queue_messages_1977058079". DBCC results for 'sys.queue_messages_2009058193'. There are 0 rows in 0 pages for object "sys.queue_messages_2009058193". DBCC results for 'sys.queue_messages_2041058307'. There are 0 rows in 0 pages for object "sys.queue_messages_2041058307". CHECKDB found 0 allocation errors and 0 consistency errors    in database 'UnitedNations'. DBCC execution completed. If DBCC printed error messages,    contact your system administrator. DBCC execution completed. If DBCC printed error messages,    contact your system administrator.

DBCC CHECKIDENT

The DBCC CHECKIDENT command checks the current identity value for a given table. The command will reset the maximum value of the identity column if the current identity value is less than the table’s current identity maximum. You can also use the DBCC CHECKIDENT command to set a new seed value for the identity column.

The syntax for the DBCC CHECKIDENT command is as follows:

 DBCC CHECKIDENT (         'table_name'         [ , { NORESEED | { RESEED [ , new_reseed_value ] } } ] ) [ WITH NO_INFOMSGS ]

The following example shows the DBCC CHECKIDENT command being used to reseed the identity column value of the Warlord table:

 DBCC CHECKIDENT ('Warlord', RESEED, 69) ;

This has the following output:

 Checking identity information: current identity value '66',    current column value '69'. DBCC execution completed. If DBCC printed error messages,    contact your system administrator.

DBCC CHECKFILEGROUP

The DBCC CHECKFILEGROUP command checks the allocation and structural integrity of all tables (including nonclustered indexes by default) and indexed views for a given file group in the current database. The DBCC CHECKFILEGROUP command effectively performs DBCC CHECKALLOC on the specified file group and DBCC CHECKTABLE on every table and indexed view in the file group.

Note 

Unlike earlier versions of SQL Server, the DBCC CHECKFILEGROUP command can run in parallel, taking advantage of multiple processors. However, be aware that this feature is available only with the SQL Server 2005 Enterprise Edition. You can disable parallel DBCCs checking by using trace flag 2528.

The syntax for the DBCC CHECKFILEGROUP command is as follows:

 DBCC CHECKFILEGROUP [     [ ( { 'filegroup' | filegroup_id | 0 }         [ , NOINDEX ]     ) ]     [ WITH         {             [ ALL_ERRORMSGS | NO_INFOMSGS ]             [ , TABLOCK ]             [ , ESTIMATEONLY ]         }     ] ]

The ESTIMATEONLY option shows the estimated amount of tempdb space needed to run the DBCC CHECKFILEGROUP command.

The following example shows the DBCC CHECKFILEGROUP command being run against the UnitedNations database:

 USE UnitedNations ; GO DBCC CHECKFILEGROUP ;

The has the following output:

 DBCC results for 'UnitedNations'. DBCC results for 'sys.sysrowsetcolumns'. There are 698 rows in 7 pages for object 'sys.sysrowsetcolumns'. DBCC results for 'sys.sysrowsets'. There are 103 rows in 1 pages for object 'sys.sysrowsets'. DBCC results for 'sysallocunits'. There are 115 rows in 2 pages for object 'sysallocunits'. DBCC results for 'sysfiles1'. There are 2 rows in 1 pages for object 'sysfiles1'. ... DBCC results for 'spt_monitor'. There are 1 rows in 1 pages for object 'spt_monitor'. DBCC results for 'spt_values'. There are 2346 rows in 10 pages for object 'spt_values'. DBCC results for 'MSreplication_options'. There are 2 rows in 1 pages for object 'MSreplication_options'. CHECKFILEGROUP found 0 allocation errors and 0 consistency    errors in database 'master'. DBCC execution completed. If DBCC printed error messages,    contact your system administrator.

DBCC CHECKTABLE

The DBCC CHECKTABLE command checks the integrity of all the pages and structures of a table or indexed view. The DBCC CHECKTABLE operation checks the following:

• All index, in-row, LOB, and row-overflow data pages are correctly linked.

• All internal pointers are consistent.

• All page offsets are reasonable.

• All indexes are in their correct sort order.

• Every row in a base table has a corresponding row in each nonclustered index.

• Every row in a nonclustered index has a corresponding row in its base table.

• Every row in a partitioned table (or index) is in the correct partition.

• The data on each page is reasonable (included computed columns).

The syntax for the DBCC CHECKDB command is as follows:

 DBCC CHECKTABLE (         'table_name' | 'view_name'     [ , { NOINDEX | index_id }      |, { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD }     ] )     [ WITH         { ALL_ERRORMSGS ]           [ , NO_INFOMSGS ]           [ , TABLOCK ]           [ , ESTIMATEONLY ]           [ , PHYSICAL_ONLY ]         }     ]

You can use the NOINDEX option to reduce the length of time the DBCC CHECKTABLE operation will take to complete because it will skip checking nonclustered indexes. The theory is quite simple-if you notice that a nonclustered index is corrupt, you can simply blow it away and create a new one.

The PHYSICAL_ONLY and the various repair options are the same as with the DBCC CHECKDB command described earlier, as you would expect.

The ESTIMATEONLY option shows the estimated amount of tempdb space needed to run the DBCC CHECKTABLE command.

The following example shows the DBCC CHECKTABLE command being run against the [WarZones] table:

 DBCC CHECKTABLE ('WarZones') ;

This has the following output:

 DBCC results for 'WarZones'. There are 6969 rows in 69 pages for object 'WarZones'. DBCC execution completed. If DBCC printed error messages,    contact your system administrator.

DBCC CLEANTABLE

The DBCC CLEANTABLE command is used to reclaim space after a variable-length column is dropped from a table. The variable-length column datatypes include the following:

• IMAGE

• NTEXT

• NVARCHAR(MAX)

• NVARCHAR

• SQL_VARIANT

• TEXT

• VARBINARY

• VARBINARY(MAX)

• VARCHAR

• VARCHAR(MAX)

• XML

Tip 

You should run the DBCC CLEANTABLE command after you have dropped variable-length columns from a table (or indexed view) and want to reclaim the unused space straightaway. You can always wait until you next rebuild the indexes on the table, but that can be a more expensive exercise.

The syntax for the DBCC CLEANTABLE command is as follows:

 DBCC CLEANTABLE (     { 'database_name' | database_id | 0 }         , { 'table_name' | table_id | 'view_name' | view_id }     [ , batch_size ] ) [ WITH NO_INFOMSGS ]

The batch_size option allows you to control the number of rows being processed per transaction.

Note 

If no batch_size is used, the entire table will be processed as one transaction, which might be a problem for very large tables.

The following example shows the DBCC CLEANTABLE command being run against the [WarZones] table:

 DBCC CLEANTABLE ('WarZones') ;

This has the following output:

 DBCC execution completed. If DBCC printed error messages,    contact your system administrator.

DBCC DBREINDEX

The DBCC DBREINDEX command is used to rebuild the indexes of a table in a database. The DBCC DBREINDEX command will not run against system tables.

Note 

DBCC DBREINDEX is an offline operation, unlike DBCC INDEXDEFRAG or ALTER INDEX with the REORGANIZE option, both of which are online operations.

The syntax for the DBCC DBREINDEX command is as follows:

 DBCC DBREINDEX (         'table_name'     [ , 'index_name' [ , fillfactor ] ] )     [ WITH NO_INFOMSGS ]

The following example shows a DBCC DBREINDEX command being run to rebuild all the indexes in the [WarZones] table:

 DBCC DBREINDEX ('WarZones') ;

This has the following output:

 DBCC execution completed. If DBCC printed error messages,    contact your system administrator.

Warning 

The DBCC DBREINDEX command is being deprecated by Microsoft and should not be used. Use the ALTER INDEX statement instead.

DBCC DROPCLEANBUFFERS

The DBCC DROPCLEANBUFFERS command removes all buffers from the buffer pool (SQL Server 2005’s data cache). It is predominantly used by database developers to flush SQL Server’s data cache when testing T-SQL query performance.

Tip 

You should execute the CHECKPOINT command before the DBCC DROPCLEANBUFFERS command to ensure that all dirty pages (data pages that have been modified in memory) from the current database are written to disk and thus able to be “flushed.”

The syntax for the DBCC DROPCLEANBUFFERS command is as follows:

 DBCC DROPCLEANBUFFERS [ WITH NO_INFOMSGS ]

The following example shows the DBCC DROPCLEANBUFFERS command being run:

 CHECKPOINT ; GO DBCC DROPCLEANBUFFERS ;

This has the following output:

 DBCC execution completed. If DBCC printed error messages,    contact your system administrator.

DBCC FREEPROCCACHE

The DBCC FREEPROCCACHE command frees SQL Server 2005’s procedure cache (memory used to store programming constructs such as stored procedures, query plans, and so on). Although developers sometimes use the DBCC FREEPROCCACHE command for testing purposes, it is also a useful command for DBAs because it causes all ad hoc T-SQL statements (and everything else of course) to be recompiled.

Tip 

Don’t forget to use the DBCC FREEPROCCACHE command as a troubleshooting option when users are experiencing poor query performance.

The syntax for the DBCC FREEPROCCACHE command is as follows:

 DBCC FREEPROCCACHE [ WITH NO_INFOMSGS ]

The following example shows the DBCC FREEPROCCACHE command being run:

 DBCC FREEPROCCACHE ;

This has the following output:

 DBCC execution completed. If DBCC printed error messages,  contact your system administrator.

DBCC INDEXDEFRAG

Traditionally, DBCC INDEXDEFRAG has been one of the least understood DBCCs.

The DBCC INDEXDEFRAG command defragments the leaf level of an index on a table or a view in a database so that the physical order of the pages matches the left-to-right logical order of the leaf pages. The DBCC INDEXDEFRAG command additionally compacts the data pages and removes any freed-up pages. This operation should improve the performance of table scans and range queries (which typically perform partial scans).

When DBCC INDEXDEFRAG is run, index defragmentation occurs serially. This means the operation on a single index is performed using a single thread. No parallelism occurs. Also, operations on multiple indexes from the same DBCC INDEXDEFRAG statement are performed on one index at a time.

Note 

The DBCC INDEXDEFRAG command runs as an online operation, where it quietly shuffles pages via one thread, which holds appropriate locks for a very short time. It can be stopped at any time.

Make no mistake, though; rebuilding an index will always be a far superior solution. However, the index rebuild process holds “stronger” locks for a greater period of time. This is why this command was added to earlier versions of SQL Server: to help those DBAs who truly ran a 24/7 environment and could not rebuild their indexes periodically. However, this paragraph is pretty much moot now, because SQL Server 2005 Enterprise Edition allows you to rebuild indexes online.

Tip 

Be aware that defragmenting an index can also take longer than simply rebuilding it, if the index is heavily fragmented. Start using this command when you have only a slight level of fragmentation. And run it often.

The syntax for the DBCC INDEXDEFRAG command is as follows:

 DBCC INDEXDEFRAG (     { 'database_name' | database_id | 0 }         , { 'table_name' | table_id | 'view_name' | view_id }     [ , { 'index_name' | index_id } [ , { partition_number | 0 } ] ] )     [ WITH NO_INFOMSGS ]

The following example shows a DBCC INDEXDEFRAG command being run against the [WarZones] table:

 DBCC INDEXDEFRAG ('WarZones') ;

This has the following output:

 Pages Scanned Pages Moved Pages Removed ------------- ----------- ------------- 2264448       664346      69 (1 row(s) affected) DBCC execution completed. If DBCC printed error messages,    contact your system administrator.

Warning 

The DBCC INDEXDEFRAG command is being deprecated by Microsoft and should not be used. Use the ALTER INDEX statement instead.

DBCC SHOWCONTIG

The DBCC SHOWCONTIG command shows the degree of both internal and external fragmentation for a table, index, or view. In a nutshell, internal fragmentation refers to how much of each data page is being used in the table, whereas external fragmentation refers to whether the pages that make up the table are sequentially located. External fragmentation is always bad. Internal fragmentation is not necessarily bad. Why? Because whenever you use a FILLFACTOR option when you create your table or indexes, you have deliberately internally fragmented your table. Of course, we assume that you used a fill factor for a particular reason.

This used to be one of the more important commands whose output an experienced DBA needed to know. So if query performance against a certain table is really slow, one of the things that an experienced DBA would examine is the degree of fragmentation of that table.

The following are the statistics produced by the output of the DBCC SHOWCONTIG command:

• Pages Scanned   Number of pages scanned.

• Extents Scanned   Number of extents scanned.

• Extent Switches   Number of times SQL Server 2005 switched from one extent to another while traversing the page chain.

• Avg. Pages per Extent   Average number of pages per extent in the page chain.

• Scan Density [Best Count: Actual Count]   Ratio of Best Count to Actual Count as a percentage. Best Count is the ideal number of extent switches. Actual Count is the actual number of extent switches.

  Tip 

  A Scan Density of 100 would indicate that everything is contiguous.

• Logical Scan Fragmentation   Percentage of pages that were out of order when the page chain of the leaf level was scanned.

  Note 

  This number is not relevant to heaps as they are effectively a “random collection of pages.”

• Extent Scan Fragmentation   Percentage of extents that were out of order when the page chain of the leaf level was scanned.

  Note 

  Again, this number is not relevant to heaps. Additionally, the Extent Scan Fragmentation statistic is meaningless if the index spans multiple files.

• Avg. Bytes Free per Page   Average number of free bytes on the pages scanned. Don’t forget to take into account your row size when looking at this statistic!

• Avg. Page Density (full)   Average amount of space used by the pages scanned. Likewise, don’t forget to take into account your row size when looking at this statistic!

So, the Avg Page density (full) and the Avg Bytes Free per Page statistics effectively show the degree of internal fragmentation discussed earlier. Conversely, the Scan Density [Best Count: Actual Count] statistic shows the degree of external fragmentation

The syntax for the DBCC CHECKCATALOG command is as follows:

 DBCC SHOWCONTIG [ (     { 'table_name' | table_id | 'view_name' | view_id }     [ , 'index_name' | index_id ] ) ]     [ WITH         {          [ , [ ALL_INDEXES ] ]          [ , [ TABLERESULTS ] ]          [ , [ FAST ] ]          [ , [ ALL_LEVELS ] ]          [ NO_INFOMSGS ]          }     ]

The FAST option indicates that SQL Server 2005 should not read the leaf- or data-level page or data pages of the index. This will result in a faster scan, although it will output minimal information.

Note 

Although the fast scan will take less time, DBCC SHOWCONTIG will still acquire a Shared lock on the table.

The following example shows a DBCC SHOWCONTIG command being run against the [WarZones] table:

 DBCC SHOWCONTIG ('WarZones') ;

This has the following output:

 DBCC SHOWCONTIG scanning 'WarZones' table... Table: 'WarZones' (69585374); index ID: 1, database ID: 69 TABLE level scan performed. - Pages Scanned................................: 2264448 - Extents Scanned..............................: 285485 - Extent Switches..............................: 287093 - Avg. Pages per Extent........................: 7.8 - Scan Density [Best Count:Actual Count].......: 98.69% [283057:287094] - Logical Scan Fragmentation ..................: 0.06% - Extent Scan Fragmentation ...................: 3.69% - Avg. Bytes Free per Page.....................: 6963.8 - Avg. Page Density (full).....................: 13.96% DBCC execution completed. If DBCC printed error messages, contact your system administrator.

In this particular example, there does not seem to be any problems with external fragmentation, but we certainly should be concerned about the internal fragmentation! We seem to be using only 13.96 percent of each page, which is an extremely inefficient use of pages and thus the precious buffer pool. It is definitely time to reindex this table, thereby dramatically decreasing the amount of pages it is using, which will dramatically improve table scans and larger-range queries.

Warning 

After all of that, we should add that the DBCC SHOWCONTIG command is being deprecated by Microsoft and should not be used. Use the sys.dm_db_index_ physical_stats DMF instead.

DBCC SHRINKDATABASE

The DBCC SHRINKDATABASE command shrinks the size of the data and log files of a database to a target percentage of free space. That’s a weird way to do it if you ask us….

The syntax for the DBCC SHRINKDATABASE command is as follows:

 DBCC SHRINKDATABASE ( 'database_name' | database_id | 0      [ ,target_percent ]      [ , { NOTRUNCATE | TRUNCATEONLY } ] ) [ WITH NO_INFOMSGS ]

The NOTRUNCATE and TRUNCATEONLY options manipulate the data files only. The NOTRUNCATE option compacts the data of the database only by moving pages allocated at the end of the file to the beginning. Consequently, the size of the database files will remain unchanged. The TRUNCATEONLY option truncates the data files only up to the last allocated extent. It does not perform any page movement at all.

The DBCC SHRINKDATABASE command is pretty much a hit-or-miss affair. Either it does it or it doesn’t do it. Personally, we have not really used it that much over the past decade or so.

Tip 

If you want finer control over how you would like the database to be shrunk, you should use the DBCC SHRINKFILE command instead.

The following example shows the DBCC SHRINKDATABASE command being used to reduce the free space in the UnitedNations database to 33 percent:

 DBCC SHRINKDATABASE ('UnitedNations', 33) ;

This has the following output:

 DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages ------ ----------- ----------- ----------- ----------- ------------------ 69     1           1835439     1835439     1829386     1829386 DBCC execution completed. If DBCC printed error messages,    contact your system administrator.

Warning 

The DBCC SHRINKDATABASE command can increase the internal fragmentation of the database.

DBCC SHRINKFILE

The DBCC SHRINKFILE command shrinks the size of specific data or log files of a database to a target size. Now this one makes sense!

Importantly, you can use the DBCC SHRINKFILE command to shrink a file to a size that is less than its initial size when it was created. This resets the minimum file size to the new value.

The syntax for the DBCC SHRINKFILE command is as follows:

 DBCC SHRINKFILE (     { 'file_name' | file_id }     { [ , EMPTYFILE ]     | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]     } ) [ WITH NO_INFOMSGS ]

We particularly like the EMPTYFILE option, which empties the specified file by moving all the data from it to other files in the same file group. This is typically done when you want to remove a file from the database.

Again, the NOTRUNCATE and TRUNCATEONLY options manipulate the data files only. As with the DBCC SHRINKDATABASE command, the NOTRUNCATE option compacts the data of the database only by moving pages allocated at the end of the file to the beginning, and the TRUNCATEONLY option truncates the data files only up to the last allocated extent.

The following example shows the DBCC SHRINKFILE command being used to empty the UnitedNations_Data666 data file:

 DBCC SHRINKFILE ('UnitedNations_Data666', EMPTYFILE) ;

This has the following output:

 DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages ---- ------ ----------- ----------- --------- -------------- 69   667    135674      135674      0         0 DBCC execution completed. If DBCC printed error messages,    contact your system administrator.

Warning 

The DBCC SHRINKFILE command can also increase the internal fragmentation of the database.

DBCC UPDATEUSAGE

The DBCC UPDATEUSAGE command correct the database catalog (system tables of a database). The DBCC UPDATEUSAGE command specifically updates inaccuracies in the data pages, leaf pages, rows, reserved pages, and used pages of the relevant catalog views (including sys.allocation_units, sys.dm_db_partition_stats, sys.indexes, and sys.partitions). These inaccuracies are typically seen in the output of the sp_spaceused system stored procedure.

According to Microsoft, “these values are always maintained correctly” by SQL Server 2005. But at the same time, Microsoft also says that in SQL Server 2005 “DBCC CHECKDB has been enhanced to detect when page or row counts become negative. When detected, the DBCC CHECKDB output contains a warning and a recommendation to run DBCC UPDATEUSAGE to address the issue.” Arrgh!

Tip 

You should run DBCC UPDATEUSAGE on any SQL Server 2000 solutions (or data-bases) that have been upgraded to SQL Server 2005 to correct any potential inaccuracies.

The syntax for the DBCC UPDATEUSAGE command is as follows:

 DBCC UPDATEUSAGE           (     { 'database_name' | database_id | 0 }     [ , { 'table_name' | table_id | 'view_name' | view_id }     [ , { 'index_name' | index_id } ] ]           ) [ WITH [ NO_INFOMSGS ] [ , ] [ COUNT_ROWS ]     ]

The following example shows the DBCC UPDATEUSAGE command being run against the UnitedNations database:

 DBCC UPDATEUSAGE ('UnitedNations') ;

This has the following output:

 DBCC execution completed. If DBCC printed error messages,    contact your system administrator.

Real World Scenario-Running DBCC Checks in a VLDB Environment

The DBCC checks can take a long time to run. And let’s face it, no one’s database is getting smaller. And not everyone can afford the Enterprise Edition of SQL Server 2005 (where DBCC checks can take advantage of parallelism or alternatively you can take advantage of partitioning and file groups). In any case, eventually every VLDB will come across the scenario where the DBCC checks required are taking longer than the maintenance window available, given the available hardware. So, what do you do then?

What we recommend for certain clients with these sorts of VLDBs is to break up and spread their DBCC checks over the course of a week. It obviously depends on the client’s database and other environmental factors, but the idea is to split up your database into a number of sets of tables that are equal in size and run DBCC CHECKTABLE on those sets in a rotational basis.

An example of this would be something like this:

On Saturday/Sunday, run the following:

• DBCC CHECKDB

On Monday, run the following:

• DBCC CHECKALLOC

• DBCC CHECKCATALOG

• DBCC CHECKTABLE on the first set of tables

On Tuesday, run the following:

• DBCC CHECKALLOC

• DBCC CHECKCATALOG

• DBCC CHECKTABLE on the second set of tables

On Wednesday, run the following:

• DBCC CHECKALLOC

• DBCC CHECKCATALOG

• DBCC CHECKTABLE on the third set of tables

On Thursday, run the following:

• DBCC CHECKALLOC

• DBCC CHECKCATALOG

• DBCC CHECKTABLE on the fourth set of tables

On Friday, run the following:

• DBCC CHECKALLOC

• DBCC CHECKCATALOG

• DBCC CHECKTABLE on the fifth set of tables

You get the idea. The reason you run DBCC CHECKALLOC and DBCC CHECKCATALOG on the workdays is that those commands should not consume too much time.

As we said, it’s important for you to understand the various DBCCs, so you’ll examine them in more detail in Exercise 5.1.

Exercise 5.1: Running Database Consistency Checks

In this exercise, you’ll examine the output of the DBCC check commands in more detail.

1. Open SQL Server Management Studio, and connect to your SQL Server 2005 instance using Windows authentication.

2. Click the New Query toolbar button to open a new query window.

3. Let’s first check the allocation structures of the AdventureWorks database. Type the following Transact-SQL (T-SQL) code, execute it, and observe the results:

    USE master ; GO DBCC CHECKALLOC ('AdventureWorks')

4. The next thing you’ll check is the system tables (or the metadata) of the AdventureWorks database. Type the following T-SQL code, execute it, and observe the results:

    USE master ; GO DBCC CHECKCATALOG ('AdventureWorks') ;

5. In certain cases, you might need to check the integrity of only a specific table, so here you’ll check the [Person].[Contact] table of the AdventureWorks database. Type the following T-SQL code, execute it, and observe the results:

    USE AdventureWorks ; GO DBCC CHECKTABLE ('[Person].[Contact]') ;

6. In most cases you would typically check the integrity of your entire database, so now you’ll check the AdventureWorks database. Execute the following T-SQL script in the query pane, and observe the results:

    USE master ; GO DBCC CHECKDB ('AdventureWorks') ;

7. Another important performance troubleshooting technique is to examine the level of both internal and external fragmentation of a table, because this could be the cause of poor query performance and indicate a need to rebuild the appropriate indexes. To check the fragmentation level of the [Person].[Contact] table of the AdventureWorks data-base, type the following T-SQL code, execute it, and observe the results:

    USE AdventureWorks ; GO DBCC SHOWCONTIG ('[Person].[Contact]')

8. As discussed earlier, however, you should be querying sys.dm_db_index_physical_ stats in lieu of using the DBCC SHOWCONTIG command. Type the following T-SQL code, execute it, and observe the results:

    USE AdventureWorks ; GO SELECT * FROM sys.dm_db_index_physical_stats (     DB_ID('AdventureWorks'), OBJECT_ID('Person.Contact'),    NULL, NULL, 'DETAILED' ) ;

9. We personally still prefer to run DBCC SHOWCONTIG, but that’s because we’re creatures of habit.

Creating a Job Dependency Diagram

As part of your database maintenance strategy, you will have to assess and document the system dependencies of your database solution. The end result is to document a job dependency diagram. You can use the job dependency diagram to avoid conflicts and coordinate various jobs efficiently.

A job dependency diagram typically has a number of inputs defined, which are resources that provide value to a process, and outputs, which are the result of the process. It is important to clearly define the inputs and outputs of a job, as well as to keep the document current.

It also is important to identify the appropriate level of detail for a job dependency diagram. Your granularity could be at the job level and show interjob dependencies. Alternatively, you could show the specific steps within the jobs to reflect particular process dependencies.

Note 

Typically, the more granular approach is used, where you determine the specific steps.

At the enterprise level, you need to potentially coordinate with other activities to avoid conflicts. For example, you could have an enterprisewide backup solution scheduled to start at midnight on Sunday. It would not make sense to schedule a database backup across the network at the same time because you would potentially saturate the network. Alternatively, you could have end-of-month or close-of-business batch processes running periodically. It would not make sense to schedule a database consistency check during these CPU- and I/O-intensive batch processes.

When creating your job dependency diagram, consider the following guidelines:

• Interjob dependencies   You will need to identify any dependencies between when jobs can execute in your database solution. For example, you could not process your data warehouse until the day’s sales data has finished loading into it.

  Note 

  Don’t forget that sometimes dependencies might be at the step level and not the job level.

• Other systems   You will also need to identify other systems that might affect your work and determine what steps you need to take to avoid conflicting with other business processes and/ or staff.

• Job concurrency restrictions   It is important to identify any restrictions on executing certain jobs concurrently. This is typically based on the resources that the jobs will be accessing. Jobs can be classified as one of the following:

  • Fully compatible   Multiple tasks that can run concurrently without adversely affecting each other are fully compatible. For example, you could run two jobs that truncate different tables concurrently.

  • Partially compatible   Multiple tasks that could run concurrently but where it would be preferable not to, for resource or efficiency reasons, are partially compatible. For example, a reindexing job in one database can run concurrently with a DBCC check in another data-base, but it would be preferable not to run them concurrently because of the high CPU and particularly I/O activity expected at the server level.

  • Incompatible   Multiple tasks that cannot run concurrently are incompatible. For example, you cannot reindex a table at the same time that you truncate it.

    Note 

    What tool you use to create a job dependency diagram is irrelevant, although we are sure Microsoft would recommend Microsoft Visio.

Managing the Maintenance of Database Servers

As a DBA, in most organizations you will be responsible for maintaining a number of SQL Server instances, to say the least! Microsoft has several tools that you can leverage in such organizations.

Multiserver Administration

Let’s face it, you commonly perform a number of tasks on multiple instances of SQL Server, such as running database consistency checks and backups on your system databases, resetting passwords, and so forth. In most cases, these tasks are conveniently scheduled at the same frequency across multiple SQL Server instances. A lot of organizations schedule their user data-bases to be backed up at the same time. Again, the point is that it is a similar task scheduled at a common time.

Since SQL Server 7.0, Microsoft has given you the capability of scheduling the same job on multiple SQL Server instances at the same time from a central location. This is referred to as multiserver administration.

Servers play two different roles in this paradigm-the master server (MSX) and the target server (TSX). The master server is responsible defining and distributing the jobs to the target servers, whereas the target servers are responsible for sending the status of their scheduled tasks as they execute. The target servers periodically poll the master server for any new jobs to download. Figure 5.1 shows this relationship between the master server and the target servers.

Figure 5.1: Multiserver administration

Implementing multiserver administration is actually very simple. First the DBA of a target server enlists in the master server. This then gives the master server complete control over which jobs get executed on the target server.

Note 

You must have at least one master server and one target server when configuring your multiserver administration environment.

After you have created this multiserver administration environment, you can both add and remove target servers.

Note 

For more information about how to implement a multiserver administration environment, search for the “Creating a Multiserver Environment” topic in SQL Server 2005 Books Online.

Microsoft SQL Server Health and History Tool (SQLH2)

The SQL Server Health and History Tool (SQLH2) was developed by the SQL Server product group to allow you to collect information from SQL Server instances, store the collected information, and run reports against this information to determine how SQL Server 2005 is being used. You can add this collected information to your run book.

SQLH2 basically collects four main types of information:

• Feature usage   What services and features are installed and running, and the level of workload on the service

• Configuration settings   Computer, operating system, and SQL Server 2005 configuration settings; SQL Server instance and database metadata

• SQL Server uptime   The uptime of the SQL Server instance

• Performance counters   Optional information collected that helps you to determine performance trends

In addition to collecting this information, SQLH2 stores this data, enabling the DBA to generate reports. At a fine level, the SQLH2 collects the following key information:

Server-level information   This includes information related to the server, including the following:

• Total physical memory

• Processor information (including the number of processors, processor status, and clock speed)

• Disk drive information (including capacity, and number of cylinders and heads)

• Network card information (including MAC address, speed, and TCP/IP drivers)

• Installed products information (including product name and installation date)

Instance-level information   This includes information related to the SQL Server 2005 instance, including the following:

• Instance information (including the version, build, and edition)

• Database information (including the files, file groups and space used)

• Installed services information (including the name, state, and security credentials)

• Summary information of the database objects

Note 

You can download the latest version of SQLH2 from http://www.microsoft.com/downloads/details.aspx?FamilyID=&DisplayLang=en.

Tip 

When installing SQLH2, make sure you read all the instructions for its installation because it does not have the usual installation engine found in most “commercial” software.

SQLH2 works by using a system task to collect the server-level and instance-level information discussed earlier and store it in a local SQLH2 database. You can configure SQLH2 to collect information from various SQL Server instances, controlling both the frequency and the performance counters to capture. For these performance counter metrics, a separate service installs on the server locally and is used to collect and store the metrics as local text files.

SQLH2 can help you audit your SQL Server instance settings and report on trends over a period of time. You can use this collected set of metrics to analyze the effect of configuration and application changes, thereby proactively fixing issues before they bite you!

Warning 

Microsoft does not officially support SQLH2.

In addition, several other components make up the overall SQLH2 tool set.

SQLH2 Performance Collector

The SQLH2 Performance Collector is an optional tool that collects performance counters that are consumed by the SQLH2 Collector. It is a stand-alone service that collects and stores performance counter data from selected servers. You should install this optional component if you are interested in collecting performance counters along with system information. The SQLH2 Collector gathers the data that this service collects and stores this data in the repository when you run the SQLH2 Performance Collector.

Note 

You can download the latest version of the SQLH2 Performance Collector from http://www.microsoft.com/downloads/details.aspx?familyid=&displaylang=en.

SQLH2 Reports

The SQLH2 Reports tool allows you to view the data collected by SQLH2. The SQLH2 Reports tool requires Microsoft’s SQL Server Reporting Services to display the reports. You can customize the reports with the Reporting Services designer if you want.

Note 

You can download the latest version of SQLH2 Reports from http://www.microsoft.com/downloads/details.aspx?FamilyID=&DisplayLang=en.