SQL Server Table Fragmentation

USE AdventureWorks;
GO
DBCC SHOWCONTIG (‘HumanResources.Employee’)
GO

DBCC SHOWCONTIG scanning ‘Employee’ table…
Table: ‘Employee’ (869578136); index ID: 1, database ID: 6
TABLE level scan performed.
– Pages Scanned…………………………..: 7
– Extents Scanned…………………………: 3
– Extent Switches…………………………: 2
– Avg. Pages per Extent……………………: 2.3
– Scan Density [Best Count:Actual Count]…….: 33.33% [1:3]
– Logical Scan Fragmentation ………………: 14.29%
– Extent Scan Fragmentation ……………….: 33.33%
– Avg. Bytes Free per Page…………………: 172.6
– Avg. Page Density (full)…………………: 97.87%

  • sys.dm_db_index_physical_stats DMV example:

USE AdventureWorks
GO
SELECT object_id, index_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(‘AdventureWorks’), OBJECT_ID(‘HumanResources.Employee’), NULL, NULL, NULL);

http://msdn.microsoft.com/en-us/library/ms189858.aspx

As a guideline from above

 

avg_fragmentation_in_percent value Corrective statement
> 5% and < = 30% ALTER INDEX REORGANIZE
> 30% ALTER INDEX REBUILD WITH (ONLINE = ON)*

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: