Some days ago Microsoft publicly releases the Release Wave 2 plans for Dynamics 365 and Power Platform products and also the Dynamics 365 Business Central Team announces the plans for the upcoming wave.
There are lots of interesting features in this list, but I want to put the emphasys on some of them that can appear minor features but instead in my personal opinion they can change everything in terms of speed.
ReadCommitted as default isolation level
I talked about that in the past here (with some details), but now the time is here… the default locking behavior in AL will change from SerializedRead to ReadCommitted and this will help on minimizing the use of database locks when running business logic (interactive sessions, background jobs, web services etc).
Turn off indexes as a partner
You will be finally able to disable not used indexes on tables. AL developers can get an overview of index cost and usage frequency. They can then turn off indexes that are infrequently used or that are too expensive to maintain. By doing this, the performance of database operations can be greatly improved.
If you disable an index, the query optimizer will not consider that disabled index for creating query execution plans. When you disable an index on a table, SQL Server keeps the index definition in the metadata and the index statistics in nonclustered indexes.
Data loads faster in the server
This is in my opinion the most misterious announcement, but probably the most “revolutionary” for the platform. The official release plan simply says that this upcoming release focuses on optimizing two essential parts of the data stack in the Business Central server:
- The data model for table extensions.
- The use of modern SQL datatypes for Booleans and blobs.
The data model for table extensions? Yes… you read it correctly! This is not any more a mistery and the product team publicly started talking about that also on socials.
Microsoft has a “work in progress” for changing the way tablextension objects are working at the database level. Instead of creating a companion table for every tableextension object you have for a specific table, current plans are now to store all tableextension fields for a specific table in a single companion table.
This means that if you have 5 extensions extending table T1, instead of doing 5 SQL joins (like in the actual runtime), future SQL runtime will do a single join.
When you uninstall a tableextension, the fields will be stop being included in the generated SELECT clause but the fields will still be in the companion table.
Possible problem here could be that he underlying SQL backend has row size limits (8060 KB). But SQL Server now also supports row-overflow storage, which enables variable length columns to be pushed off-row. Only a 24-byte root is stored in the main record for variable length columns pushed out of row.
The maximum amount of data and overhead that is contained in a single row on a page is 8,060 bytes. This doesn’t include the data stored in the text/image page type.
This restriction is relaxed for tables that contain varchar, nvarchar, varbinary, or sql_variant columns. When the total row size of all fixed and variable columns in a table exceeds the 8,060-byte limitation, SQL Server dynamically moves one or more variable length columns to pages in the ROW_OVERFLOW_DATA allocation unit, starting with the column with the largest width. This is done whenever an insert or update operation increases the total size of the row beyond the 8,060-byte limit. When a column is moved to a page in the ROW_OVERFLOW_DATA allocation unit, a 24-byte pointer on the original page in the IN_ROW_DATA allocation unit is maintained. If a subsequent operation reduces the row size, SQL Server dynamically moves the columns back to the original data page. For more information, see Large Row Support.
This is the public plan on this topic and currently a “work in progress” process. But this can change a lot in terms of speed for the platform.
Could this future change break possible “direct-SQL” integrations for Dynamics 365 Business Central on-premises? YES. But in my personal opinion 1) you should not do direct-sql integrations and 2) who cares? 🤪
Add SetBaseLoadFields record API
Also related to this, the future AL language extension will add a new method for the Record object: SetBaseLoadFields. This new method adds the ability to select all fields defined on the base table for initial loading on a record, only delay loading fields defined in table extensions.
Will Dynamics 365 Business Central 2023 Wave 2 release be the fastest Business Central release so far? Probably yes… 😉
Stefano Demiliani 