set nocount on
declare @freespace as numeric(4, 2)
declare @page_space_available as
bigint
declare @estimated_records_after_one_year as bigint
declare @table_name as sysname
declare @max_record_size as
bigint
declare @records_per_page as
bigint
declare @total_datapages as
bigint
declare @RID_size as bigint
/*******************************************************************/
/********update these variables to get
estimated space usage********/
/*******************************************************************/
/*****/set @freespace = 0.1 /*****/
/*****/set @estimated_records_after_one_year= 3000000 /*****/
/*****/set @table_name = 'TABLE NAME' /*****/
/*******************************************************************/
/*******************************************************************/
/*******************************************************************/
/*A datapage is 8192 bytes, but up to
132 bytes of that can be the header leaving just 8060 bytes for data*/
set @page_space_available =
8060 * (1.0 - @freespace)
if not exists (select 1 from sys.objects where name =
@table_name and type
= 'u')
raiserror ('Table does not exist.',
16, 1)
else
begin
if exists (select 1 from sys.indexes si inner join sys.objects so on so.object_id = si.object_id where so.type = 'u' and so.name = @table_name and si.index_id = 0)
begin
raiserror ('This table is a HEAP and space estimate could be off due to
forwarded records, you should consider adding a clustered index.', 16, 1)
end
if exists (select 1 from sys.columns c inner join sys.objects o on o.object_id = c.object_id and o.name = @table_name and o.type = 'u' and c.max_length = -1)
begin
raiserror ('This table contains datatypes which do not live on the
datapage - such as varchar(max) or xml - this estimate is not entirely valid
since those datatype could reach an infinite size.', 16, 1)
end
select @max_record_size =
sum(case c.max_length when -1 then 10000 else c.max_length end)
from sys.columns c
inner join sys.objects o on o.object_id = c.object_id
and o.name = @table_name
and o.type = 'u'
if @max_record_size >
@page_space_available
begin
select 'This table contains a
datatype which may not live on a datapage so these results should be taken with
a grain of salt' as message
set @max_record_size =
@page_space_available
end
set @records_per_page =
@page_space_available / @max_record_size
set @total_datapages =
@estimated_records_after_one_year /
@records_per_page
select (@total_datapages
* 8192) / (1048576) as estimated_table_space_used_in_mb
--If there is a clustered index then grab the size of it in
bytes
if exists (select 1 from sys.indexes si inner join sys.objects so on so.object_id = si.object_id where so.type = 'u' and so.name = @table_name and si.index_id = 1)
begin
select @RID_size = sum(sc.max_length)
from sys.indexes si
inner join sys.objects so on so.object_id = si.object_id
inner join sys.sysindexkeys sik on
sik.id = so.object_id and sik.indid = si.index_id
inner join sys.columns sc on sc.object_id = so.object_id and sc.column_id = sik.colid
where so.type = 'u'
and so.name = @table_name
and si.index_id = 1
end
else
begin
select @RID_size = 16
/*setting this to 16 bytes if the table is a HEAP -
it's not necessarily 16 bytes but I think
thats a good estimage for the file
identifier, page number, and row number.*/
end
select sum(max_length) as estimated_index_space_used_in_mb
from (
select ((@estimated_records_after_one_year
/ (@page_space_available / (sum(sc.max_length) + @RID_size))) * 8192) / (1048576) as max_length -- include a
constant for nonclustered key value and a row locator
from sys.indexes si
inner join sys.objects so on so.object_id = si.object_id
inner join sys.sysindexkeys sik on
sik.id = so.object_id and sik.indid = si.index_id
inner join sys.columns sc on sc.object_id = so.object_id and sc.column_id = sik.colid
where so.type = 'u'
and so.name = @table_name
and si.index_id <> 1 --Do not include
the clustered index
and si.index_id <> 0 --Do not include
heaps
group by si.name) t
end
No comments:
Post a Comment