Ankith


Hi:

when does a table not show any Logical fragmentation

1). Is it possible if the table has only non-clustered indexes and no clustered indexes, it wont show any logical fragmentation at all or shows less logical fragmentation .

Also what is the difference between logical fragmentation and Physical fragmentation of a index .Is there actually something like a physical fragmentation for a index

Thanks

AK




Re: Logical fragmentation

Ankith


An example repro T-SQL Script would be helpful. Experts please comment.




Re: Logical fragmentation

Sherry Li from MSFT

Logical fragmentation only applies to indexes (clustered or non-clustered). If the physical location of key orders from leaf level of an index is the same as physcial page order in the database files, then logical fragmentation is 0. You can find more information in Book Online from DBCC SHOWCONTIG in SQL Server 2000/2005 and sys.dm_db_index_physcial_stats in SQL Server 2005.

To answer your first question, if a table does not have clustered index (it is heap), then SQL Server cannot do a order scan on the data. Heap will have 0 logical fragmentation; but your query performance may not be good. Logical fragmentation is independent from different indexes/heaps. You may see logical fragmentation from non-clustered indexes.

From SQL Server point of view, it has no control of physcial fragmentation (aka file fragmentation for a database file). The physical fragmentation is from OS when allocating space for database files. It does not know if an index has physcial fragmentation.

I suggest that you read the following white paper ("Microsoft SQL Server 2000 Index Defragmentation Best Practices") to gain more ideas about logical fragmentation and file fragmentation: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

Thanks

Sherry