concept index fragmentation in category sql server

This is an excerpt from Manning's book Learn Windows SQL Server Administration in a Month of Lunches. Login to get full access to this book.

Figure 10.2. Index fragmentation comes from page splits.

Fragmentation occurs to all indexes on tables where data is being added, deleted, or changed; the only time fragmentation isn’t an issue is for a table that is entirely read-only. Not many databases have purely read-only tables, so index fragmentation is one of the biggest things that you, as a database maintenance person, need to worry about.

This is an excerpt from Manning's book SQL Server 2008 Administration in Action. Login to get full access to this book.

Listing 13.9. Displaying index fragmentation

-- View index fragmentation ordered by fragmentation level SELECT stats.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats( DB_ID(N'AdventureWorks'), NULL, NULL, NULL, NULL ) as stats INNER JOIN sys.indexes AS b ON stats.object_id = b.object_id AND stats.index_id = b.index_id ORDER BY avg_fragmentation_in_percent DESC

copy