Index structure and Types -- Part 3 : Index Creation & Detecting Fragmentation

Index Creation Tasks

The following tasks make up our recommended strategy for creating indexes:

1.  Design the index.

Index design is a critical task. Index design includes determining which columns to use, selecting the index type (for example, clustered or nonclustered), selecting appropriate index options, and determining filegroup or partition scheme placement.

2.  Determine the best creation method. Indexes are created in the following ways:

·         By defining a PRIMARY KEY or UNIQUE constraint on a column by using CREATE TABLE or ALTER TABLE

The SQL Server 2005 Database Engine automatically creates a unique index to enforce the uniqueness requirements of a PRIMARY KEY or UNIQUE constraint. By default, a unique clustered index is created to enforce a PRIMARY KEY constraint, unless a clustered index already exists on the table, or you specify a unique nonclustered index. By default, a unique nonclustered index is created to enforce a UNIQUE constraint unless a unique clustered index is explicitly specified and a clustered index on the table does not exist.

Index options and index location, filegroup or partition scheme, can also be specified.

An index created as part of a PRIMARY KEY or UNIQUE constraint is automatically given the same name as the constraint name.

·         By creating an index independent of a constraint by using the CREATE INDEX statement, or New Index dialog box in SQL Server Management Studio Object Explorer

You must specify the name of the index, table, and columns to which the index applies. Index options and index location, filegroup or partition scheme, can also be specified. By default, a nonclustered, nonunique index is created if the clustered or unique options are not specified.

3.  Create the index.

Whether the index will be created on an empty table or one that contains data is an important factor to consider. Creating an index on an empty table has no performance implications at the time the index is created; however, performance will be affected when data is added to the table.

Creating indexes on large tables should be planned carefully so database performance is not hindered. The preferred way to create indexes on large tables is to start with the clustered index and then build any nonclustered indexes. Consider setting the ONLINE option to ON when you create indexes on existing tables. When set to ON, long-term table locks are not held enabling queries or updates to the underlying table to continue.

Detecting Fragmentation

The first step in deciding which defragmentation method to use is to analyze the index to determine the degree of fragmentation. By using the system function sys.dm_db_index_physical_stats, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases. For example, the percentage of logical fragmentation (out-of-order pages in an index) is displayed in the avg_fragmentation_in_percent column. For partitioned indexes, sys.dm_db_index_physical_stats also provides fragmentation information for each partition.
The result set returned by this function includes the following columns.

Column	Description
avg_fragmentation_in_percent	The percent of logical fragmentation (out-of-order pages in the index).
fragment_count	The number of fragments (physically consecutive leaf pages) in the index.
avg_fragment_size_in_pages	Average number of pages in one fragment in an index.

After the degree of fragmentation is known, use the following table to determine the best method to correct the fragmentation.

avg_fragmentation_in_percent value	Corrective statement
< = 30%	ALTER INDEX REORGANIZE
> 30%	ALTER INDEX REBUILD WITH (ONLINE = ON)*

*Reorganizing an index is always executed online, although rebuilding an index can be executed online or offline. To achieve availability similar to the reorganize option, you should rebuild indexes online.
For example, execute the following statement.

USE AdventureWorks; GO SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),      NULL, NULL, NULL) AS a     JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id; GO

The statement might return a result set similar to this.

index_id    name          avg_fragmentation_in_percent -------- ---------------- ---------------------------- 1        PK_Product_ProductID        23.076923076923077 2        AK_Product_ProductNumber    50.0 3        AK_Product_Name             66.666666666666657 4        AK_Product_rowguid          50.0 (4 row(s) affected)

By using the previous table, the recommended resolution is to reorganize PK_Product_ProductID and rebuild the other indexes.
To detect fragmentation, the sys.dm_db_index_physical_stats dynamic management function should be used instead of DBCC SHOWCONTIG.
you can also find detecting fragmentation query from following link
 optimize query using indexes index