October 11, 2010 at 9:35 am
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_PK104064505921673385570400.448
IXPERF_1620360195921673255432.565
LOC_CONCATCODE230318715921673215028.565
LOC_SOPINDEX40271315921673204124.039
The reason I am confused is the top index showed the index usage at 7mb then applying the other 3 makes it 770mb
October 11, 2010 at 10:24 am
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 (
SELECTDBName = 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
October 13, 2010 at 5:59 am
Thanks for your reply however I want to see how much physical space in KB each index takes up.
October 13, 2010 at 6:18 am
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
October 13, 2010 at 6:21 am
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
October 13, 2010 at 6:27 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply