Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

List all indexes with keys, description and size Expand / Collapse
Author
Message
Posted Sunday, September 30, 2007 11:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:36 AM
Points: 12, Visits: 154
Comments posted to this topic are about the item List all indexes with keys, description and size
Post #404843
Posted Friday, January 4, 2008 2:30 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 9:19 AM
Points: 475, Visits: 376
While trying to find more about sizes of specific indexes, unlike the sum of used space as returned with sp_spaceused, I changed the queries used by sp_spaceused a bit to return individual index sizes.
At a first glans the sum of the returned sizes seems to fit the sum returned by sp_spaceused itself, so this query could be adapted to filter a specific index name (joined with sys.indexes) etc.
Maybe someone else is trying to find out about specific index sizes too, so you might have a look (still trying to restructure this query a bit, too make it smoother, this is just a first shot ;) ):

SELECT
reserved_page_count,
used_page_count,
CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END pages,
CASE
WHEN (index_id < 2) THEN row_count
ELSE 0
END rows,
LTRIM (STR ((CASE WHEN used_page_count >
(CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END)
THEN (used_page_count -
(CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END) )
ELSE 0 END) * 8, 15, 0) + ' KB') indexsize
FROM sys.dm_db_partition_stats
WHERE object_id = 546100986 -- filter for object_id of a table here
AND index_id in (1,2,5)
Post #438809
Posted Friday, January 4, 2008 6:04 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 9:19 AM
Points: 475, Visits: 376
After putting together the previous query, I came along this undocumented sp:

sp_MSIndexSpace tablename

which also returns the sizes for each single index. Just in case anyone's looking for that... :)
Post #438893
Posted Thursday, September 11, 2008 9:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 30, 2009 3:56 AM
Points: 1, Visits: 8
After hours of looking around on the net, I found this script, copied it and immediately ran it. Let me just add that I am by now means a DBA or anything similar.

When the results came back, I copied it to Excel and summed it. It came up with a total of 19Gb worth of indexes. I thought that this couldn't be as te database is 24Gb in total. I ran the sp_spaceused against a specific table and compared the two. The results were:

Index Name Size (sp_spaceused) Size (your script)
----------------------------------------------------------------------------
PK_CustID 7872KB 2277

So this leaves me unsure as to what the size actually is. Is it 7.69Mb or 2.22Gb. Could you clarify this for me, please?

Thanks
Post #567851
Posted Friday, September 12, 2008 1:11 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 9:19 AM
Points: 475, Visits: 376
Hello Willie,

you're right, the result from sp_spaceused might differ from the query, since the query itself doesn't add xml indexes or fulltext indexes for the given table, in contrast to sp_spaceused. I didn't need these so I left them away.

Anyway, I modified the query so you can query for a tablename and only one index in this table if desired. If I find some time, I'll add the query for fulltext and xml index space too.

BTW, the original query wasn't put together by me, but it's also a part of the sp_spaceused sys storeproc (sp_helptext 'sp_spaceused'), I just put it together for a quick view while performance tuning a database.

Regards

/*** Show index sizes for each index of a table ***/

SELECT o.name TableName, i.name IndexName, reserved_page_count, used_page_count,
CASE
WHEN (s.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END pages,
row_count,
LTRIM (STR ((CASE WHEN used_page_count >
(CASE
WHEN (s.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END)
THEN (used_page_count -
(CASE
WHEN (s.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END))
ELSE 0 END) * 8, 15, 0) + ' KB') IndexSize
FROM sys.dm_db_partition_stats s
INNER JOIN sys.objects o ON o.object_id = s.object_id
INNER JOIN sys.indexes i ON i.object_id = o.object_id and s.index_id = i.index_id
WHERE o.name = 'TableName'
--AND i.name = 'IndexName' -- use this to query size of one index in the table

Post #568295
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse