don't understand why this index is fragmented

[sqlcasey]

Hi,

I am examining the weekly index defrag report... I run DBCC REINDEX on a weekly basis on indexes that are fragmented.

This is weird. One of the indexes that was reindexed was an index on a numeric ID field - it's a nonclustered, unique, primary key index - don't ask me why this is a non-clustered field, I didn't create it.

No updates take place on this field.

So, how could this index be getting fragmented if no updates are taking place??

Thanks

 

[sqlcasey]

found the culprit - the table is truncated on a weekly basis, then the fields re-populated

i guess that would explain the fragmentation? not sure how, though

 

[ptheriault]

are there inserts or deletes? Also, only tables with clustered indexes can be reindexed. Tables without a clustered index are called heap tables. There are 3 ways to fix fragmentation on them.
1. create a unique clustered index
2. Create a unique clustered index then drop it.
3. select the data out of the table into a temp table then back into the table with an order by clause.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[sqlcasey]

Also, only tables with clustered indexes can be reindexed

I find this statement interesting because every week the log indicates the the DBCC DBREINDEX took place on non-clustered indexes. And running DBCC SHOWCONTIG on the index in question, it shows:

- Pages Scanned................................: 102
- Extents Scanned..............................: 25
- Extent Switches..............................: 24
- Avg. Pages per Extent........................: 4.1
- Scan Density [Best Count:Actual Count].......: 52.00% [13:25]
- Extent Scan Fragmentation ...................: 84.00%
- Avg. Bytes Free per Page.....................: 632.0
- Avg. Page Density (full).....................: 92.19%

My selection criteria for defragging is:

WHERE ScanDensity <= 40
AND LogicalFrag > 10
AND CountPages > 8

So it does appear that the reindex took place, doesn't it?

Frankly, I never gave this much thought, whether the index was clustered or non-clustered. If it was up to me, all unique pk's would be clustered. Like I said, I didn't write this stuff.

 

[ptheriault]

Extent Scan Fragmentation ...................: 84.00%

I would say this table has not been re-indexed. It is 84% fragmented. You want this number as close to 0 as possible.
And the scan density should be as high as possible,

Here is a sample from a table I have just re-indexed on Sunday.

DBCC SHOWCONTIG scanning 'Access_Log' table...
Table: 'Access_Log' (2130158684); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 42787
- Extents Scanned..............................: 5365
- Extent Switches..............................: 5385
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.31% [5349:5386]
- Logical Scan Fragmentation ..................: 0.16%
- Extent Scan Fragmentation ...................: 10.44%
- Avg. Bytes Free per Page.....................: 721.5
- Avg. Page Density (full).....................: 91.09%

If a table has a unique clustered index then when you re-index the table it will reindex all the indexes. This is because the data is actually stored in the leaf level of the clustered index. Any other index is just a pointer to the clustered index.

Here is very good informational link.

http://www.sql-server-performance.com/dt_dbcc_showcontig.asp

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[sqlcasey]

wait, i was confused... you are right...

i think the solution here is to convert some of these indexes to clustered indexes... since they are UNIQUE, non null anyways... it will be easier than the 3 options you mentioned

thanks

 

[ptheriault]

That is correct. Unless you are working with an OLAP database you want to try to aviod heap tables.

- Paul
- If at first you don't succeed, find out if the loser gets anything.