May 20, 2008 at 4:01 pm
Hi all,
I'm working on separating tables and indexes across multiple file groups and multiple LUNs that were presented to us by our storage team.
There are two heavily hit tables within the database, one with over 107 million records and growing, and another that is slightly less.
My question is: How can I determine the physical size of the non-clustered indexes on these tables?
The Standard Report for table sizes returns the total size used by indexes, but I want to leave the PK indexes within the same filegroup as the table and have the non-clustered indexes on separate disks. However, i can't determine the size of the disk to project out without knowing the current size of the indexes, and i don't know how much of the total index size is being used by either or.
I can't find anything online, and the dynamic management views i've tried have provided no help thus far.
Thanks for the help,
SK
May 20, 2008 at 4:36 pm
I'm thinking you're looking at the wrong standard report. If you look at the INDEX PHYSICAL STATS reports, it will tell you how many pages each index is taking up. Those are 8K pages, so pages x 8192 should be a rough number as to the size.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 22, 2008 at 10:26 pm
Thanks for that information. That is exactly what I was looking for. I had looked at that report but it didn't provide the size and I wasn't sure how to calculate it. Thanks!
Steve
May 23, 2008 at 11:15 am
If you're using SQL Server 2005 this query will show you the size of nonclustered indexes (both used and reserved) by table in GB:
SELECT
OBJECT_NAME(partition_stats.object_id),
SUM(used_page_count) AS used_page_count,
(SUM(used_page_count) * 8) / 1024.0 / 1024.0 AS used_page_size_GB,
SUM(reserved_page_count) AS reserved_page_count,
(SUM(reserved_page_count) * 8) / 1024.0 / 1024.0 AS reserved_page_size_GB
FROM sys.dm_db_partition_stats AS partition_stats
INNER JOIN sys.indexes AS indexes ON partition_stats.object_id = indexes.object_id
and partition_stats.index_id = indexes.index_id
where indexes.type_desc = 'NONCLUSTERED'
GROUP BY OBJECT_NAME(partition_stats.object_id)
ORDER BY OBJECT_NAME(partition_stats.object_id)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply