Index Fragmentation for SQL Server

RE: Microsoft's MCTS 70-431 certification for SQL SErver 2005 : Fragementation Section

This section was horribly boring until I figured out a way to create fragementation on a table in a database.
Here's how I did it:

-- create database called repository
create database repository
go
-- create table:
USE [repository]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmp]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[tmp]
(
[id]
[int] IDENTITY(1,1) NOT NULL,
[field] [varchar](64) NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)
WITH
(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [repository]
)
ON [repository]
END

THEN:

1. I inserted 10,000 rows of random string data in the [field] column (I used Redgate's Data generator.. fantastic time-cutting tool)
2. I truncated some data to be smaller (random rows)
3. I added string data to be larger (random rows)
4. I deleted random rows.

What I got:

• I had fragmentation to play with (yay!).

THEN:

1. I did an alter on the ALTER index REORGANIZE. (reduced fragmentation by 1/2)
2. Then I used the alter index... rebuild. (yes, you guessed it: ALTER INDEX...REBUILD thwacks all of the fragmentation & performance is improved).

WHEN SHOULD I DEFRAG MY INDEXES?

here is a system tool to peek into what kind of fragmentation you have:

select * from sys.dm_db_index_physical_stats
-- (this system view is a "dynamic management function" = DMF. yet another acronymn to learn. yippee)

Do a INDEX....REORGANIZE if:

avg_page_space_used_in_percent is between 60 and 75
or
avg_avg_fragmentation_in_percent between 10 and 15

Do a INDEX....REBUILD if:

avg_page_space_used_in_percent is greater than 60
or
avg_fragmentation_in_percent is greater than 15

This is also a procedure I use to analyze the fragmentation (by database). I threw this together from a few sources & use it regularly:

EXEC dbo.sp_executesql @statement = N'
ALTER procedure [dbo].[database_fragmentation_check]
@database sysname
as
select object_name(dt.object_id),
si.name,
dt.avg_fragmentation_in_percent,
dt.avg_page_space_used_in_percent
from ( select object_id,
index_id, avg_fragmentation_in_percent,
avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(@database),
null,
null,
null,
''detailed'')
where index_id <> 0
) as dt -- does not return information about heaps
inner join sys.indexes si on si.object_id = dt.[object_id]
and si.index_id = dt.index_id
'