-2

I am trying to get the actually size of records based on the date which they where created. i used a count to get the number of rows in the table then multiply the result by the top average byte. I have created a table that stores the results. I tried to sum the results of the size that are stored in the results tables but the values did not compare to the actually size of the table. is it possible to use the rows to get the actually size of the table based on the date the rows where created. e.g i wanted to calculate the size of the rows that where created between the 1st of November to the end of November. I want to do this for all tables within the database. I am using Microsoft (SQL Server) Management Studio 2016

  DECLARE @numbrowsJ int;
  DECLARE @byteNumberJ decimal (18,5); 
  Declare @tableName varchar(100); --
  DECLARE @Dbname varchar(100);
  Declare @year int;
  set @Dbname = 'Test';
  SET @year = 2017;
  set @tableName = 'ProductRequest'
  set @byteNumberJ = (select TOP 1 avg_record_size_in_bytes FROM 
  sys.dm_db_index_physical_stats
              (DB_ID(@Dbname), OBJECT_ID(@tableName), NULL, NULL , 'DETAILED'));

 select @byteNumberJ
 --set to insert into table then from there  
 Insert Sizing( month, SizeinMb, RowCounts, year, TableName,DatabaseName)    

 SELECT DATENAME(mm,request_date) MONTH, COUNT(*) * @byteNumberJ /1024 As SizeinMB, count (*) RowCounts , DATENAME (yyyy,request_date) As 'Year', @tableName as 'TableName', @Dbname
  FROM ProductRequest
  where YEAR(request_date) = @year 
  Group BY DATENAME(mm,request_date),  DATENAME (yyyy,request_date)

-- The above basically inserts the calculation into a table when ever i do a sum using the query below does a summation of the calculated size.

-- see attached link query result for answer.
  SELECT  
  Sum(RowCounts) as TotalRowCounts
  ,[DatabaseName]
  ,sum (SizeinMb) /1024  As CurrentSizeMB
  FROM Sizing
  where year = 2017 and  DatabaseName ='databasenamegoe' and
  TableName = 'ProductRequest'
  Group by DatabaseName

Query Result How do i get the size to add up to the same value if i was to right click on the table name and select storage as shown below.

Storage

  • You are only getting the leaf level avg size for one index. How about the other label, other index, empty space in pages etc etc? It is not comparable. I am curious to know what is your end goal. – SqlWorldWide Nov 17 '17 at 16:55
0

I have created a table that stores the results. I tried to sum the results of the size that are stored in the results tables but the values did not compare to the actually size of the table.

It's normal. Why do you think that all your pages are full?

Your table seems to have and index, is it clustered index? What is it's key? For example, if you have a PK that is GUID, your index will be always highly fragmented, insertion of one row that causes page split can lead to more pages to be added to your table, so your calculation is wrong.

And even if your table is not fragmented, you don't consider page header that is 96 bytes (to be multiplied by number of pages).

Your database can have 1)RCSI enabled or your table can have 2) instead of trigger+ LOB or row-overflow column, both 1) and 2) add additional 14bytes to every row.

Your table can have row-overflow data, so when your in-row data exeeds 8060 bytes, additional bytes don't pass to another row but are written to row-overflow page (i.e. you have only some bytes that don't fit into a row, but new page is allocated to accomodate that bytes and only a pointer to that new page is put in your original row).

So there are too many things that you did not consider at all.

  • Yes the it does have a clustered index and the index is Guid. it was design to accommodation the passing of GUID from a c# application. you are right. Any suggestion as to how i show go about fixing the calculation? or doing the calculation using a query? it would be much appreciated? – Anonymous Z Nov 17 '17 at 17:03
  • What is your aim? If you want to just estimate monthly growth of your tables, just put actual table sizes in a table once a month along with row count. This way you'll find even the average ratio to which you can multiply your calculations to get more realistic results. – sepupic Nov 17 '17 at 17:11
  • yeah i just want want to just estimate monthly growth of tables. – Anonymous Z Nov 17 '17 at 17:19
  • yeah i know that putting it once a month would be ideal. is there a way for me to get the historically size monthly like for the past previous month? – Anonymous Z Nov 17 '17 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.