Performance and data management tools available in Business Central
In this post I want to summarize some of the features present in Business Central (up to preview version 2022Wave1) related to performance and data tools.
Microsoft has been very attentive to performance for a long time and requires that those who develop the APPs be too, so it is making more and more tools available for monitoring based on telemetry (both in-Client session and in AL Development).
Some interesting features
Performance tools
- Performance Profiler (In-Client)
- Performance Counter
- Latest Error
Database tools
- Database Missing Indexes
- Database Wait Statistics
Data Management
- Table Information
- Data Administration (and data archiving)
- Data Export to a File
..let’s test some of them on BC Preview 2022Wave1
Performance Profiler
Performance profiler is a very interesting and useful tool (based on the old Client Monitor that already exists for Dynamics NAV and on BC Windows Client).
Use the performance profiler to record a slow scenario that you can then analyze to see what took a long time. The profiler uses sampling technology, so the results may differ slightly between recordings of the same scenario.
Activities.. like in Debug
..after you can export results to file and open In VSCode to Analyze
Performance Counter – Data Collector
Business Central also provides a Data Collector Set template that you can use to easily create Data Collector Sets that contain all the Business Central performance counters.
With a lot of indicators to-date.
Database tools
Some interesting database functions have been added as virtual tables, gradually others are being added.
Search for ”Database”
Database Missing Indexes
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.
Database Wait Statistics
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.
- Sys.query_store_wait_stats (SQL Server)
- Sys.dm_db_wait_stats (Azure SQL)
Database Locks
The Database Locks page gives a snapshot of all current database locks in SQL Server. It provides information like the table and database resource affected by the lock. Or, the AL object or method that ran the transaction that caused the lock.
Database Access Intent
Businesses need fast access to information, and don’t want other tasks to slow down while the system finds or calculates it. For several reports where you only want to view information, we’ve changed the default database access intent to use the read-only replica.
Table Information
Always used to control the space occupied by tables over time (30 days data consuming etc.)..
Data Administration
Very powerful tool, to archive\delete useless historical data … maybe after having exported \ copied them!!
Latest Error
I get back the last known error that occurred in my session ..
Export to a data file (export NAV Data)
With this function it is possible to export the data of a company (or more companies on File).
.NAVData file
Recently used SQL Mantra Tools software and their consultancy service to improve performance. It is geared towards NAV/BC databases greater focus in performance. I will recommend it for any NAV/BC installation. Visit http://www.sqlmantratools.com. Tool is functional rich and have all the above features and much more and it is superior compared to Performance Tool provided by Microsoft.