And Bank Account Ledger Entry is non-integer?
What's behind Business Central table information
Have you ever asked yourself, looking at the table information in BC, what all these numbers are supposed to mean? How can it be that the record size is not an integer number? Can a record take half a byte in the storage? Or what does it mean when a setup table with a single record takes 16 KB of storage space?
This info in the screenshot just does not seem right, does it?
To answer the question what this table means and how all the table information should be interpreted, let's first have a look at the SQL Server data allocation architecture.
SQL Server Data Allocation
And the first thing we need to keep in mind when we look at the table size is that SQL Server never stores precise information about the size of each record or table. Space for table data is always allocated in 8-kilobyte blocks called pages. Same principle applies to indexes - SQL Server handles data and index pages separately, but the minimum allocation block is always a page. And whenever we want to know a table size, best thing we can get from the server is the number of pages the table occupies.
But "occupies" doesn't mean that the record fills the whole page. Even if it's a tiny record with a single 4-byte Integer field, a whole 8 KB data page will be allocated for it. When more records are inserted into this table, they can reside in the same data page, but two tables cannot share one page.
Microsoft documentation gives a very good introduction into the SQL data storage architecture: https://learn.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver16
And this diagram explains the data allocation principles.
All the data rows in this picture belong to one table and cannot be mixed with other tables' data.
So the first thing we learned for now is that no matter how small the table record is, it can't show less than 8 KB in SQL Server allocation data because an 8 KB page is the smallest allocation unit.
OK, 8 kilobytes is the the resolution limit for an external observer of SQL data. But the "Sales & Receivables Setup" is showing 16 KB, twice that size. Why is it so big when the record size is obviously much smaller?
The answer to this question will need to run a small experiment.
The Experiment
For the experiment, I declared a table which I called MyTestTable, with two fields:
fields
{
field(1; "No."; Integer) {}
field(2; Description; Text[50]){}
}...and started inserting records into the table, varying the NoOfRecords parameter.
for I := 1 to NoOfRecords do begin
MyTestTable."No." := I;
Descr := Format(I);
MyTestTable.Description:= PadStr('', 10 - StrLen(Descr), '0') + Descr;
MyTestTable.Insert();
end;The Description field is declared as Text[50], but its actual length is always 10 characters - PadStr adds leading zeroes to ensure this. Therefore, the actual size of the record in the database is 4 + 22 bytes (No. and Description) + 76 bytes in system fields, which is far from the 8 kilobytes page limit (in fact, each page can store only 8060 bytes of data, but this is not that important for our test at the moment).
For the start, I inserted one record into the the test table, and - the table information shows the same stats as for the Sales & Receivables Setup. The total size is 32 KB, equally split between data and index.
And in order to find out what is hiding behind these numbers, we need to take a step even deeper into SQL data allocation structures.
Indexes and index allocation maps
SQL Server offers various low-level functions to peek into table allocation structures. One of the methods is to query the sys.allocation_units system view, as described here: https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-allocation-units-transact-sql?view=sql-server-ver16.
sys.allocation_units view can give the total count of pages used by table data and indexes, and the space requirement in kilobytes is calculated as the number of pages multiplied by 8.
Now, from this query, we see that the table with a single record has 18 pages reserved for it, and 4 out of these 18 are in use (112 KB or 14 pages are reserved, but not currently in use).
But to dig deeper into the content of those four pages, we need to use other methods. For example, a function dm_db_database_page_allocations which inspects the pages of a table. The syntax of the function call is as follows:
SELECT *
FROM sys.dm_db_database_page_allocations(
DB_ID('CRONUS'),
OBJECT_ID('CRONUS International Ltd_$MyTestTable$df2c06d9-16dc-4d28-806a-32b959417cfc'),
NULL , NULL , 'DETAILED')The output of the function is the list of all reserved pages for the given table. As you can see below, only four lines of the output list have the is_allocated mark and an associated page type.
These are the four pages received from the sys.allocation_units view, but with some additional details. We see one data page allocated for the record, which was explained above. But besides that, there is one page with the type INDEX_PAGE and two other pages bearing the type IAM_PAGE. As I mentioned previously, table data and indexes are handled independently by SQL server and stored in separate pages. Remember that each table in BC always has a unique non-clustered index on the $systemId field? This is what we see here. The index page is allocated to store this index. But what are the other two?
To address the data within an allocation unit, SQL Server uses index allocation maps, data structures containing references to database extents (blocks of 8 pages). And this structure is stored in a separate page, which is marked as IAM_PAGE in the query result.
Another image from Microsoft Learn portal illustrates the structure of references between IAM and data pages.
So, as we can see those 32 KB equally divided between the data and index size values, are in fact distributed in four pages:
Data page containing one table record
Index allocation map for the table data
Index page for the non-clustered index $systemId
Index allocation map for the non-clustered index
This diagram also explains the nature of the 14 unallocated pages. Data space is allocated by pages, but the index allocation map addresses extents, and once the first record is inserted into a table, the database engine reserves a full extent. So the full list of reserved pages includes an IAM page for the table data and the reserved data extent, plus the same structure for the non-clustered index. It does not mean, though, that the table actually occupies the whole volume of 64 KB. According to the SQL Server page and extent architecture, the first extent of a table is a mixed extent and can be shared between multiple tables. When the table volume reaches 8 pages, all subsequent space is reserved in uniform extents which can contain data from one table only.
Business Central way of querying table size
Now let's see what's happening under the hood of Business Central when it queries the table information.
If we capture the SQL query issues by the BC to SQL Server and extract the part directly responsible for the data size estimates, we will find that it reads the system catalog view dm_db_partition_stats (highlighted in the next code snippet).
SELECT
SUM(
case when [PS].index_id in ( 0, 1 )
then [PS].row_count
else 0
end) AS [Row Count],
SUM([PS].used_page_count) * 8 AS [Size],
SUM(
case when [PS].index_id in ( 0, 1 )
then [PS].used_page_count
else 0
end)* 8 AS [Data Size],
SUM(
case when [PS].index_id not in ( 0, 1 )
then [PS].used_page_count
else 0
end)* 8 AS [Index Size],
MAX([P].data_compression) AS [Compression]
FROM [CRONUS].[sys].[dm_db_partition_stats] AS [PS]
INNER JOIN [CRONUS].[sys].[partitions] AS [P]
ON [PS].partition_id = [P].partition_id
WHERE [PS].object_id = [Table Object ID]) AS [TS]The BC query receives the total count of used pages (which includes the data page and the IAM page) and multiplies the number by 8 to calculate the data size.
Same applies for the index space. To differentiate between the table data and the index, BC uses the index_id column from the catalog view. As you can see in the query, all records with index_id 0 or 1 are considered belonging to the data, and all the rest is counted towards the index size.
Number 1 for the index_id in page allocation data identifies the clustered index, whilst 0 corresponds to a heap, or a table without the clustered index. Any index number greater than 1 is a non-clustered index. These details are also explained on Microsoft Learn.
This query confirms the assumption that the Business Central data information statistics includes index allocation maps and data pages in the total data size calculation.
What happens as the table grows bigger
We know that SQL Server does not have such a metric as record size, so can make an educated guess saying that the Record Size value in BC table info is simply the total data size divided by the number of records. Let's add a few more records into the test table to check this assumption.
Ten records in the table, all data still fits in one page. Data size is 16 KB, and the record size has dropped to 1 638.40 bytes - exactly 16 kilobytes divided by 10.
70 records - and the record size changes to 234.06 bytes: 16 384 / 70 = 234.057. This explains how a table can become non-integer.
10 more records, and the trend changes - the record size increases again. Now the total size of all records in the table exceeded the 8060 bytes limit, and the data spilled over one page. But now the size is 48 kilobytes - adding two pages, not one.
Once again, dm_db_database_page_allocations function comes handy to peek into the page allocation details.
The query output shows that indeed, another data page from the reserved extent changed the allocation status to "allocated". But besides this, one more index page is allocated with the index_id = 1. As we remember from the Microsoft documentation reference, index id 1 means the clustered index, which is equivalent to the table data itself.
While the volume of data was small enough to fit into a single page, the index allocation map was pointing directly to the page containing the data. Now, as the data size grew over one page, an intermediate index page was inserted, so now the IAM points to the index page, which in its turn contains references to leaf pages with the table data.
If you are interested to know more about SQL Server index structure, Microsoft Learn portal is always a good source of information: https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms177443(v=sql.105)?redirectedfrom=MSDN
Conclusion: What is the data size?
Now we can tell exactly what all the numbers in the table information mean.
Data Size: Number of pages allocated for the clustered index, including the index allocation map and intermediate index pages, multiplied by 8.
Index Size: The number of pages allocated for all non-clustered indexes, multiplied by 8. This number also includes the IAM for non-clustered indexes.
Record Size: Average space required to store one record, including service information, intermediate pages of the clustered index, free page space, etc. It is calculated as the Data Size divided by the number of records.
Size: The sum of Data Size and Index Size.
The bigger your table grows, the closer the size value is to the actual record size, and this number is mostly irrelevant for single-record setup tables.
Besides, we need to keep in mind that not all records have fixed size. All BC Text and Code fields are stored as nvarchar(n) in the database, and the space allocated for each record depends on the actual length of the text. Which once again illustrates the statement that SQL Server has no such metric as a fixed record size, and the number that we see is an average estimate.
Comentários