Fix Business Central Slow Performance: Full Walkthrough of Performance Insights

Introduction 0:00

Welcome to another edition focusing on helping you to unlock peak performance from Microsoft Dynamics 365 Business Central. Today we will explore how to proactively diagnose and monitor performance issues. Without the right insights and the right tool, valuable time, money and effort can be lost. To help you pinpoint the root cause of a performance problem with precision, let's introduce Performance Insights for Business Central from SQL Mantra Tools.

What Performance Insights covers 0:33

Built on over 25 years of performance tuning experience, Performance Insights is designed to work with both on-premises and SaaS environments, giving you flexibility and ease of use, with many advanced, practical features designed specifically for Business Central. Getting started is simple: log in to the SQL Mantra Tools website, select your customer, go to the licence and open the Performance Dashboard.

Within moments you have a clear view of the four areas that most affect system performance: slow queries, deadlocks, blocking and network speed. It highlights the complex interaction between extensions in a live Business Central environment in a matter of minutes, so you can act before issues escalate and keep your Business Central system running at its best.

Logging in and the Performance Dashboard 1:48

Here we log in as Cronus, open Manage Customers and select the Cronus customer. Clicking the key icon shows that Cronus has two sites, the UK and Germany; customers can have several Performance Insights subscriptions, each monitoring its own site. We select the UK site. Once connected and authenticated, you see the four main performance areas: slow queries, deadlocks, lock timeouts and network speed. Let's start with slow query analysis.

Slow query time series and zooming into peaks 2:57

The menu gives you every analysis. The time series analysis shows how the speed of the system has changed over the last month, with the date and hour on the horizontal axis and slow execution time on the vertical axis: the higher the bar, the slower the performance. You can zoom into a peak by drawing a rectangle with the mouse. Here the slowness was recorded on 17 April at 12 o'clock, so we zoom into the 11 and 12 o'clock peaks by setting the from and to date and time and clicking Search. The analysis is now broken down into minutes.

All dates and times in your own time zone 5:03

These dates and times are in the customer's own time zone, so you don't need to convert anything in your head. That is especially useful for customers in the USA and Europe, because the raw data from Microsoft is in UTC. The date and time filters you enter are in your local time too, and by specifying minutes in the filter you can zoom in to minute level.

Named Business Central user analysis 5:47

Next, the users involved in the slowness, one of the key features of Performance Insights. Every incident is shown against the named Business Central user. Cronus is an LS Retail customer with a mixture of store and head office users, and here you can see which stores and head office users experienced slowness. The pie chart shows which stores were affected most, and the table below gives more detail. In practice it is very useful, and often necessary, to filter by user during a performance investigation. With Performance Insights, you can filter, analyse and report every performance issue by Business Central user.

Fuzzy filtering on users and groups 6:49

Performance Insights lets you fuzzy filter users. If you are only interested in store users, type store in the user filter and only store users are shown; all head office users are removed, and the filter stays in place as you move between analyses.

Slow queries by AL code, SQL code and call stack 7:12

Slow query analysis by AL code shows the AL code that contributed to the slowness, identifying the source of the problem and the interaction between extensions. You can download the details to Excel and share them with extension publishers and your development team. In Excel you can open the call stack, which shows the hierarchy of the code: in this typical POS operation, the first entry point is where the POS user interacts with the screen, which calls one function, then the next, all the way down to where the performance problem most likely lies.

You can look at the SQL code contributing to the slowness as well as the AL code. Developers often need to look at the AL code and SQL code together to piece together the bottleneck and come up with the right solution. You can sort any column, page through the table, and export the current view to Excel.

Analysis by object and extension 9:05

For system administrators and IT managers, the object analysis gives an overview of where performance issues exist in Business Central. It shows the extension name, publisher, object type, number and name, so the problem can be categorised and assigned to the right extension publisher in a complex live environment with many extensions. The extension analysis gives a high-level view: with all filters reset, the Base Application contributes most of the slowness here, followed by Magento and then LS Central, which tells managers where to focus performance improvements first.

Individual and combined users, by date and by hour 10:22

As well as groups such as store, you can filter on an individual user. Typing Edward shows only Edward's sessions and the slow queries he experienced in the selected time window, and every other analysis, such as AL code and SQL code, keeps that filter. The by-date analysis shows that Edward's problems were mostly on 9 April, and the by-hour analysis narrows it to 9am that day.

You can combine users too. Selecting Edward and Adam shows the combined slowness both experienced, and you can keep adding users with a pipe (|) as the separator, for example store|adam for all store users plus Adam. A not-equal sign (<>) shows everyone except those users, and the user list updates instantly. You can also filter on part of a name: entering ri performs a case-insensitive match, so Bristol, Cambridge, Victoria and Brighton are all included.

The by-date analysis gives a daily view of how the speed of Business Central has changed, and the by-hour analysis an hourly view. Fuzzy filtering on Business Central users, analysis broken down by user, automatic conversion to your local time zone and all the other features we have seen come built in, ready to use.

Filtering tables and sharing with developers 15:27

Any table can be filtered further. In the slow queries by object analysis you can see which Business Central objects are causing slowness. To look at the code in, say, the Record Management codeunit, copy its object number, open slow query analysis by AL code and SQL code, and paste the number into the search box: the list is filtered to code from that codeunit, ready to export to Excel for the development team, who can trace the slowness to a specific line of code.

You can also request additional logins free of charge and share live access with third-party extension publishers, solution partners and developers, so everyone works from the same analysis in a secure portal.

Deadlock analysis 17:33

The same analyses are available for deadlocks: how many deadlocks occurred by date and hour, and the AL and SQL code that contributed to them.

Lock timeout (blocking) analysis 18:14

Lock timeouts are, technically, blocking, so this analysis shows how blocking affected the system, again with the same breakdowns. For tips on reducing blocking, see our Business Central and Dynamics NAV performance blog.

Network speed by city, user and object 19:06

Network speed can have a big impact on performance, and a poor connection tends to cause system-wide issues, so Performance Insights reports network speed alongside slow queries, blocking and deadlocks, in the same dashboard. Network speed by city summarises the speed experienced at each location, which is very useful when users work from many sites: IT and network administrators can see which sites need a better internet line, or advise individual users to use a better connection. The benchmark is 10 Mbps, and the lower the number, the worse the network speed; here Lambeth, Royton and Southampton are the slowest.

Network speed by object shows whether slow speeds correlate with the Business Central objects users were working with. Filtering by user, for example to the Wrexham store, shows its network speed is well below the 10 Mbps benchmark, less than half of it. You can download the graph as an image to share with the network manager, or export the data behind any chart as CSV for further analysis.

Partner multi-customer dashboard 22:50

Business Central partners can subscribe to Performance Insights for all their customers. It becomes a tool for support teams, consultants and developers to monitor customer sites proactively and tune them before incidents escalate. Logged in as a partner, you see every customer's Performance Insights dashboard, can open each site, such as Cronus UK and Germany, and can switch from one customer to another in moments. When a customer reports unusual behaviour, the dashboard is the first place to look.

Summary and key benefits 24:22

With Performance Insights you can access, analyse and interact with live performance data for any Business Central system, on-premises or in the cloud, 24/7, from anywhere, including on mobile devices. The whole team sees the same data and analysis, which means faster decisions and quicker resolution, and it works as a collaborative tool with customers, partners, extension publishers, developers and support teams. It acts like CCTV for your production environment: identifying the root cause of performance issues with pinpoint accuracy, saving time and money, and making it easier to support any Business Central system.

Find out more 25:35

For all your Dynamics NAV and Business Central performance challenges, on cloud or on-premises, visit www.sqlmantratools.com.