Extent Fragmentation High Just After Reindexing

Hello. When reviewing the DBCC SHOWCONTIG immediately after reindexing all indexes on a database, I see the ExtentFragmentation has values like 50 to 70%... These are SQL 2005 tables with clustered PK's, no large varchars/blobs, and at least 100 pages in the index... The numbers related to PAGE fragmentation are ok after reindexing, but not the EXTENT fragmentation numbers.

I noticed the drive is in need of being defragged at the disk level. Is that a reason why reindexing doesn't fix the Extent frag numbers? ANy other ideas on this? I can try defragging the DISK over the weekend, bringing the database offline then, but any other thougths on why the Extents show these high %'s? Is there any command to reset them and maybe that isn't happening? Like must I do update usage to get valid Extent frag #'s?

If there were MANY autogrows on the files, is that a different level of fragmentation? and how could all those small pieces of files be pulled back together? Thanks, Bruce

Correct me if I'm wrong, but since its not a heap, I'm not sure the old extent fragmentation column is meaningful, in terms of SQL fragmentation. You're in 2005, use sys.dm_db_index_physical_stats instead. The avg_fragmentation_in_pct column shows logical fragmentation for indexes and extent fragmentation for heaps.

Code Snippet

use <database name here>
select * from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'<table name here>'),null, null,null)

This may show you a more application fragmentation percent.

Running your NTFS defrag tool in the OS won't hurt, but you are correct that will have to bring all apps to a halt including SQL.

Physical and logical fragmentation due to autogrow has a lot to do with existing fragmentation, available disk space and activity.
|||

W, thanks. Accoring to the SQL 2005 BOL snip below, ExtentFragmentation is not applicable to heaps and when the index spans multiple files. All of the indexes in question are NOT heaps and have a single MDF file. I'm doing other tests, like using the ALTER INDEX REORGANIZE command, and will try fully dropping and recreating the index, so I see a number closer to 0% Extent Fragmentation. I'd think ExtentFragmentation still matters, but if you'er saying to use the new functions, and the SHOWCONTIG is showing bogus data, hmmm, great, I'll try to find ExtentFragmentation in those functions too... Thanks, Bruce

ExtentFragmentation

Percentage of out-of-order extents in scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order extent is one for which the extent that contains the current page for an index is not physically the next extent after the extent that contains the previous page for an index.

Note: This number is meaningless when the index spans multiple files.

|||

ok, here is a specific example, with SQL I used and results... If anyone has any ideas on WHY the Extent Fragmentation numbers act like this, please let me know. Maybe they ARE just BOGUS completely and meaningless in SQL 2005? Here are the exact steps I did... all on SQL 2005 SP2, database has a single data file, and "MyTable" has a clustered PK and 2 other indexes.

Step 1: Ran a SHOWCONTIG to see the fragmentation level.

Code Snippet

print 'DBCC SHOWCONTIG ([tMyTable])'
DBCC SHOWCONTIG (tMyTable) WITH ALL_INDEXES
go

DBCC SHOWCONTIG ([tMyTable])
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 314
- Extents Scanned..............................: 46
- Extent Switches..............................: 189
- Avg. Pages per Extent........................: 6.8
- Scan Density [Best Count:Actual Count].......: 21.05% [40:190]
- Logical Scan Fragmentation ..................: 54.14%
- Extent Scan Fragmentation ...................: 45.65%
- Avg. Bytes Free per Page.....................: 2890.9
- Avg. Page Density (full).....................: 64.28%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 3, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 42
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 135.0
- Avg. Page Density (full).....................: 98.33%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 6, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 31
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 2.8
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 25.81%
- Extent Scan Fragmentation ...................: 72.73%
- Avg. Bytes Free per Page.....................: 214.1
- Avg. Page Density (full).....................: 97.36%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Note that 2 of teh indexes have 21% and 36% scan density values. So, this is my starting point, and I want to DEFRAG this tabloe, all 3 indexes.

Step 2: Ran an ALTER INDEX on all 3, with the REBUILD option.

Code Snippet

ALTER INDEX XPKtMyTable ON dbo.tMyTable
REBUILD
GO
ALTER INDEX XIF2tMyTable ON dbo.tMyTable
REBUILD
GO
ALTER INDEX XIF5tMyTable ON dbo.tMyTable
REBUILD
GO

Step 3: Ran another SHOWCONTIG

Code Snippet

print 'DBCC SHOWCONTIG ([tMyTable])'
DBCC SHOWCONTIG (tMyTable) WITH ALL_INDEXES
go

DBCC SHOWCONTIG ([tMyTable])
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 205
- Extents Scanned..............................: 26
- Extent Switches..............................: 25
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 100.00% [26:26]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 46.15%
- Avg. Bytes Free per Page.....................: 123.2
- Avg. Page Density (full).....................: 98.48%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 3, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 42
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 66.67%
- Avg. Bytes Free per Page.....................: 135.0
- Avg. Page Density (full).....................: 98.33%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 6, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 31
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 2.8
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 25.81%
- Extent Scan Fragmentation ...................: 72.73%
- Avg. Bytes Free per Page.....................: 214.1
- Avg. Page Density (full).....................: 97.36%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Note that the Scan Density of teh first 2 indexes changed to 100%, but the 3rd didn't defrag for some reason?!? Also, note that the Extent Scan Fragmentation numbers are still high, up to 72% on the 3rd index.

Step 4: Dropped and recreated the 3 indexes

Code Snippet

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tMyTable]') AND name = N'XPKtMyTable')
ALTER TABLE [dbo].[tMyTable] DROP CONSTRAINT [XPKtMyTable]

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tMyTable]') AND name = N'XIF2tMyTable')
DROP INDEX [XIF2tMyTable] ON [dbo].[tMyTable] WITH ( ONLINE = OFF )

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tMyTable]') AND name = N'XIF5tMyTable')
DROP INDEX [XIF5tMyTable] ON [dbo].[tMyTable] WITH ( ONLINE = OFF )

ALTER TABLE [dbo].[tMyTable] ADD CONSTRAINT [XPKtMyTable] PRIMARY KEY CLUSTERED
( [MyTableID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [XIF2tMyTable] ON [dbo].[tMyTable]
( [coCode] ASC,
[DefID] ASC,
[GroupID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [XIF5tMyTable] ON [dbo].[tMyTable]
( [liabilityID] ASC,
[coCode] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Step 5: Ran the SHOWCONTIG again

Code Snippet

print 'DBCC SHOWCONTIG ([tMyTable])'
DBCC SHOWCONTIG (tMyTable) WITH ALL_INDEXES
go

DBCC SHOWCONTIG ([tMyTable])
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 205
- Extents Scanned..............................: 26
- Extent Switches..............................: 25
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 100.00% [26:26]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 38.46%
- Avg. Bytes Free per Page.....................: 123.2
- Avg. Page Density (full).....................: 98.48%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 3, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 42
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 33.33%
- Avg. Bytes Free per Page.....................: 135.0
- Avg. Page Density (full).....................: 98.33%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 6, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 31
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 2.8
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 25.81%
- Extent Scan Fragmentation ...................: 45.45%
- Avg. Bytes Free per Page.....................: 214.1
- Avg. Page Density (full).....................: 97.36%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Note the Extent Scan Fragmentation numbers are better, but not good, and that 3rd index is still at 36% Scan Density.

Step 6: Used the new sys.dm_db_index_physical_stats function to compare to the SHOWCONTIG values. (Broke it up into 4 queries for ease in reading here)

Code Snippet

select database_id, object_id, index_id, partition_number, index_type_desc
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)

select alloc_unit_type_desc, index_depth, index_level, avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)

select fragment_count, avg_fragment_size_in_pages, page_count, avg_page_space_used_in_percent, record_count
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)

select ghost_record_count, version_ghost_record_count, min_record_size_in_bytes, max_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)

database_id object_id index_id partition_number index_type_desc
-- -- -- -
21 1125175354 1 1 CLUSTERED INDEX
21 1125175354 3 1 NONCLUSTERED INDEX
21 1125175354 6 1 NONCLUSTERED INDEX

alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent
-- -- -
IN_ROW_DATA 2 0 0
IN_ROW_DATA 2 0 0
IN_ROW_DATA 2 0 25.8064516129032

fragment_count avg_fragment_size_in_pages page_count avg_page_space_used_in_percent record_count
-- -- -- --
11 18.6363636363636 205 NULL NULL
3 14 42 NULL NULL
9 3.44444444444444 31 NULL NULL

ghost_record_count version_ghost_record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes forwarded_record_count
-- -- -
NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL

That function doesn't appear to provide as much info as SHOWCONTIG, but maybe there are more functions to get the ExtentFrag % values...

ok, anyone have any ideas on this? Do I even care if Extent Frag numbers are high? and wondering why on some tables that have like 28,000 rows, they get very page-fragged after about only 200 row changes. I did this locally on a laptop also and defragged my drive in between and that had no effect on the SHOWCONTIG numbers. IF the autogrow was very small in the past and expanded many times, how would I see that now? In older versions of SQL Server, there was a table that maintained those "extents" (?), anything like that now, and is that a possible reason for fragmentation also?

Other ideas/comments are appreciated!! Thanks, Bruce

|||Dunno about why index 6 is showing that fragmentation, but your table not only looks pretty tight, but pretty small. 205 pages. Is fragmentation really a concern?

I was also under the impression that fragmentation of the clustered index was the chief concern anyway, and that looks tight right now. Pretty sure the stated fragmentation of nonclustered indexes is even meaningful.

Bruce dBA wrote:

IF the autogrow was very small in the past and expanded many times, how would I see that now?"

You should be able to see "Autogrow of file" in the log if you're auditing that.|||

W, yes, I should have done that example on a larger table. The clustered index is the main importance, true, was just wondering why the other indexes get "fragged" so quickly with a small amount of updates (like 200 updates on a 28,000 row table)... The autogrow cauing fragmentation question, was more about before I started checking this out, the autogrow is set better now, but I don't know what it WAS before, and was just wondering if having a lot of small autogrowths in the past added to this, even though reindexing is done, aren't the MDF/NDF files fragged also possibly. In older releases of SQL Server, I remember that data was stored in catalog tables, each new "autogrowth" back then, not sure if you can historically see that, and how files fragging relates to index fragging.... Thanks, Bruce|||

Try using a fill factor of 90%. You are getting fragmentation because you are filling the pages at 100%.