Table/Index fragmentation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Table/Index fragmentation

Author

Topic

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2011-05-06 : 09:45:41

Would someone explain this to me? SQL 2005, 64 bit, SP3.

I have a bunch of tables reporting fragmentation. Here's an analysis of one of the tables. It has 3,473,393 rows.

Here's the showcontig:

DBCC SHOWCONTIG scanning 'Table1' table...
Table: 'Table1' (165575628); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 84925
- Extents Scanned..............................: 10627
- Extent Switches..............................: 10629
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.87% [10616:10630]
- Logical Scan Fragmentation ..................: 0.03%
- Extent Scan Fragmentation ...................: 9.46%
- Avg. Bytes Free per Page.....................: 1540.8
- Avg. Page Density (full).....................: 80.96%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I used the following query to look further:

SELECT a.index_id
, name
, avg_fragmentation_in_percent
,index_type_desc
,fragment_count
,page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE a.avg_fragmentation_in_percent > 10

Here is the info for the the table (paste into notepad for better view):

index_id name avg_fragmentation_in_percent index_type_desc fragment_count page_count
----------- ------------------------------------ ---------------------------- ------------------------------------------------------------ -------------------- --------------------
1 I3TimeStampGMT 100 CLUSTERED INDEX 2 2
2 IEAgentActivityLog 37.2950819672131 NONCLUSTERED INDEX 46 244
2 IEAgentActivityLog 100 NONCLUSTERED INDEX 5 5
6 Avtex__AgentActivityLog_AgentReport 85.7142857142857 NONCLUSTERED INDEX 14 14
8 IX_StatusDateTime 12.987012987013 NONCLUSTERED INDEX 17 77

It says the clustered index (I3TimeStampGMT) is 100% fragmented, but only has 2 pages. The showcontig says it has 84,925 pages. What am I missing? How could this clustered index only have 2 pages? The table has over 3 million rows.

Thanks in advance.

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2011-05-06 : 09:50:02

Also, I do a dbcc dbreindex on all tables each night. I've ran that for this one table as well as indexdefrag and rebuilding the indexes (scripted). Nothing seems to help. I assume that's because there's only 2 pages but showcontig shows 84,925 pages. Just a bit confused. Can someone set me straight? Thanks.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-06 : 10:59:19

only table which have 46244 pages have less fregmentation and rest of tables have very few pages so NO need to worry becuase these pages could be from mixed extents thats why they will keep showing heavy frementation.

--------------------------
http://connectsql.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources