how does one determine memory usage required for columns and rows?

Question

Hi

Is the some sort of memory calculation to estimate memory usage of a table based on row counts, column counts and data types?

thanks

Answer 1

Hi @Vilen Moodley ,

No.
The basic unit of data storage in SQL Server is page. So you can use the DBCC PAGE command to view the PAGE information.
The number of rows, columns, and data types of memory are related to the actual data. There is no way to calculate the memory space based on these.
The memory usage of a table depends on the storage structure of the table, whether it is a heap or a B-tree(This depends on whether there is an index on the table and what type of index is created).

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I don't think what Seeya says is fully accurate. You can indeed compute the some form of row size, given the columns and their data types. Although, for variable-length types, such as nvarchar, you will need to make some assumptions about the average length. Once you have the row size, you can compute how many rows you can fit on a page., and then you can divide the number of rows with the number of rows per page, to get the number of pages.

However, this does not take in account that pages may not be completely full do page splits, deletes etc. So what you get is an ideal value.

And, no, I don't have the formulas around. I know the sizes for the data types, but there is a fixed row overhead that I don't recall on the top of my head.

I think the main reason why you would do a calculation is when you want estimate how much space your table will take when you are still in the design stage. Once the table is live, the computation is less interesting.

Answer 3

calculation to estimate memory usage of a table based on row counts, column counts and data types?

You can and it is documented at Estimate the Size of a Table with follow up article about size of indexes; but it's onlky an estimation, the real size can be higher e.g. caused by index fragmentation etc.