Determining index size - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Determining index size

Author

Topic

billsox
Yak Posting Veteran

74 Posts

Posted - 2006-04-14 : 11:09:54

Hi -- Is it possible to determine the size of each index on a table? If so, how?

Thanks in advance,

Bill

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-14 : 11:23:43

You can use DBCC SHOWCONTIG for this.

Read about DBCC SHOWCONTIG in SQL Server Books Online.

CODO ERGO SUM

billsox
Yak Posting Veteran

74 Posts

Posted - 2006-04-14 : 11:37:04

This is good information but I was hoping to get an idea of how large each index is in bytes. My boss won't understand me if I tell him about pages, extents, or fragmentation. Is it possible to calculate the size of each index in bytes based on the information from DBCC SHOWCONTIG?

Bill

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-14 : 11:49:22

Read about Pages and Extents in SQL Server Books Online.

CODO ERGO SUM

billsox
Yak Posting Veteran

74 Posts

Posted - 2006-04-14 : 12:26:52

Okay, so if a page is 8 KB, then I should be able to take the "pages scanned" from DBCC SHOWCONTIG and multiply it by 8 to get the actual size of the index in kilobytes. Yes?

Bill

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-04-14 : 17:47:40

You can query the sysindexes table to get number of rows and size. You can also execute sp_spaceused to get the total index size (among other stats) of one table or one database. You can also run this little stored proc I wrote that gives you the sp_spaceused stats for all the tables in a particular database (just pass in the database name like this: execute spaceused_simulator 'YourDatabase').

CREATE PROC spaceused_simulator
@database_name varchar(128)
AS
DECLARE @cmd varchar(1000),
@bytes int

SET NOCOUNT ON

SELECT @bytes = [low]/1024 FROM master..spt_values
WHERE number = 1
AND type = 'E'

CREATE TABLE #tmp_spaceused (
id int NULL,
tablename varchar(128) NULL,
rows int NULL,
reserved int NULL,
data int NULL,
index_size int NULL,
unused int NULL)

SET @cmd =
'INSERT INTO #tmp_spaceused (id, tablename)
SELECT id, name FROM ' + @database_name + '..sysobjects
WHERE xtype = ''U'''

EXEC (@cmd)

SET @cmd =
'UPDATE #tmp_spaceused
SET rows = A.rows
FROM ' + @database_name + '..sysindexes A
WHERE #tmp_spaceused.id = A.id
AND A.indid IN (0, 1)'

EXEC (@cmd)

SET @cmd =
'UPDATE #tmp_spaceused
SET reserved = A.SumReserved
FROM (SELECT id, SUM(reserved) AS SumReserved
FROM ' + @database_name + '..sysindexes
WHERE indid IN (0, 1, 255)
GROUP BY id) AS A
JOIN #tmp_spaceused ON A.id = #tmp_spaceused.id'

EXEC (@cmd)

SET @cmd =
'UPDATE #tmp_spaceused
SET data = C.data
FROM (SELECT A.id, A.SumDpages + ISNULL(B.SumUsed, 0) AS data
FROM (SELECT id, SUM(dpages) AS SumDpages
FROM ' + @database_name + '..sysindexes
WHERE indid IN (0, 1)
GROUP BY id) AS A
LEFT JOIN
(SELECT id, ISNULL(SUM(used), 0) AS SumUsed
FROM ' + @database_name + '..sysindexes
WHERE indid = 255
GROUP BY id) AS B
ON A.id = B.id) AS C
JOIN #tmp_spaceused ON C.id = #tmp_spaceused.id'

EXEC (@cmd)

SET @cmd =
'UPDATE #tmp_spaceused
SET index_size = A.SumUsed - #tmp_spaceused.data
FROM (SELECT id, SUM(used) AS SumUsed
FROM ' + @database_name + '..sysindexes
WHERE indid IN (0, 1, 255)
GROUP BY id) AS A
JOIN #tmp_spaceused ON A.id = #tmp_spaceused.id'

EXEC (@cmd)

SET @cmd =
'UPDATE #tmp_spaceused
SET unused = #tmp_spaceused.reserved - A.SumUsed
FROM (SELECT id, SUM(used) AS SumUsed
FROM ' + @database_name + '..sysindexes
WHERE indid IN (0, 1, 255)
GROUP BY id) AS A
JOIN #tmp_spaceused ON A.id = #tmp_spaceused.id'

EXEC (@cmd)

UPDATE #tmp_spaceused
SET
reserved = reserved * @bytes,
data = data * @bytes,
index_size = index_size * @bytes,
unused = unused * @bytes

SELECT * FROM #tmp_spaceused ORDER BY tablename

Kristen
Test

22859 Posts

Posted - 2006-04-15 : 01:12:31

"I was hoping to get an idea of how large each index is in bytes"

This is more for all the indexes on a table, aggregated, but in case it does what you want:

http://www.databasejournal.com/scripts/print.php/1450801

Kristen

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-04-15 : 16:05:26

quote:
This is more for all the indexes on a table, aggregated, but in case it does what you want:

http://www.databasejournal.com/scripts/print.php/1450801

That does the same thing that my proc does, except I don't use cursors. Plus, my proc accepts a database name as a parameter.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-17 : 02:31:32

Also refer
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_2248.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources