Index sizes . . .

Question

Post reply

Index sizes . . .

Paul Farnell

SSC Veteran

Points: 208
More actions
October 11, 2010 at 9:35 am

#227912

Hi, I am confused about index sizes and how best to get visability of them. So I have ran -

DBCC SHOWCONTIG (STK_LOCATION2) WITH ALL_INDEXES, TABLERESULTS and get back various columns. How do those equate back to the total indexes size shown in the disk usage report.

IndexName Id Pages Rows Ave Record Size

LOC_PRIMARY2_PK 1 0 406450 5921673 385 570 400.448

IXPERF_16 2 0 36019 5921673 25 54 32.565

LOC_CONCATCODE2 3 0 31871 5921673 21 50 28.565

LOC_SOPINDEX 4 0 27131 5921673 20 41 24.039

The reason I am confused is the top index showed the index usage at 7mb then applying the other 3 makes it 770mb

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

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

spaghettidba SSC Guru Points: 105712 More actions · Answer 1

This is a great script I borrowed from Jeff Moden that should make things a lot clearer:



--===== "Space Used on Steroids"

-- If "UnusedKB" is negative, it's likely you need to run DBCC UpdateUsage on the table.

-- If the RowModCtr is high (contains number of rows inserted/updated/deleted sinse last stats update)

-- you might want to run UPDATE STATISICS on those tables.

-- Jeff Moden

;WITH SpaceUsed AS (

SELECT	DBName       = DB_NAME(),

		Owner        = USER_NAME(so.UID),

		TableName    = so.Name,

		TableID      = so.ID,

		MinRowSize   = MIN(si.MinLen),

		MaxRowSize   = MAX(si.XMaxLen),

		ReservedKB   = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved       ELSE 0 END) * pkb.PageKB,

		DataKB       = SUM(CASE WHEN si.IndID IN (0,1    ) THEN si.DPages         ELSE 0 END) * pkb.PageKB

					 + SUM(CASE WHEN si.IndID IN (    255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,

		IndexKB      = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used           ELSE 0 END) * pkb.PageKB

					 - SUM(CASE WHEN si.IndID IN (0,1    ) THEN si.DPages         ELSE 0 END) * pkb.PageKB

					 - SUM(CASE WHEN si.IndID IN (    255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,

		UnusedKB     = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved       ELSE 0 END) * pkb.PageKB

					 - SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used           ELSE 0 END) * pkb.PageKB,

		Rows         = SUM(CASE WHEN si.IndID IN (0,1    ) THEN si.Rows           ELSE 0 END),

		RowModCtr    = MIN(si.RowModCtr),

		HasTextImage = MAX(CASE WHEN si.IndID IN (    255) THEN 1                 ELSE 0 END),

		HasClustered = MAX(CASE WHEN si.IndID IN (  1    ) THEN 1                 ELSE 0 END)

FROM dbo.SysObjects so,

	dbo.SysIndexes si,

	(

		SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte

		FROM Master.dbo.spt_Values

		WHERE Number = 1   --Identifies the primary row for the given type

			AND Type   = 'E' --Identifies row for system type

	) pkb

WHERE si.ID = so.ID

	AND si.IndID IN (0, --Table w/o Text or Image Data

		1,

		255) --Table w/ Text or Image Data

	AND so.XType = 'U'  --User Tables

	AND PERMISSIONS(so.ID) <> 0

GROUP BY  so.Name,

	so.UID,

	so.ID,

	pkb.PageKB

HAVING SUM(CASE WHEN si.IndID IN (0,1    ) THEN si.Rows           ELSE 0 END) > 100

)

SELECT  DBName,

        Owner = ISNULL(Owner,'dbo'),

        Schema_Name = OBJECT_SCHEMA_NAME(TableID),

        TableName,

        ObjectName = QUOTENAME(DB_NAME()) + '.' + QUOTENAME(OBJECT_SCHEMA_NAME(TableID))  + '.' + QUOTENAME(TableName),

        TableID,

        MinRowSize,

        MaxRowSize,

        ReservedKB,

        DataKB,

        IndexKB,

        UnusedKB,

        Rows,

        RowModCtr,

        HasTextImage,

        HasClustered

FROM    SpaceUsed AS SU

ORDER BY Rows DESC

Hope this helps,

Gianluca

-- Gianluca Sartori

Paul Farnell SSC Veteran Points: 208 More actions · Answer 2

Paul Farnell

SSC Veteran

Points: 208

October 13, 2010 at 5:59 am

#1236097

Thanks for your reply however I want to see how much physical space in KB each index takes up.

Lowell SSC Guru Points: 323482 More actions · Answer 3

Paul Farnell (10/13/2010)

Thanks for your reply however I want to see how much physical space in KB each index takes up.

Paul one of the output columns is [IndexKB] in the script Gianluca provided, which is probably the sum of the space for ALL indexes on the table...you need the same info, but on a per-index , not per-table?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

spaghettidba SSC Guru Points: 105712 More actions · Answer 4

This should do:



SELECT  DBName = DB_NAME(),

        Owner = USER_NAME(so.UID),

        TableName = so.Name,

        TableID = so.ID,

        IndId = si.indid,

        IndName = si.name,

        MinRowSize = si.MinLen,

        MaxRowSize = si.XMaxLen,

        ReservedKB = si.Reserved * pkb.PageKB,

        DataKB = si.DPages * pkb.PageKB + ISNULL(si.Used, 0) * pkb.PageKB,

        IndexKB = si.Used * pkb.PageKB - si.DPages * pkb.PageKB,

        UnusedKB = si.Reserved * pkb.PageKB - si.Used * pkb.PageKB,

        Rows = si.Rows,

        RowModCtr = si.RowModCtr

FROM    dbo.SysObjects so,

        dbo.SysIndexes si,

        (SELECT Low / 1024 AS PageKB --1024 is a binary Kilo-byte

         FROM   Master.dbo.spt_Values

         WHERE  Number = 1   --Identifies the primary row for the given type

                AND Type = 'E' --Identifies row for system type

        ) pkb

WHERE   si.ID = so.ID

        --AND si.IndID IN (0, --Table w/o Text or Image Data

        --        1,

        --        255) --Table w/ Text or Image Data

        AND so.XType = 'U'  --User Tables

        AND PERMISSIONS(so.ID) <> 0

        AND maxirow IS NOT NULL

        AND so.id = OBJECT_ID('youTableNameGoesHere')

-- Gianluca Sartori

Lowell SSC Guru Points: 323482 More actions · Answer 5

i have this saved, which uses the old sysindexes view;

i have not updated it to use sys.indexes and whatever sys view has some index details; this might help:



SELECT object_name(id) as ObjectName

        , indid as index_id

        , name AS index_name

        , STATS_DATE(id, indid) AS statistics_update_date

        ,dpages

,reserved

,used * 1024.0 As UsedKB

,rowcnt

,rowmodctr

FROM sysindexes

-- WHERE id = OBJECT_ID('myschema.mytable')

order by ObjectName, Index_id;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!