Help in understanding dbcc showcontig readout 1

[Eyespi20]

I have a small table with 18 columns and 602 rows. It has two indexes on it; one clustered on the primary key and the other non-clustered index includes the primary key and a column that is the primary key in a table that is used in conjunction with this one to apply pricing levels to customers.

This is my readout when I run dbcc showcontig:

DBCC SHOWCONTIG scanning 'np_price_list' table...
Table: 'np_price_list' (158623608); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 11
- Extents Scanned..............................: 4
- Extent Switches..............................: 3
- Avg. Pages per Extent........................: 2.8
- Scan Density [Best Count:Actual Count].......: 50.00% [2:4]
- Logical Scan Fragmentation ..................: 27.27%
- Extent Scan Fragmentation ...................: 75.00%
- Avg. Bytes Free per Page.....................: 760.5
- Avg. Page Density (full).....................: 90.60%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

This is what dbcc showcontig shows for the other table -- also a very small one with 561 rows and 18 columns.


USE openp
GO
DECLARE @id int, @indid int
SET @id = OBJECT_ID('np_price_rule')
SELECT @indid = indid
FROM sysindexes
WHERE id = @id
AND name = 'aunmind'
DBCC SHOWCONTIG (@id, @indid)
GO


In asking you guys questions yesterday about indexing and fragmentation, you told me that I wanted the scan density to be as close to 100% as possible. I've run index wizard against this table and it has not recommended any changes in indexing, yet 50% is the best I can get on either of them.

Can any of you give me a direction to look in to better improve these tables? They are heavily used since they contain the pricing rules used to price out anything that is not an inventory item.

Thanks!

Margaret

 

[SQLDenis]

you don'r run the wizard to improve density
You either rebuild the index or defragment the index
While rebuilding the index the table will not be available, while defragment it will try fragmenting it first and then run showcontig again

run this after looking it up in BOL

DBCC INDEXDEFRAG (9, np_price_list , 1)
GO

And then run showcontig again


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Eyespi20]

Thanks, Dennis, but I can't run indexdefrag because this is a SQL 7.0 and defrag doesn't come in until 2000.

I have a nightly job that rebuilds the indexes on these tables, but still get no better results, so either my job isn't working correctly (and I didn't write it, I just inheirited it) or there is something else going on.

These two tables very seldom have new rows put in, so are not being inserted into or updated that often, but queried heavily.

Thanks!

Margaret

 

[SQLDenis]

In the worst case rebuild the whole table then

select * into np_price_list2 from np_price_list
create indexes on this table
check with showcontig if everything is OK drop old table rename this one to np_price_list



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Eyespi20]

Great idea! Thanks, Denis!

Margaret

 

[SQLDenis]

No Problem and thanks

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[yelworcm]

Actually, this table is way too small to give you any noticeable benefit to defragging. Have a look at this paper, it should give you everything you need:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

 

[Eyespi20]

Thank you, yelworcm, but as I stated before, this is a SQL 7 database and defrag is not available for it anyway.

Margaret

 

[yelworcm]

The whitepaper deals with a lot more than one command. You should at least click the link.

 

[Eyespi20]

I did -- thanks again.

Margaret