Business Central 2022 wave 1 (BC20) new features: Database Wait Statistics page and Database Missing Indexes page

Dynamics 365 Business Central

Hi, Readers.
The preview environment for Dynamics 365 Business Central 2022 release wave 1 (BC20) is available. Learn more: Link

I will continue to test and share some new features that I hope will be helpful.
In this post, I will share two new pages related to performance insights, Database Wait Statistics page and Database Missing Indexes page.

Let’s see more details.

Performance – partners can get insights into database wait statistics:

Business value:

Developers and administrators can get insights into database performance.

Feature details:

The database for a Business Central environment makes performance counters and information about SQL queries available through Dynamic Management Views (DMVs). It requires direct database access to be able to obtain this information, and for Business Central online environments, this is not possible due to security restrictions.

With this capability, the Business Central platform will make data about database wait statistics as a virtual table, so that the data can be accessed from AL code. We will also add a page on top of the new table to make it easy to get the data without having to write code.

https://docs.microsoft.com/en-us/dynamics365-release-plan/2022wave1/smb/dynamics365-business-central/performance-partners-get-database-insights-through-virtual-tables-in-client-pages

Choose Tell Me icon, enter Database Wait Statistics, and then choose the related link.

This page shows a snapshot of wait time statistics on the tenant database. The statistics are aggregated since the time that the database was started and the different wait types are also aggregated into wait categories (see the documentation for the SQL Server dynamic management view sys.query_store_wait_stats for more information). See the documentation for system dynamic management views sys.dm_db_wait_stats (Azure SQL database) or sys.dm_os_wait_stats (SQL Server) for more information on wait statistics.

PS: sys.dm_db_wait_stats (Azure SQL Database)

The fields info:
Wait Category: Name of the wait category
Waiting Tasks Count: Number of waits on this wait type. This counter is incremented at the start of each wait.
Wait Time in ms: Total wait time for this wait type in milliseconds. This time is inclusive of signal_wait_time_ms.
Max Wait Time in ms: Maximum wait time in milliseconds on this wait type.
Signal Wait Time in ms: Sum of the differences between the time that the waiting thread was signaled and when it started running. Measured in milliseconds.
Database start time: The date and time that the database was started. All numbers shown for Database Wait Statistics are collected since this time.

Page Info:
Database Wait Statistics (9520, List)
Database Wait Statistics (2000000235)

The table Database Wait Statistics cannot be read because it contains system data.

Performance-related insights into missing indexes:

Business value:

Developers and administrators can get insights into missing indexes in the database. They can then use a per-tenant extension to add the indexes.

Feature details:

The database for a Business Central environment makes performance counters and information about SQL queries available through Dynamic Management Views (DMVs). It requires direct database access to be able to obtain this information, and for Business Central online environments, this is not possible due to security restrictions.

With this capability, the Business Central platform will make data about missing indexes available as a virtual table, so that the data can be accessed from AL code. We will also add a page on top of the new table to make it easy to get the data without having to write code.

https://docs.microsoft.com/en-us/dynamics365-release-plan/2022wave1/smb/dynamics365-business-central/performance-related-insights-into-missing-indexes

Choose Tell Me icon, enter Database Missing Indexes, and then choose the related link.

This page shows indexes that the SQL query optimizer suggests are added to the database. See the SQL documentation on sys.dm_db_missing_index_details for more information on missing indexes.

PS: sys.dm_db_missing_index_details (Transact-SQL)

The fields info:
Table Name: Name of the table on the database.
Extension Id: Id of the extension that table belongs to.
Index Equality Columns: A list of columns that are used in WHERE equality predicates in the SQL query needing the index.
Index Inequality Columns: A list of columns that are used in WHERE inequality predicates (such as < or >) or ORDER BY in the SQL query needing the index.
Index Include Columns: A list of columns that are part of the SELECT clause in the SQL query needing the index.

Page Info:
Database Missing Indexes (9521, List)
Database Missing Indexes (2000000236)

The table Database Missing Indexes cannot be read because it contains system data.

Update info from Dynamics 365 Business Central Launch Event 2022 Release Wave 1:

END

Hope this will help.

Thanks for reading.

ZHU

コメント

タイトルとURLをコピーしました