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%

SQLHacks for Microsoft SQL Server
Home page Administration Dates & Times Data Manipulation FAQs Optimization Summaries Site Map About us About us Copyrights Credits Disclaimers Guaranties Privacy Support policies Feedback	Optimize How to defragment data with MSSQL 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%
All contents in SQLHacks.com are the property of db Networks Ltd and it's affiliates. All other trademarks not owned by db Networks Ltd or its affiliates that appear on the SQLHacks.com site are the property of their respective owners, especially: Windows^�, MS SQL^�, SQL Server^� which are the property of Microsoft Corp. DB2^� which is the property of IBM Systems. Oracle database^� which is the property of Oracle Corp.