Retrieving Index Size in SQL Server 2005

I have a seemingly simple task, but the solution isn't quite as
obvious/simple as one would think.
I am trying to find out the disk space used by each of the indexes in my
database. I have been able to get the size used by indexes within each
table (sp_spaceused), but this sums all of the indexes within the table. I
have also been able to get the broken down stats of each index
(dm_db_index_physical_stats), but it doesn't show how much total size the
index consumes.
I can see exactly what I'm looking for in Management Studio, through the
"Disk Usage" report on the Summary tab for my database. It is listed under
the "Disk Space Used by Partitions" section, which shows each index, its
"Total Memory Used", and its "Total Memory Reserved". But I can't manage to
find a query that gives me this information.
Thanks in advance for your help.
Jerad
Jerad Rose,
1 - You can use "Profiler" to trace the batch / statement sent to SQL Server
by the custom report.
2 - Take a look to these catalog views in BOL.
- sys.indexes
- sys.partitions
- sys.allocation_units
3 - You can read more about this in:
Inside Microsoft (r) SQL Server (tm) 2005: The Storage Engine
http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055
Example:
use northwind
go
select
i.[object_id],
i.index_id,
p.partition_number,
p.rows as [#Records],
a.total_pages * 8 as [Reserved(kb)],
a.used_pages * 8 as [Used(kb)]
from
sys.indexes as i
inner join
sys.partitions as p
on i.object_id = p.object_id
and i.index_id = p.index_id
inner join
sys.allocation_units as a
on p.partition_id = a.container_id
where
i.[object_id] = object_id('dbo.order details')
and i.index_id = 1 -- clustered index
order by
p.partition_number
go
AMB
"Jerad Rose" wrote:

> I have a seemingly simple task, but the solution isn't quite as
> obvious/simple as one would think.
> I am trying to find out the disk space used by each of the indexes in my
> database. I have been able to get the size used by indexes within each
> table (sp_spaceused), but this sums all of the indexes within the table. I
> have also been able to get the broken down stats of each index
> (dm_db_index_physical_stats), but it doesn't show how much total size the
> index consumes.
> I can see exactly what I'm looking for in Management Studio, through the
> "Disk Usage" report on the Summary tab for my database. It is listed under
> the "Disk Space Used by Partitions" section, which shows each index, its
> "Total Memory Used", and its "Total Memory Reserved". But I can't manage to
> find a query that gives me this information.
> Thanks in advance for your help.
> Jerad
>
>
|||Thanks for taking the time to respond, Alejandro. Your example works well.
That's the information I needed!
Thanks again.
Jerad
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:6CF06094-5C27-4A2E-B8A2-46FF1C33569E@.microsoft.com...[vbcol=seagreen]
> Jerad Rose,
> 1 - You can use "Profiler" to trace the batch / statement sent to SQL
> Server
> by the custom report.
> 2 - Take a look to these catalog views in BOL.
> - sys.indexes
> - sys.partitions
> - sys.allocation_units
> 3 - You can read more about this in:
> Inside Microsoft (r) SQL Server (tm) 2005: The Storage Engine
> http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055
>
> Example:
> use northwind
> go
> select
> i.[object_id],
> i.index_id,
> p.partition_number,
> p.rows as [#Records],
> a.total_pages * 8 as [Reserved(kb)],
> a.used_pages * 8 as [Used(kb)]
> from
> sys.indexes as i
> inner join
> sys.partitions as p
> on i.object_id = p.object_id
> and i.index_id = p.index_id
> inner join
> sys.allocation_units as a
> on p.partition_id = a.container_id
> where
> i.[object_id] = object_id('dbo.order details')
> and i.index_id = 1 -- clustered index
> order by
> p.partition_number
> go
>
> AMB
> "Jerad Rose" wrote: