Scan Density question

[ezecastle]

Can someone explain why i can drop and recreate indexes on say 2 tables and they had a scan densitiy of say 15% and 10%.

After i recreate the indexes on both tables one comes back with a scan density of 100% and the other only 25%.

What would cause this table to only go to 25% and not 100% like i want it to be?

Thanks for your help

[rmiao]

Ensure the table has clustered index, only rebuilding clustered index can defrag table.

[ezecastle]

There is one clustered index on the table that didnt update to 100%.


Any other ideas?

[rmiao]

What's fill factor used for that index?

[ezecastle]

I used '0' as the fill factor.

[rmiao]

How big is the table? How many pages used?

[ezecastle]

- Pages Scanned................................: 13
- Extents Scanned..............................: 7
- Extent Switches..............................: 6
- Avg. Pages per Extent........................: 1.9
- Scan Density [Best Count:Actual Count].......: 28.57% [2:7]
- Logical Scan Fragmentation ..................: 15.38%
- Extent Scan Fragmentation ...................: 85.71%
- Avg. Bytes Free per Page.....................: 2549.8
- Avg. Page Density (full).....................: 68.50%

[rmiao]

You rebuilt clustered index on that table?

[ezecastle]

yes, this was the only table that had an issue not coming back 100% on the scan density.

[ezecastle]

yes, this was the only table that had an issue not coming back 100% on the scan density.

The clustered index is a Primary key clustered, does that make a differance?

[ezecastle]

The index is being used for primary key contraint enforcement. So i'm assuming it couldn't be dropped and recreated correctly because of this.

correct?

[rmiao]

This would happen on table that uses less than 8 pages since sql just put it on free pages in any shared extent. If table has more than 8 pages, sql should assign dedicated extents to it. Will move out from shared extents when the table getting bigger.

[ezecastle]

well I re-ran the dbcc dbreindex on the table and then re-ran dbcc showcontig and now the table has a 100% sand density. I have no idea what happened here since i used the dbcc dbreindex before

[ezecastle]

After numbers...

TABLE level scan performed.
- Pages Scanned................................: 10
- Extents Scanned..............................: 2
- Extent Switches..............................: 1
- Avg. Pages per Extent........................: 5.0
- Scan Density [Best Count:Actual Count].......: 100.00% [2:2]
- Logical Scan Fragmentation ..................: 10.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 780.3
- Avg. Page Density (full).....................: 90.36%