scan density (dbcc showcontig) question

[collierd2]

Hello

I am using SQL 2000

I am experimenting with indexes at the moment, trying to improve update/access time.

I have created a simple table (bunzlsubgroups) as:

CREATE TABLE [bunzlsubgroups] (
	[bunzlgroupid] [udt_bgroupid] NOT NULL ,
	[bunzlgroupdesc] [udt_bgroupdesc] NULL ,
	[majorgroupid] [udt_mgroupid] NULL ,
	[majorclassid] [udt_mclassid] NULL ,
	CONSTRAINT [PK_bunzlsubgroups] PRIMARY KEY  CLUSTERED 
	(
		[bunzlgroupid]
	)  ON [PRIMARY] 
) ON [PRIMARY]

This contains about 1500 records

When I run dbcc showcontig on it, it returns:

DBCC SHOWCONTIG scanning 'bunzlsubgroups' table...
Table: 'bunzlsubgroups' (1577458206); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 6
- Extents Scanned..............................: 4
- Extent Switches..............................: 3
- Avg. Pages per Extent........................: 1.5
- Scan Density [Best Count:Actual Count].......: 25.00% [1:4]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 75.00%
- Avg. Bytes Free per Page.....................: 750.8
- Avg. Page Density (full).....................: 90.72%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

looking around, the general consensus seems to be, aim for a Scan and Page Density near 100%

Why is the scan density so low if I have only just created this table?
If I run

dbcc indexdefrag(mdw, bunzlsubgroups, pk_bunzlsubgroups)

to defrag the index, it does nothing.

I'm really trying to get a good understanding of how this works and this doesn't quite make sense

Thanks

Damian.

 

[SQLDenis]

it looks like each user defined data type is placed in its own extent. Remember an extent has 8 pages, you have 6 pages spread out over 4 extents

what happens when you do this?

[COLOR=blue]CREATE[/color] [COLOR=blue]TABLE[/color] [bunzlsubgroups] (
    [bunzlgroupid] [COLOR=blue]int[/color] NOT NULL ,
    [bunzlgroupdesc] [COLOR=blue]varchar[/color](100) NULL ,
    [majorgroupid] [COLOR=blue]int[/color] NULL ,
    [majorclassid] [COLOR=blue]int[/color] NULL ,
    [COLOR=blue]CONSTRAINT[/color] [PK_bunzlsubgroups] [COLOR=blue]PRIMARY[/color] [COLOR=blue]KEY[/color]  [COLOR=blue]CLUSTERED[/color] 
    (
        [bunzlgroupid]
    )  [COLOR=blue]ON[/color] [[COLOR=blue]PRIMARY[/color]] 
) [COLOR=blue]ON[/color] [[COLOR=blue]PRIMARY[/color]]

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[SQLDenis]

are any of the user defined datatypes text or ntext?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[collierd2]

This is a little bit better, although it is still 33.33%:

DBCC SHOWCONTIG scanning 'bunzlsubgroups' table...
Table: 'bunzlsubgroups' (1641458434); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 6
- Extents Scanned..............................: 3
- Extent Switches..............................: 2
- Avg. Pages per Extent........................: 2.0
- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 66.67%
- Avg. Bytes Free per Page.....................: 750.8
- Avg. Page Density (full).....................: 90.72%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

[collierd2]

All the datatypes are varchar (using above, int is actual varchar(10))

 

[Lamprey13]

I've run into this before, but I don't remember the solution any more. I seem to recall that there is some sort of statistical issue, as in they are not updated. You could try running a DBCC UPDATEUSAGE (make sure to include the COUNT_ROWS option). I'm not sure if this will help in your case or what the issue is exactly. BTW, did you try changing the FILL FACTOR to 100?

-Ryan