How to defragment data with MSSQL
- With Microsoft SQL Server 2000, Microsoft SQL Server 2005 you can check how fragmented is your data with the DBCC SHOWCONTIG see: How fragmented is the data with MSSQL.
- Microsoft SQL Server 2005 introduced the dynamic management view: SYS.DM_DB_INDEX_PHYSICAL_STATS.
- SYS.DM_DB_INDEX_PHYSICAL_STATS shows the fragmentation for all the tables in the database being queried.
- Check: How fragmented is the data with MSSQL and How fragmented is the data with MSSQL 2005
- But how do you actually defragment the data file?
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
and somewhat to
- Microsoft SQL Server 2008. Microsoft SQL Server 2008 does not support the DBCC SHOWCONTIG to display the verification of the defragmentation of the data file.
Data used
- USE sql911;
- go
- SELECT CAST(DB_NAME(database_id) AS VARCHAR(15)) AS 'databasename',
- CAST(OBJECT_NAME([OBJECT_ID]) AS VARCHAR(15)) AS 'tablename',
- CAST(index_type_desc AS VARCHAR(20)) AS index_type_desc,
- avg_fragmentation_in_percent AS '% fragmentation',
- fragment_count '# fragments',
- avg_fragment_size_in_pages 'Avg frag size in pages'
- FROM sys.dm_db_index_physical_stats (DB_ID('sql911'),null,null,null,null );
- go
databasename tablename index_type_desc % fragmentation # fragments Avg frag size in pages --------------- --------------- -------------------- ---------------------- -------------------- ---------------------- sql911 EMP_WORK HEAP 0 1 1 sql911 DEPARTMENT HEAP 0 1 1 sql911 ORG CLUSTERED INDEX 0 1 1 sql911 Nums CLUSTERED INDEX 0.415183867141162 9 187.333333333333 sql911 sales02 CLUSTERED INDEX 66.6666666666667 3 1 sql911 trans01 HEAP 0 1 1 sql911 trans02 HEAP 0 1 1 sql911 HttpLogs HEAP 0 0 0 sql911 logs HEAP 0 0 0 sql911 sales HEAP 0 1 1 sql911 projects CLUSTERED INDEX 0 1 1 sql911 Emps HEAP 0 1 1 (12 row(s) affected)
We can see that the table sales02's index is 66% fragmented. How to fix it?
Fragmentation
- USE sql911;
- go
- DBCC showcontig("sales02") WITH NO_INFOMSGS ;
- go
DBCC SHOWCONTIG scanning 'sales02' table... Table: 'sales02' (309576141); index ID: 1, database ID: 10 TABLE level scan performed. - Pages Scanned................................: 3 - Extents Scanned..............................: 2 - Extent Switches..............................: 1 - Avg. Pages per Extent........................: 1.5 - Scan Density [Best Count:Actual Count].......: 50.00% [1:2] - Logical Scan Fragmentation ..................: 66.67% - Extent Scan Fragmentation ...................: 50.00% - Avg. Bytes Free per Page.....................: 7616.0 - Avg. Page Density (full).....................: 5.91%
- SELECT a.index_id,
- CAST(name AS VARCHAR(15)) AS 'Index name',
- avg_fragmentation_in_percent
- FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('sales02'),
- null, null, null) AS a
- join sys.indexes AS b ON a.OBJECT_ID = b.OBJECT_ID and a.index_id = b.index_id;
- go
index_id Index name avg_fragmentation_in_percent ----------- --------------- ---------------------------- 1 PK_sales_02 66.6666666666667 (1 row(s) affected)
This shows which index is fragmented.
- If the fragmentation is less than 5%, don't bother defragmenting. You will NOT get any improvement.
- If the fragmentation is more than 5%, then you can start thinking about the defragmentation.
- Only the enterprise edition can rebuild indexes online. On a standard edition, you will get the following message:
- ALTER INDEX pk_sales_02 ON sales02
- rebuild WITH (online = ON);
- Msg 1712, LEVEL 16, STATE 1, Line 3
- Online INDEX operations can ONLY be performed in Enterprise edition OF SQL Server.
Defragmentation
- ALTER INDEX all ON sales02
- rebuild WITH (FILLFACTOR = 80, sort_in_tempdb = ON);
- SELECT a.index_id,
- CAST(name AS VARCHAR(15)) AS 'Index name',
- avg_fragmentation_in_percent
- FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('sales02'),
- null, null, null) AS a
- join sys.indexes AS b ON a.OBJECT_ID = b.OBJECT_ID and a.index_id = b.index_id;
- go
index_id Index name avg_fragmentation_in_percent ----------- --------------- ---------------------------- 1 PK_sales_02 0 (1 row(s) affected)
Verification of defragmentation
- DBCC showcontig("sales02") WITH no_infomsgs ;
- go
DBCC SHOWCONTIG scanning 'sales02' table... Table: 'sales02' (309576141); index ID: 1, database ID: 10 TABLE level scan performed. - Pages Scanned................................: 1 - Extents Scanned..............................: 1 - Extent Switches..............................: 0 - Avg. Pages per Extent........................: 1.0 - Scan Density [Best Count:Actual Count].......: 100.00% [1:1] - Logical Scan Fragmentation ..................: 0.00% - Extent Scan Fragmentation ...................: 0.00% - Avg. Bytes Free per Page.....................: 6656.0 - Avg. Page Density (full).....................: 17.77%
When you compare the DBCC SHOWCONTIG before and after:
- Before: PK_sales_02 fragmentation: 66%, After: 0%
- Before: Pages Scanned: 3, After: 1
- Before: Scan Density: 50%, After: 100%
