594
Part V: Enterprise Data Management
DBCC CHECKDB also runs DBCC CHECKALLOC, DBCC CHECKTABLE, and DBCC CHECKCATALOG, so you
do not need to separately run these commands if you execute DBCC CHECKDB. However, DBCC CHECKDB does
not run DBCC CHECKCONSTRAINTS; run this separately if your tables have one or more constraints.Index Maintenance
Indexes provide the performance bridge between the data and SQL queries. Because of data
inserts, updates, and deletes, indexes fragment, the data-distribution statistics become
out of date, and the fi ll factor of the pages can be less than optimal. Index maintenance is
required to combat these three results of normal wear and tear and to prevent performance
reduction.Chapter 7, “Relational Database Design and Creating the Physical Database Schema,” and Chapter 45,
“Indexing Strategies,” both contain more information on index creation.Database Fragmentation
By default, as data is inserted into the data pages and index pages, the pages fi ll to 100
percent. At that point, SQL Server performs a page split, creating two new pages with
about 50 percent page density each. Although this solves the individual page problem, the
internal database structure can become fragmented.To demonstrate the DBCC commands that affect fragmented tables and indexes, a table
large enough to become fragmented is required. The following script builds a suitable table
and a nonclustered index. The clustered primary key is a GUID, so row insertions can occur
throughout the table, generating plenty of fragmentation:USE Tempdb;CREATE TABLE Frag (
FragID UNIQUEIDENTIFIER NOT NULL DEFAULT NewID(),
Col1 INT,
Col2 CHAR(200),
Created DATETIME DEFAULT GetDate(),
Modified DATETIME DEFAULT GetDate()
);ALTER TABLE Frag
ADD CONSTRAINT PK_Frag
PRIMARY KEY CLUSTERED (FragID);CREATE NONCLUSTERED INDEX ix_col
ON Frag (Col1);c22.indd 594c22.indd 594 7/31/2012 9:24:30 AM7/31/2012 9:24:30 AM
http://www.it-ebooks.info