Estimating the size of a table with a clustered in

Question

Estimating the size of a table with a clustered in

LeopoldNjampa

SSC-Addicted

Points: 473
More actions
July 24, 2003 at 2:39 am

#80542

Hi all,
Anyone out there to help... I want to estimate the size of a SQL 2k table as outlined in BOL topic "Estimating the size of a table with a clustered index". In particular, I want to collate the number of columns in index key. I have attempted joining the following tables; sysindexes, sysindexkeys, sysobjects,syscolumns.
SELECT b.[name], a.id, a.indid, COUNT(c.colid) AS ['# Cols']
FROM sysindexkeys c INNER JOIN sysindexes a
ON a.indid=c.indid
INNER JOIN sysobjects b
ON a.id = b.id
WHERE b.xtype = 'U'
AND a.indid = 1
GROUP BY b.[Name], a.[ID], a.Indid
Any help is much appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

jpipes SSChampion Points: 12230 More actions · Answer 1

Note quite sure what you mean by "collate the number of columns", but you may want to join on syscolumns to find information on the columns involved in the table (clustered index). What's the overall objective of your query?

LeopoldNjampa SSC-Addicted Points: 473 More actions · Answer 2

Thanks jpipes for the reply. The overall objective is to estimate the space used to store in clustered index! For this, I not only need to find out the number of columns in the index keys (BOL), but also calculate the space used by variable- and fixed- length columns within the index row.

This is where i got to so far...

SELECT SUM([# Cols]) AS Num_Ckey_Cols

FROM

(

SELECT [NAME], COUNT(ColId) AS [# Cols]

FROM

(

SELECT c.[Name], c.xtype, c.type, a.indid, a.keys, b.colid--, d.name

FROM SYSOBJECTS c

inner join SYSINDEXES a

on c.id=a.id

inner join SYSINDEXKEYS b

on a.id = b.id

--inner join syscolumns d

--on b.colid=d.colid

WHERE a.indid=1 --AND a.indid NOT IN (0,1, 255)

--ORDER BY c.[Name]

) AS T1

GROUP BY [Name]

--ORDER BY [Name]

) AS T2

jpipes SSChampion Points: 12230 More actions · Answer 3

If you want to find the space used by the table, check out the dpages column in sysindexes. It stores the number of extents used by the table (heap or clustered index). An extent (sometimes referred to as a page) is an 8-KB unit of storage within SQL Server:



select so.name, CONVERT(VARCHAR(20), (si.dpages * 8)) + 'KB' as "Space used"

from sysindexes si 

inner join sysobjects so on si.id = so.id

where so.type = 'u'

and indid < 2

order by so.name

It sounds a bit like your more interested in the average size of the row in a clustered index. If that's the case, there are a number of scripts (on this site and others) you can use to query syscolumns, sysindexes, and sysobjects to determine the average row length for a clustered index or heap.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 4

A clustered index only requires space for the b-tree rows, as the leaf rows are stored in the table itself. Extra index fields add to the size of each row, but the number of rows is a fraction of the number of rows in the table, so a clustered index usually takes up little space compared to the table.

I have a spreadsheet I put together from the BOL topics and other sources that can be used to estimate the minimum size for tables and indexes. I would be happy to send you a copy.

If you're trying to use the system tables to determine the space used by existing tables, the only thing I would add to jpipes' note is to run DBCC UPDATEUSAGE to get accurate numbers.

LeopoldNjampa SSC-Addicted Points: 473 More actions · Answer 5

Thanks Scotts and JPipes for your very helpful replies.

PS: Scott, the spreadsheet is excellent.

pravallika.mca06 SSC Enthusiast Points: 170 More actions · Answer 6

Hi SS, Can you pleaes forward the excel sheet for me also?