Hi everyone,
This might be a tough one, I don't know if this is doable.
Basically I want to create a stored procedure to return estimated size of a
table.
because I don't know how many rows it will have in the future, I want to
calcuate how much disk space it takes to have one row,
then multiply by number of rows I specified. (space calculation for index is
not necessary).
EXEC GetEstimateTableSize @.TableName='Table1', @.NumberOfRows ='3000000'
it'll return value in KBs after I execute proc. possible?If you run :
EXEC dbo.sp_spaceused table_name
You get the current space usage. Divide it by the current number of rows,
and multiply by the projected one.
If the table is completely empty or you'd rather calculate theoretical size,
there are formulas you can use from BOL, or better yet, a lengthy discussion
on internal structures and row sizes in Inside SQL Server 2000.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:uWKIMMBpFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi everyone,
> This might be a tough one, I don't know if this is doable.
> Basically I want to create a stored procedure to return estimated size of
> a table.
> because I don't know how many rows it will have in the future, I want to
> calcuate how much disk space it takes to have one row,
> then multiply by number of rows I specified. (space calculation for index
> is not necessary).
>
> EXEC GetEstimateTableSize @.TableName='Table1', @.NumberOfRows ='3000000'
>
> it'll return value in KBs after I execute proc. possible?
>
>
>
>
>
>|||Have you looked in the Books Online for sp_spaceused? It'll get you
part of the way there because it returns the rows used and the current
size of the table. Here's a quick and dirty stab at it; obviously
it'll need polishing:
This is actually a pretty useful idea; I'm planning on using this
myself.
Stu
DECLARE @.Table varchar(255)
DECLARE @.NumberOfRows int
SET @.Table = 'Splat'
SET @.NumberOfRows = 1
CREATE TABLE #t (name varchar(255),
rows int,
reserved varchar(100),
data varchar(100),
index_size varchar(100),
unused varchar(100))
--how big is the table now?
exec sp_spaceused @.Table
INSERT INTO #t
exec sp_spaceused @.Table
--strip off the ' KB' from the data column
--convert data and rows to decimal, and
--divide data by number of rows and multiply by number of anticipated
rows
SELECT rows, data, (data/rows) * @.NumberOfRows
FROM ( SELECT rows = CONVERT(decimal(32,3), rows),
data = CONVERT(decimal(32,3), LEFT(data, LEN(data)-3))
FROM #t) x
DROP TABLE #t
HTH,
Stu|||The following may help:
http://www.microsoft.com/downloads/...&displaylang=en
If you need it in a SP, try to understand the formulas used in the
spreadsheet and translate them in T-SQL (using the data from syscolumns
and other system tables).
Razvan|||I see 4 columns called reserved , index_size, unused, data
I guess I need to add 4 columns to get the total size, then divide by number
of row to find out how much disk space per row?
then disk space per row * estimate number of rows to find out estimate size?
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:etjHceBpFHA.2888@.TK2MSFTNGP10.phx.gbl...
> If you run :
> EXEC dbo.sp_spaceused table_name
> You get the current space usage. Divide it by the current number of rows,
> and multiply by the projected one.
> If the table is completely empty or you'd rather calculate theoretical
> size, there are formulas you can use from BOL, or better yet, a lengthy
> discussion on internal structures and row sizes in Inside SQL Server 2000.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:uWKIMMBpFHA.3936@.TK2MSFTNGP10.phx.gbl...
>|||You need the data + index_size.
Reserved includes: data + index_size + unused.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Britney" wrote:
> I see 4 columns called reserved , index_size, unused, data
> I guess I need to add 4 columns to get the total size, then divide by numb
er
> of row to find out how much disk space per row?
> then disk space per row * estimate number of rows to find out estimate siz
e?
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in messa
ge
> news:etjHceBpFHA.2888@.TK2MSFTNGP10.phx.gbl...
>
>|||but I have a NTEXT column,
I don't think it can calculate NTEXT.
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1124386169.690259.289640@.z14g2000cwz.googlegroups.com...
> The following may help:
> http://www.microsoft.com/downloads/...&displaylang=en
> If you need it in a SP, try to understand the formulas used in the
> spreadsheet and translate them in T-SQL (using the data from syscolumns
> and other system tables).
> Razvan
>|||Indeed, ntext columns are not covered by the spreadsheed.
In the DataSizer.doc file, they wrote:
The tool does not include the formula to estimate the size of a table
that has Text columns. Not NULL text values consume 16 bytes in the
data row and have a minimum size of 84 bytes on the text page. Text
values are packed onto text pages with the same algorithm as data
rows so it should be possible to estimate the size of text data
storage using the HEAP table spreadsheet if you know the average size
of your text values.
Razvan|||if I use sp_spaceused 'tablename' command,
if that table has a few NText Columns,
I think it will calculate total spaces including disk spaces for NTEXT
column, correct?
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1124483366.622335.202490@.g49g2000cwa.googlegroups.com...
> Indeed, ntext columns are not covered by the spreadsheed.
> In the DataSizer.doc file, they wrote:
> The tool does not include the formula to estimate the size of a table
> that has Text columns. Not NULL text values consume 16 bytes in the
> data row and have a minimum size of 84 bytes on the text page. Text
> values are packed onto text pages with the same algorithm as data
> rows so it should be possible to estimate the size of text data
> storage using the HEAP table spreadsheet if you know the average size
> of your text values.
> Razvan
>
