0

I have a table 'Connections' with the following schema in SQL Server:

[ID] [int] IDENTITY(1,1) NOT NULL,
[User] [nvarchar](20) NOT NULL,
[UserID] [int] NULL,
[True] [bit] NOT NULL,
[Comment] [nvarchar](max) NOT NULL,
[Created] [datetime] NOT NULL,
[Flag] [bit] NULL,
[Destination] [varchar](20) NULL

and it has a primary key clustered index on the ID column.

I need to get the size generate by the entries on this table per month. I have search for any existing SP, function or any DMV that can help me with this but I only found how to get the size of the table not per row. Also I need the total size of the rows per month so cannot get the total size/minimum/maximum of the rows on the whole table (as the solution provided in other stackexchange posts).

My attempt to this is as follows:

USE DB1;
SELECT DATEPART(year,created),
       DATEPART(month,created),
       (count (*))*(4+2*ISNULL((max(len([User]))),2)+4+1+2*ISNULL((max(len([Comment]))),2)+8+1+ISNULL(max(len([Destination])),2)) 'BytesPerMonth'
FROM Connections
GROUP BY DATEPART(year,created),DATEPART(month,created)

In the above I multiplied the number of rows with the byte size of a row and considered the following:

int - 4 bytes
nvarchar - 2 bytes per character ([nvarchar](max) also take 2 bytes per character, same as if we had [nvarchar](40), correct?)
bit - 1 byte
datetime - 8 bytes
varchar - 1 byte per character

However, this only provides an estimate due to only considering the max length of the variable columns and multiples that (the maximum) by the amount of rows which results in a much bigger value than the actual size of the rows. Is there a way that I can get the actual size per row in this context?

Furthermore, I am aware of the row header which is per row - another 4 bytes per row (currently I did not include this since my result was already huge due to considering the max of the variable columns). Also I have found that I should consider 3 bytes due to the null values and 8 bytes due to the variable columns in my schema, do they need to be considered per row/ per column? How can I calculate the size of the index?

  • 1
    have a look at this. not exactly what you are looking for but can get you started dba.stackexchange.com/questions/25531/… – Bob Klimes May 7 at 16:45
  • 1
    You need to come up with estimates for the average length of your variable columns. Otherwise the huge range between 0 bytes and 2 GB for Comment will make any estimate useless. Then having done that just insert a million rows matching this estimate into an empty table and look at the table size and divide by a million to get a figure that takes into account page overhead – Martin Smith May 7 at 17:27
  • @MartinSmith so instead of where I used max I use avg? Since I already tried that and the number of bytes given are actually far less than the size of the table when all are added together. With the max function, the total bytes is far bigger though. – user1930901 May 7 at 17:31
0

You can use the DATALENGTH function to get the number of bytes for each column in a row. You can sum these up to get the total for the row, get the MIN, MAX, AVG and so forth.

SELECT DATEPART(year,created) AS [Year],
       DATEPART(month,created) AS [Month],
       COUNT(*) AS Rows,
       SUM(DATALENGTH([User]) + DATALENGTH([UserID]) + DATALENGTH([True]) + DATALENGTH([Comment]) + DATALENGTH([Created]) + DATALENGTH([Flag]) + DATALENGTH([Destination])) AS TotalRowSize,
       AVG(DATALENGTH([User]) + DATALENGTH([UserID]) + DATALENGTH([True]) + DATALENGTH([Comment]) + DATALENGTH([Created]) + DATALENGTH([Flag]) + DATALENGTH([Destination])) AS AvgRowSize,
       MIN(DATALENGTH([User]) + DATALENGTH([UserID]) + DATALENGTH([True]) + DATALENGTH([Comment]) + DATALENGTH([Created]) + DATALENGTH([Flag]) + DATALENGTH([Destination])) AS MinRowSize,
       MAX(DATALENGTH([User]) + DATALENGTH([UserID]) + DATALENGTH([True]) + DATALENGTH([Comment]) + DATALENGTH([Created]) + DATALENGTH([Flag]) + DATALENGTH([Destination])) AS MaxRowSize
FROM Connections
GROUP BY DATEPART(year,created),DATEPART(month,created)

The advantage of DATALENGTH is that it provides the actual used bytes for variable length fields, so there is no need to guess/estimate. See this db<>fiddle for a working example.

| improve this answer | |
  • Thanks for this! The total size is still relatively small when compared to the storage size of the table when doing it on a small example. In addition to the above solution, I added a value of 4 bytes with each row for the row size header. For null block and variable block do I need to add another 3 and 8 bytes respectively per row? – user1930901 May 8 at 8:34
  • Where did you get the values for row header, null and variable? DATALENGTH on the columns provides the storage in bytes for that column, whether it is NULL or variable-length, doesn't matter. – HandyD May 10 at 21:59
  • c-sharpcorner.com/article/…. This explains that each row has a row header, a null block and a variable block which consume additional storage size (in addition to the size of the row). – user1930901 May 12 at 18:19
  • For NULL and Variable block you would need to add bytes depending on the number of nullable or variable-length columns you have, however, this isn't necessary unless you want accurate to the byte or kilobyte space measurements. For example, the row header adds 4 bytes to each row size. Over a million rows, this only amounts to 4 MB of space, so the space consumption for these parts of the row is very low and probably inconsequential. Test against a larger data set and you should see the discrepancy between measured size and storage size decrease – HandyD May 12 at 22:36

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.