Index size are huge compared to the amount of data?

Index size are huge compared to the amount of data?

Post by steve hugh » Thu, 26 Jun 2008 01:28:35


Anyone help with this SQLserver 2000.
I have recently tried to shrink the database in our production server
(currently 17G) I notice when using sp_spaceused in a stored proc that
iterates through all tables that some tables (about 30) have large
sizes for the amount of data that they contain. For example. Tables
that have 4 columns Int - uniqueidentifier, col2 varchar(25), col3
varchar(25), col4 varchar(25) now has index shown

tblRejectCodes 8 9760 KB 8 KB 5216 KB 4536 KB
I cannot drop the index, in production, but if i do dbreindex no
change, and if i do dbcc indexdefrag no change.
If I do insert select all into a new table and add an index of the same
type (int) then the size of the toal index is about 25K
Any one know what is going on here??
thanks for any help


--
regards
steve
 
 
 

Index size are huge compared to the amount of data?

Post by Gert-Jan S » Thu, 26 Jun 2008 02:01:55

Steve,

Based on your narrative, I can only guess. My guess is, that in
production, the index you mention is clustered. In that case, the data
is part of the index (IOW, the index = the table).

If you SELECT the data into a new table, and manually create an index
without the keyword CLUSTERED, then you are creating a nonclustered
index. Such an index only contains the index keys and a reference to the
clustered index or table page that stores the row, which (typically) is
just a fraction in size.

--
Gert-Jan

 
 
 

Index size are huge compared to the amount of data?

Post by Andrew J. » Thu, 26 Jun 2008 05:14:53

Gert is correct in that you most likely have a HEAP (table with no clustered
index). The only way to defrag or reclaim wasted space is to export and
import or better yet create a clustered index. Also ensure that you use the
UPDATE USAGE option of sp_spacedused to ensure you are looking at accurate
info as well.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
 
 
 

Index size are huge compared to the amount of data?

Post by TheSQLGur » Thu, 26 Jun 2008 05:56:21

In addition to the possibility that your table is a heap, perhaps you don't
have any free space in the database in which case a defrag type operation
may not have much luck in actually getting pages contiguous on disk.

Also note that it is generally a very BAD thing to shrink a database.
search the web for tibor karaszi don't shrink.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
 
 
 

Index size are huge compared to the amount of data?

Post by steve hugh » Thu, 26 Jun 2008 17:42:49

Thanks for the info. The index is definitely not clusterd. I have
scripted the table from qa into this

/****** Object: Table [dbo].[tblRejectCodes] Script Date:
25/06/2008 9:40:42 AM ******/
CREATE TABLE [tblRejectCodes] (
[RejectID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Code] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_tblRejectCodes] PRIMARY KEY NONCLUSTERED
(
[RejectID]
) WITH FILLFACTOR = 80 ON [PRIMARY]
) ON [PRIMARY]
GO

. So Why would this give me the increased size? I could understand the
clustered index - being the whole table, being big, or as big as the
data + index, but the sizes seem excessive for a mere 8 rows of data.
thanks again for the help.

steve







--
regards
steve
 
 
 

Index size are huge compared to the amount of data?

Post by steve hugh » Thu, 26 Jun 2008 18:18:25

Thanks for the info. I am reading his articles now! And all the links,
I might not get any work done today, but it should help in the long
run.;)







--
regards
steve
 
 
 

Index size are huge compared to the amount of data?

Post by Andrew J. » Thu, 26 Jun 2008 22:54:48

Steve,

That is not an index it is a heap. A Heap is the table and thus all the
data. If you look in sysindexes you will see an indid of 0 which is the
heap. 1 is a clustered index and >1 and < 255 is a nonclustered index. 255
are blobs. The example you gave originally is:

tblRejectCodes 8 9760 KB 8 KB 5216 KB 4536 KB

Since this is the table (heap) not an index a reindex will do nothing. Add a
clustered index and you will be able to reindex if needed in the future.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
 
 
 

Index size are huge compared to the amount of data?

Post by steve hugh » Fri, 27 Jun 2008 00:38:48

Many thanks for the info. Will need to test this to see if creating
clustered index on all of the tables will affect the application that
is using this db.

Thanks.
steve






--
regards
steve