SQL Server: Number of disk blocks used by index or table

Question

In SQL Server, how can I see the number of disk blocks an index or a tables uses?

This is a better fit for http://dba.stackexchange.com/ – SWeko Apr 05 '11 at 07:17 — SWeko, Apr 05 '11 at 07:17

score 3 · Accepted Answer · answered Apr 05 '11 at 07:18

3

Using this query, you can determine the number of pages (8 KB blocks of space) a SQL Server table uses:

SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) AS TotalSpace, 
    SUM(a.used_pages) AS UsedSpace
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME = 'YourTableNameHere'
GROUP BY 
    t.Name, p.Rows
ORDER BY 
    t.Name

This sums up all the data and index pages a table uses.

answered Apr 05 '11 at 07:18

marc_s

732,580
175
1,330
1,459

Each row in this query must be associated with an index_id or i.object_id, so isn't the query only summarizing up the number of pages used for **indexes**? It can easily be rewritten though... – someName Apr 05 '11 at 07:43
@someName: in a clustered table (table with clustering key), index = 1 is the clustering index which **is** the data (and for a table without clustering key, it's index = 0, the heap) - so this **does** list **data and index** pages – marc_s Apr 05 '11 at 08:18
Aha? Index_id=1 is simply the table itself (if it is clustered)? I didn't knew that. Thanks a lot!!! – someName Apr 05 '11 at 08:28
@someName: yes, index=1 is the **clustered index** (which is on the primary key by default, unless you define something else specifically), and the leaf level of the clustered index **is** the data. – marc_s Apr 05 '11 at 08:30

score 0 · Answer 2 · answered Apr 05 '11 at 11:17

This is the best one I have used up to now. Taken from here

set nocount on
--dbcc updateusage(0) with count_rows

declare @tables table (
   sizerank int identity (1, 1) not null
 , table_name varchar (255) null
 , table_total_size_dec decimal(18, 1) null
 , table_size_measure varchar(10) null
)

insert into @tables (table_name, table_total_size_dec, table_size_measure)
select upper(name) as [table_name]
     , convert(decimal(15, 1), left(sizemb, len(sizemb)-2)) as sizedec
     , right(sizemb, 2) as measure
from      (select convert(varchar(255), so.name) as name
                , case when sum(convert(decimal(15, 1), si.dpages) * convert(decimal(15, 1), 8192)) / 1024 > 1000 
                            then convert(varchar(255), convert(decimal(15, 1), round((sum(convert(decimal(15, 1), si.dpages) * convert(decimal(15, 1), 8192)) / 1048000), 1))) + 'mb'
                       else convert(varchar(255), convert(int, round((sum(convert(decimal(15, 1), si.dpages) * convert(decimal(15, 1), 8192)) / 1024), 0))) + 'kb' 
                       end as sizemb
           from sysindexes si join sysobjects so on si.id = so.id join sysfilegroups sf on si.groupid = sf.groupid
           where objectproperty(so.id, 'IsUserTable') = 1
           group by so.name) as dv
order by right(sizemb, 2) desc, convert(decimal(15, 1), left(sizemb, len(sizemb)-2)) desc

select t.*, indname, indid, convert(decimal(15, 1), left(indsizemb, len(indsizemb)-2)) as indsizedec, right(indsizemb, 2) as indmeasure
from @tables t
left join (
select convert(varchar(255), so.name) as tblname
     , convert(varchar(255), si.name) as indname
     , si.indid
     , case when sum(convert(decimal(15, 1), si.dpages) * convert(decimal(15, 1), 8192)) / 1024 > 1000 
                 then convert(varchar(255), convert(decimal(15, 1), round((sum(convert(decimal(15, 1), si.dpages) * convert(decimal(15, 1), 8192)) / 1048000), 1))) + 'mb' 
            else convert(varchar(255), convert(int, round((sum(convert(decimal(15, 1), si.dpages) * convert(decimal(15, 1), 8192)) / 1024), 0))) + 'kb' 
            end as indsizemb
from sysindexes si join sysobjects so on si.id = so.id join sysfilegroups sf on si.groupid = sf.groupid 
where si.dpages > 0
group by so.name, si.name, si.indid
) as dvtablesindexes on t.table_name = dvtablesindexes.tblname
where table_total_size_dec > 0
order by sizerank, right(indsizemb, 2) desc, convert(decimal(15, 1), left(indsizemb, len(indsizemb)-2)) desc

SQL Server: Number of disk blocks used by index or table

2 Answers2