Q9.9: SQL to determine space used for an index
OK, here's sp_spaceused reduced to bare essentials:
<![CDATA[
set nocount on
declare @objname varchar(30)
select @objname = "your table"
select index_name = i.name,
i.segment,
rowtotal = rowcnt(i.doampg),
reserved = (reserved_pgs(i.id, i.doampg) +
reserved_pgs(i.id, i.ioampg)),
data = data_pgs(i.id, i.doampg),
index_size = data_pgs(i.id, i.ioampg),
unused = ((reserved_pgs(i.id, i.doampg) +
reserved_pgs(i.id, i.ioampg)) -
(data_pgs(i.id, i.doampg) + data_pgs(i.id,
i.ioampg)))
into #space
from sysindexes i
where i.id = object_id(@objname)
]]>
You can analyse this in a number of ways:
- This query should tally with sp_spaceused @objname:
<![CDATA[
select 'reserved KB' = sum(reserved) * 2,
'Data KB' = sum(data) * 2,
'Index KB' = sum(index_size) * 2,
'Unused KB' = sum(unused) * 2
from #space
]]>
- This one reports space allocation by segment:
<![CDATA[
select 'segment name' = s.name,
'reserved KB' = sum(reserved) * 2,
'Data KB' = sum(data) * 2,
'Index KB' = sum(index_size) * 2,
'Unused KB' = sum(unused) * 2
from #space t,
syssegments s
where t.segment = s.segment
group by s.name
]]>
- This one reports allocations by index:
<![CDATA[
select t.index_name,
s.name,
'reserved KB' = reserved * 2,
'Data KB' = data * 2,
'Index KB' = index_size * 2,
'Unused KB' = unused * 2
from #space t,
syssegments s
where t.segment = s.segment
]]>
If you leave out the where clause in the initial select into,
you can analyse across the whole database.
Hope this points you in the right direction.