| Author |
Topic |
|
matnyc2000
Starting Member
16 Posts |
 Posted - 2007-10-03 : 13:24:09
|
| Hi,After issuing an index rebuild on a primary key index (and updating statistics), the index still shows a scan density of 12.5%!Any ideas on why the rebuild doesn't seem to do anything on the fragmentation levels?I'm using sql 2005Thanx |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-03 : 13:36:51
|
| Small index? |
 |
|
|
matnyc2000
Starting Member
16 Posts |
Posted - 2007-10-03 : 14:33:15
|
| the index spans 8 pages |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-03 : 14:34:24
|
| That would be the problem then.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-10-03 : 14:45:33
|
| is this scan density causing a performance problem?-ec |
|
|
|
matnyc2000
Starting Member
16 Posts |
Posted - 2007-10-03 : 14:56:05
|
| ok so the index is too small to be defraged?is this because the number leaf pages havent reach a certain number?please explainthnx |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-10-03 : 15:18:06
|
| paste in your DBCC SHOWCONTIG output |
|
|
|
matnyc2000
Starting Member
16 Posts |
Posted - 2007-10-03 : 15:45:41
|
| Here it is:DBCC SHOWCONTIG scanning 'Sponsor' table...Table: 'Sponsor' (431340601); index ID: 1, database ID: 7TABLE level scan performed.- Pages Scanned................................: 8- Extents Scanned..............................: 8- Extent Switches..............................: 7- Avg. Pages per Extent........................: 1.0- Scan Density [Best Count:Actual Count].......: 12.50% [1:8]- Logical Scan Fragmentation ..................: 87.50%- Extent Scan Fragmentation ...................: 75.00%- Avg. Bytes Free per Page.....................: 1340.0- Avg. Page Density (full).....................: 83.44% |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-10-03 : 16:12:05
|
| ok, here is my explanation of what you are seeing.SQL server has 2 types of extents. Uniform extents and mixed extents. Uniform extents are extents owned by a single object. all 8 data pages of an extent (pages are 8kb each, each extent is 64kb) is all allocated to a single object.Mixed extents are used for smaller objects. Objects that are less than 8 pages in size use mixed extents. This is why we see that this object is using a single data page in each of 8 extents. This is why you see a low scan density. 1/8 is 12.5%. If the object were a little larger, sql server would rebuild it into a Uniform extents and you would see a scan density closer to 100%.-ec |
|
|
|
matnyc2000
Starting Member
16 Posts |
Posted - 2007-10-03 : 16:32:49
|
| now thats an explanation ;)thnx |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-10-04 : 05:28:51
|
The first extent for any table is a mixed extent irrespective of size. Subsequent extents are uniform. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-10-04 : 15:17:06
|
quote:
Originally posted by pootle_flump
The first extent for any table is a mixed extent irrespective of size. Subsequent extents are uniform.
For SQL 2000 the first 8 extents are allocated from mixed extents. from extent 9 on they are allocated from uniform extents. You can see this behavior by using the undocumented DBCC EXTENTINFO command. Has SQL 2005 changed this behavior?-ec |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-10-05 : 04:43:55
|
quote:
Originally posted by eyechart
quote:
Originally posted by pootle_flump
The first extent for any table is a mixed extent irrespective of size. Subsequent extents are uniform.
Has SQL 2005 changed this behavior?-ec
No I don't think so. I think perhaps I was a bit lose in my terminology too. The first 8 pages of data in a table are allocated amongst mixed extents and pages 9+ are in uniform extents. I have only read re 2005 but it sounds the same as what you are saying. |
|
|
|
|
index rebuild does not defrag