October 28, 2008 at 7:30 am
Hi
Does anyone know of a way to get the space used by an individual index, I know how to get the table data/index space used?
Thanks.
October 28, 2008 at 8:29 am
Take a look at sys.dm_db_index_physical_stats. That will give you quite a lot of information about the index, including the number of pages, density, space used, etc.
You can just look at the object size in sys.dm_db_file_space_usage.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 28, 2008 at 12:12 pm
I think sp_spaceused is a little more accurate. The index_size column is derived from the used_page_count, in_row_data_page_count, lob_used_page_count and row_overflow_used_page_count columns from sys.dm_db_partition_stats.
Initially, I was thrown off by the page_count value from sys.dm_db_index_physical_stats because it doesn't always correlate to what sp_spaceused returns. But in actuallity, the index size is more than just space used. It's the difference between total pages and pages used.
DAB
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply