question

VilenMoodley-3608 avatar image
0 Votes"
VilenMoodley-3608 asked SeeyaXi-msft commented

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

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

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @VilenMoodley-3608,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it is not help, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @VilenMoodley-3608,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.