A database sizing query
I am regularly calculating database sizing details for various tasks & use a few scripts for different scenarios. For example, when investigating a performance problem on a database I've not seen before, I usually grab a snapshot of which are the biggest tables broken down by individual indexe sizes. I then identify which tables / indexes are the largest & keep that list in the back of my mind when making other observations.
There are lots of scripts to do this, but a few people have asked me for the one I use, so I'll post it here in case it's useful to anyone. Heck, I'll be able to grab it occassionally myself when I need it as well! (c: It's a nasty little script & can prolly be improved so grab the sizzors & cut it up if you feel inclined.
You toggle the dbcc statement on / off to either update the sysobjects dpages & rowcnt columns. I usually do this on a backup of a prod database so I'm not interrupting online users, but I've often found it runs fast enough to get away with in periods of low activity. If you're only after specific tables, you can add params to that dbcc command to focus it's work to the tables you're interested in.
The script's output format is fairly straight forward - it includes the table name, total table size (all indexes), index name, indexid & index size.
I'll often grab the first ten rows, throw them into a spreadsheet graph & use the output for a nice visual view of the biggest db object sizes. Often, the DBA won't ever have seen that info & is sometimes very surprised to see how big some tables in his / her db really are..
Anyway, enough blab - here's the nasty stuff.
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