Defragmenting Indexes in SQL Server 2005

There are several things that should be done on a regular basis and one of these things is to manage database fragmentation. Depending on the tables, queries and indexes that are being used fragmentation can cause performance issues as well as using unnecessary space in the database. Database fragmentation is similar to disk fragmentation in that the data is stored in various places in the database file instead of sequentially or next to like data within the database. This often occurs where you have non-sequential keys and the constant inserting, updating and deleting of data causes the data to become fragmented as well as the use of additional data pages to store the data.
As the indexes are modified, the information stored in them becomes fragmented, resulting in the information being scattered across the data files. When this occurs, the logical ordering of the data no longer matches the physical ordering, which can lead to a deterioration of query performance.
To fix this problem, indexes must be periodically reorganized or rebuilt (defragmented) so the physical order of the leaf-level pages matches the logical order of the leaf nodes.

So where is the information stored?
To analyze SQL Server 2005 or 2008 indexes, you use the system function sys.dm_db_index_physical_stats to determine which indexes are fragmented and the extent of that fragmentation.

The sys.dm_db_index_physical_stats function returns a number of values that provide details about the indexes you specify. avg_fragmentation_in_percent: Percentage of the logical index that is fragmented.
fragment_count: Number of fragments in the leaf level.
avg_fragment_size_in_pages: Average number of pages in a leaf-level fragment.
page_count: Number of index or data pages.
An index always has at least one fragment (fragment_count). The maximum number of fragments that an index can have is equal to the number of pages (page_count).
Ideally, the avg_fragmentation_in_percent value should be as close to zero as possible, and the avg_fragment_size_in_pages should be as high as possible.
Microsoft recommends that you reorganize your index if the avg_fragmentation_in_percent value is less than or equal to 30% and rebuild the index if the value is greater than 30%.
Reorganizing Indexes
When you reorganize an index, SQL Server physically reorders the leaf-level pages to match the logical order of the leaf nodes. The process uses the existing pages only and does not allocate new ones, but it does compact the index pages. In addition, reorganization uses minimal resources and is automatically performed online, without blocking queries or updates. You should reorganize indexes only if they’re lightly fragmented, otherwise, you should rebuild them
ALTER INDEX PK_StoreContact_CustomerID_ContactID ON AdventureWorks.Sales.StoreContact
REORGANIZE

Rebuilding Indexes
Rebuilding an index is generally more effective than reorganizing it because it drops the original index and builds a new one. As a result, the index starts clean with minimal fragmentation and the pages are compacted, and new pages are allocated as needed. In addition, you can choose to rebuild an index offline (the default) or online.
ALTER INDEX IX_StoreContact_ContactID ON
AdventureWorks.Sales.StoreContact
REBUILD

SQL Server script to rebuild all tables which are fragmented.
-- =============================================
-- Author: Avishkar Meshram
-- Create date: 11/26/2009
-- Description: Check fragmentation in table and rebuild table
-- =============================================
CREATE PROCEDURE [dbo].[RebuildFragmentedTables]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @dbid smallint;
DECLARE @FragCount smallint; -- Fragmentation level in table
SET @cmd = 30
Create table #RebuildTable
(
Table_Name varchar(50)
)
-- Gather tables which are fragmented
INSERT INTO #RebuildTable
SELECT OBJECT_NAME(OBJECT_ID)
FROM sys.dm_db_index_physical_stats(DB_ID(),
NULL, NULL, NULL , 'DETAILED')
WHERE avg_fragmentation_in_percent > 30

SET @cmd = 'DECLARE TableCursor CURSOR FOR
SELECT Distinct Table_Name
FROM #RebuildTable'
-- Rebuild only those tables which are fragmented
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD'
PRINT (@cmd)
--EXEC (@cmd)
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
DROP TABLE #RebuildTable
END