|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
estimate size of table based on number of rowsHi 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. Show quote "Britney" <britneychen_2***@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? > > > > > > > > > > > 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? Show quote "Itzik Ben-Gan" <it***@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_2***@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? >> >> >> >> >> >> >> >> >> >> >> > > You need the data + index_size.
Reserved includes: data + index_size + unused. Show quote "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 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" <it***@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_2***@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/details.aspx?FamilyID=564C5704-D4F5-4EE8-9F3C-CB429499D075&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 but I have a NTEXT column,
I don't think it can calculate NTEXT. Show quote "Razvan Socol" <rso***@gmail.com> wrote in message news:1124386169.690259.289640@z14g2000cwz.googlegroups.com... > The following may help: > http://www.microsoft.com/downloads/details.aspx?FamilyID=564C5704-D4F5-4EE8-9F3C-CB429499D075&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? Show quote "Razvan Socol" <rso***@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 >  
Other interesting topics
|
|||||||||||||||||||||||
