In SQL Server 2005 and later version, you can use sys.dm_db_index_physical_stats to query size and fragmentation information for the data and indexes. http://msdn.microsoft.com/zh-cn/library/ms188917.aspx

In SQL Server 2000, you could use DBCC SHOWCONTIG .http://msdn.microsoft.com/en-us/library/ms175008.aspx

The main effect of fragmentation is that it slows down page read-ahead throughput during index scans. This causes slower response times.(When you query a fragmentated tables, SQL engine need scan more pages, after

defragmentation, it will scan less pages which reduce I/O and memory utilization).