Tag: Dynamics Business Central

Business Central Performance Tool

Posted on | by InfoMaster

Business Central Performance Tool. With the Business Central Performance Tool V8 Search XE you can increase the performance, analyze and improve the performance of your Dynamics Business Central or NAV solution.

A typical problem in the Dynamics world...

Some time ago we had an inquiry about the known problems in Business Central / NAV. The SQL Server performance for Dynamics Business Central / NAV would not be better than the updated NAV version 2013. Daily many table locks, frustrated users and an IT partner who is somewhat clueless.

Our offer...

Using V8 Care to analyze the "ACTUAL" state of the SQL Server and the Dynamics Business Central solution and to collect the bottlenecks in the database. True to the new motto "Data is the gold of the 21st century" . To our surprise, we got the answer: "Sufficient data for the analysis of the Dynamics Business Central problems had been collected." Since the interested party did not have its own IT experts in the company, he uses specialized IT service providers who use the SQL Server and Dynamics Business Central care for. It is true that we live in a world in which algorithms make the decisions in many areas about what has to happen if a certain result occurs or a data limit is exceeded. But in many application scenarios, people are and remain the final authority.

Our question, what kind of data was collected? With which SQL Server monitoring tool? There are fantastic real-time SQL Server monitoring tools. Unfortunately, SQL monitoring tools do not provide information about Dynamics AL code.

Thus, when it comes to monitoring IT security systems, the IT admin or chief security officer is ultimately the one who makes the decisions.

For this reason, we would like to briefly discuss the possibilities offered by our analysis tool V8 Search XE .

The problem:

SQL Server is capable of handling queries from a large number of concurrent users. When SQL Server handles requests from many clients, there is a high probability that conflicts will occur because different processes will request access to the same resources at the same time. A conflict where one process is waiting for another process to release a resource is called a block. Although in SQL Server a blocked process usually resolves itself when the first process releases the resource, a process can hold a transaction lock and not release it.

Our solution:

To unblock a blocked process from Dynamics NAV / Business Central, we must first determine which process is the blocking process. And then, if possible, analyze and optimize the Dynamics NAV/BC blocking process.
In V8 Search XE, there are many different ways to identify a blocking and unblocking process, which are listed below:

1. SQL Server: Extended Events
2. SQL Server: Dynamic Management Views (DMV)
3. Windows Trace Events: SQL Trace Events
4. Business Central/NAV Server: C/AL tracing
5. Business Central/NAV Server: Business Central Telemetry Data

 SQL Server: Extended Events

The goal of the Extended Events installed by V8 is to present all the information collected by the Extended Events sessions in a readable form. The SQL queries that exceed the specified threshold (we recommend starting with 10 seconds) are recorded and collected in different Extended Events sessions. The SQL scripts are used as a basis for code analysis in V8 Search XE. In V8 XE Profiler, SQL queries are recorded when they are created by the Dynamics Business Central / NAV object.

V8 Extended Events sessions:
1. blocked_process -> Table locks and deadlocks
2. long_duration -> Long running SQL queries in Business Central/NAV
. 3. v8_user_NAV_Trace -> This session allows you to view SQL queries for all statements issued by the C/AL code (Windows user name with SQL Server SPID). These are stored in the V8 XE profiler and displayed as comments.
/*
Get connection from the pool.
User: ComputerName\WindowsUsername
*/
4. V8_FullSQL_NAV_Trace -> The session allows SQL queries to be displayed for all statements issued by the C/AL code. These are collected and displayed as comments for a complete transaction in the V8 XE profiler of the SQL server.



 SQL Server: Dynamic Management Views (DMV)

"DMVs" are query structures built into SQL Server that provide details about the health and performance of servers and databases. DMVs provide a common mechanism for extracting "all things SQL" as well as Windows operating system performance data. There are several DMV categories that return configuration information and performance data.



 Windows Trace Events: SQL Trace Events

SQL Trace events track a specific set of SQL statements executed by the Business Central Server instance against the Business Central/NAV database on SQL Server.
The event data collected includes: session ID, tenant ID, the Business Central/NAV user, and the SQL statement. The listing is only the SQL portion of the Business Central Server trace events.

Important!
To collect this data, you need one V8 service per Business Central Server instance. This feature is available from version 7 of V8 Services and requires .NET Framework 4.7.2, which is not installed by default on older Dynamics NAV.

It is also possible to collect individual event IDs.

The following table lists a few Dynamics SQL trace events. For example:

IDEvent (task/opcode)What is traced
1ExecuteScalar/StartSQL statements that query a database table and return a single field from a row in the query result.
2ExecuteScalar/StopSQL statements that query a database table and return a single field from a row in the query result.
3ExecuteNonQuery/StartSQL statements that return a number of rows from a database table
4ExecuteNonQuery/StopSQL statements that return a number of rows from a database table
5ExecuteReader/StartSQL statements that return a set of rows from a database table.

If you are using Dynamics Business Central version 18 or higher we recommend you to read this article Watch Business Central table locks live

 Business Central/NAV Server: C/AL tracing

Since the Microsoft Dynamics NAV 2013 version, the server has a feature that allows you to view the AL call stack for SQL commands. Full SQL Trace enables/disables tracing for all new and existing sessions per Dynamics Server instance.

This allows you to view SQL queries for any statements issued by the AL. All SQL statements between consecutive comments correspond to the AL statement starting with the first comment. These comments correspond to events when the connection is retrieved and returned to the Microsoft Dynamics NAV Server connection query. These comments are needed to separate SQL query issues from different clients on the same SQL connection. The SQL statement that matches these comments is issued by Microsoft Dynamics NAV Server, but not by AL. Comments containing only the username also correspond to SQL statements issued by Microsoft Dynamics NAV Server but not by Dyanmics AL code.

For example, Microsoft Dynamics NAV Server runs queries to calculate calculated fields that are displayed in the fact fields. These types of comments are required because Microsoft Dynamics NAV Server may run an SQL query without reconnecting to the pool, and they do not originate from Dynamics AL.

Important! To collect this data, you need one V8 service per Business Central Server instance.

Collecting and analyzing data
V8 Search XE offers two ways to collect event data from Business Central / NAV.

After the SQL trace has been captured, the data is stored in the SQL table. The trace was saved in the "V8 XEvents Full SQL Trace" table in the V8 Search XE database.

2. the complete AL programming code of the respective objects. The data is stored in the "V8 Performance Profiler" table in the V8 Search XE database.

The V8 Source Code Search module allows you to search your entire Dynamics NAV/BC codebase to find where specific code elements are referenced.

 Business Central/NAV Server: Business Central Telemetry Data

Business Central sends telemetry data for various activities and processes in environments and applications/extensions. Up to Business Central version 14 (Role Tailored Client) there was the very helpful function of “SQL Tracing for Microsoft Dynamics Business Central / NAV” (EnableFullALFunctionTracing = true). After Business Central was converted to the web client version (from version 15), the function in the Business Central server options has changed slightly.

Monitoring telemetry data gives you insight into the activity and overall health of your environments/apps, so you can diagnose issues and analyze operations that are impacting performance. The V8 service of V8 Search XE collects telemetry data for analysis and presentation to Business Central or Dynamics NAV on-premise versions.

V8 Service Telemetriedaten: Analyse- und Präsentationsdaten bis BC Version 14 (Role Tailored Client)

V8 Service Full AL Function Tracing: Analyse- und Präsentationsdaten bis BC Version 14 (Role Tailored Client)

V8 Service Telemetriedaten: Analyse- und Präsentationsdaten ab BC Version 15 (Web Client)

Zeigt, welche „SQL SERVER SPID“ in BC welche Session ID ist. Die Information ist für die Analyse von Performanceproblemen enorm wichtig!

SQL Statements von Business Central (sehr wichtig für Tabellensperren und Deadlocks)

The telemetry data is expanded by Microsoft with each new version of Business Central in order to provide more detailed information on any performance bottlenecks.
V8 Search XE can analyze and present telemetry data up to the latest version of Business Central.

Conclusion
This specific data, is very difficult to capture with a real-time SQL Server monitoring tool. You need to monitor all the Business Central Server instances in parallel.

V8 Search XE the Business Central Performance Tool is intended to enable all administrators and developers supporting a Dynamics NAV/BC ERP solution to make a statement about the weaknesses of the SQL commands generated by Dynamics Dynamics Business Central / NAV and to document them.

In any case, it pays off to have the Dynamics NAV / Business Central performance checked by a third party to find out what condition the ERP system and the SQL server are in.
After that, you can consider what options you then have for further action to improve SQL Server performance for Dynamics Business Central.

We will be happy to answer any further questions you may have on this topic in person.

Your dynamicsproject.com Team

SQL Server query optimization for Dynamics ERP

Posted on | by InfoMaster

SQL Server query optimization for Dynamics ERP

SQL Server query optimization for Dynamics ERP

1. Why perform SQL server query optimization for Dynamics ERP systems like Business Central or NAV?

In recent years, you've seen incredible improvements in many areas of database servers:

  • Database optimizers are constantly improving and finding ways to make queries more adaptable and overcome areas of poor optimization.
  • Memory speeds have increased massively due to advances in both memory technology and network bandwidth, CPUs have become much faster, and memory prices have dropped dramatically.

However, there are still professionals (like us) who make their living tuning queries and training others to optimize queries. This process involves finding certain slow queries (long durations) that are critical to Dynamics Business Central or NAV performance. These "slow SQL queries" can result from making strategic changes, whether in code, database structure, NAV server instance configuration, or something else. Optimizations are often necessary to ensure that these specific SQL queries execute consistently at a certain speed or meet a required performance standard.
But so why SQL server query optimization despite all the improvements?

Very simple:

  • Data volumes are also increasing dramatically
  • Customer/user expectations for performance/speed of applications like Dynamics Business Central have also increased
  • Customers/users expect not to wait for results now - they want to see current status immediately

2. Who should perform the SQL query optimization?

SQL query optimization is performed by:

  • Database Administrators
  • C/AL or AL developers who specialize in performance or specifically in databases.

Full-stack developers for AL and C/AL generally do not perform query optimization unless they have a specific interest or work experience. This is more of a specialization than a "quick learn" task, so most full-stack developers simply don't have time and need to hire a more specialized person to help them. Teams that don't have a readily available specialist can periodically bring in consultants like us the DynamicsProject Team to help.

There are also many database administrators who manage SQL databases where only "basic" availability and performance are required. These SQL databases are used by cost-conscious organizations that don't need to tune every database like a "race car": Most of their SQL databases are used by internal users who are used to Dynamics ERP performance and speed moderation.

3. What skills are required in SQL query optimization?

Most of us are not wizards at TSQL, but many DBAs have gotten pretty good at query tuning (e.g., through our V8 Performance Workshops).

A very unscientific estimate of what skills make someone in query optimization:

40% - The time, interest, and resources (including a network of people to ask) to build a comprehensive profile of "performance information" - this is patterns that don't optimize well, boundary conditions where performance becomes poor, understanding trace flags, and configuration on TSQL.

30% - Interest and ability to learn how the database engine optimizes and processes queries using indexes and other resources, and what affects parallelism when running multiple queries simultaneously against a live database.

10% - Understanding of the TSQL language and various ways to rewrite a query in conjunction with Dynamics AL or C/AL programming to produce a specific result set. This often only works with database DevOps.

What is database DevOps?
Let's start with the definition of DevOps. DevOps is a set of practices that combine software development (dev!) and IT operations (ops!) with the goal of delivering more features, fixes, and updates faster, in alignment with business goals. Database DevOps applies the same principles and ensures that AL or C/AL database code is included in the same process as development code.

Database DevOps helps teams further identify and streamline the application development and release process by addressing a known bottleneck: AL or C/AL Dynamics source code changes.

4. SQL query optimization tools and their cooperation.
4.1 Execution plans

How the SQL query from Dynamics Business Central is executed behind the scenes.

  • "Estimated" execution plans show the decisions the optimizer has made to execute the query, including the estimated number of rows that will flow through the different parts of the plan
  • "Actual" execution plans are estimated plans that are updated with runtime statistics, e.g. how many lines have passed through the plan. If the plan is "adaptive", it contains some information about which options were chosen
4.2 Query memory

SQL Server 2016+, all editions

  • This function tracks execution plans and aggregated runtime metrics (duration, CPU usage) along with aggregated wait statistics
  • This also has the possibility to "freeze" plans
  • Query store information is restored with the database itself so that it can be shared between environments as needed.
4.3 Dynamic Management Views and Performance Indicators (DMV)
  • These help to understand the bottlenecks of the whole instance during slow performance
  • Example: Overall maintenance statistics for the instance and metrics on memory latency during the time the queries were poorly executed can help explain whether the query really needs to be optimized
  • Query optimization often requires callbacks for "workload optimization"
  • SQL trace and enhanced event traces (enhanced events are a lightweight performance monitoring system to collect data and are the foundation of V8 Search XE).
  • The "Old" SQL Profiler is difficult to use for query optimization in "live" operations, as they can easily slow down your workload and cause performance issues when tracing.
  • Execution plans (filtering does not help in this case, the plans are all examined / collected and the filter is applied too late).
  • Wait Statistics (filtering can help here, but the collected data is so massive that you have to be very careful - and sorting and querying the collected data is also quite cumbersome
  • "Business Central Server Trace Events" There are two event trace providers that publish different trace events in the event log: Microsoft-DynamicsNAV-Server and Microsoft-DynamicsNAV-Common. The Microsoft-DynamicsNAV-Common provider is for telemetry trace events only. All other events use Microsoft-Dynamics NAV server. Usually, you need to specify the event trace provider in the monitoring tool you use (e.g. V8 Search XE)
  • "SQL Trace Events" trace a specific set of SQL statements executed by the Business Central Server instance against the Business Central database on SQL Server

5. Difficult problems - dispute over resources

It's hard to predict how SQL queries will interact with each other in a live workload

Shared resources:
  • Memory for queries - a certain amount of memory must be allocated for sorting/linking/moving data in a query. Many queries running at the same time that require a lot of memory can cause problems with this. (Sometimes queries assume they need much more of this memory than they need, and it needs to be optimized - this will probably not be aware to users outside of a live workload).
  • The number of queries that make changes and the approach to blocking are difficult to predict outside of a live workload (changes in query plans can cause blocking when they were not previously present)
Changes in server resources - even improvements - can cause blockages if they were not there before
  • Example: Switching to a new server with more memory and faster CPUs resulted in an increase in lock wait times due to reduced in-memory wait times and faster query execution.

6. Is a check in the "live" SQL database required?

Yes. One example of this is parallelism.

Optimizing the degree of parallelism for a workload and for specific queries in that workload is usually quite hardware specific, and you need a live environment.

Workload- „Replays“ sind im Toolkit von SQL-Servern verfügbar, aber sie sind:

  • Repetitions only - you can't meaningfully "boost" the activity (deleting the same lines 10 times is not the same as deleting different lines 10 times)
  • Time-consuming to set up

7. Automated SQL server query optimization: history and development
7.1 Automated plan correction

SQL Server 2017+, Enterprise Edition

  • Built on the query store
  • Detects SQL queries that are sometimes fast and sometimes slow
  • Can only recommend changes if desired and set up
  • Can freeze plans, test to see if it helps, and respond accordingly (Freezing is intended as a temporary solution - it is recommended that a user evaluate the query for optimization as a longer-term solution)
  • Very good function for identifying parameter sniffing
7.2 Intelligent Query Processing
  • "Intelligent Query Processing" (IQP) have been released in the latest versions of SQL Server
  • Several of these features fix common query optimization issues in SQL Server
  • More information: Intelligent Query Processing in SQL Databases.

8. Common errors and pitfalls in SQL Server query optimization.
  • Lack of connection between DBAs and Dynamics development teams
  • Lack of knowledge of the execution plans in the team
  • Lack of understanding of SQL Server isolation levels and "optimistic" options
  • Lack of knowledge of how Dynamics AL or C/AL programming is implemented on the SQL Server

We will be happy to personally answer any further questions you may have on this topic.
Your dynamicsproject.com Team