Tag: Performance-Optimierung

Performance optimization is divided into four main areas:

  • SQL-Tuning
  • Database Tuning
  • System-Tuning
  • Network tuning

Performance tuning deals with the new structuring of SQL queries / AL source code (Dynamics NAV programming) and offers the most potential for improving performance. Up to 90% of the optimization opportunity lies in the SQL statement or NAV programming.

However, SQL tuning should be done only after network, system and database tuning, otherwise changes in SQL statement or tuned execution plans must be undone or redone.

The goal of V8 Performancepack Tuning is to improve the execution plan so that the result set of SQL query (Dynamics NAV objects such as pages, code units, etc.) is returned in the shortest time and with the smallest number of logical and physical I/O accesses.

When the resources consumed to execute an SQL query are reduced, more users can be served and more statements executed with the same CPU, memory, and disk I/O resources.

SQL tuning is a very complex and extensive topic. It is not always possible to foresee performance problems because the database is constantly changing. For this reason, constant monitoring of the entire database system by database tools is necessary.

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

 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.

This is what the SQL trace event data stored in the SQL table "V8 ETW Log Viewer" in the V8 Search XE database looks like.

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.

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 personally answer any further questions you may have on this topic.
Your dynamicsproject.com Team

Ihr dynamicsproject.com Team

Watch Business Central table locks live

Posted on | by InfoMaster

Watch Business Central table locks live - This has been the topic in the performance optimization of Microsoft Dynamics Business Central for years. Since version 18 of Dynamics Business Central, Microsoft has added new information in this area about the Table lock causers provided. This new information enables administrators and system administrators to take the right steps to provide AL programming colleagues in-house or at partners with the location in the AL code that is causing problems.

V8 Search XE (version 4) now offers all Dynamics Business Central version 18 or higher the ability to view live table locks in Business Central. The ability to identify locks in tables is also available for older Dynamics NAV / Business Central. However, retrieving the information about the table lock originator is only possible via an advanced workflow in V8 Search XE.

See Business Central table locks "live" - what information do the new Dynamics Business Central versions provide?

The database lock controls simultaneous access to the same data by multiple users. To protect a transaction against other transactions that modify the same data, the first transaction locks the data. The lock remains in place until the transaction is completed.

Users can be blocked from completing transactions with the blocked data. They usually receive a message indicating the locked state.

What does the administrator or system administrator see?

Probably most IT departments use a monitoring tool to identify possible bottlenecks or bottlenecks on SQL Server in connection with Business Central. For example, the Activity Monitor in SQL Server Management Studio will show you such a table lock.

No Dynamics Business Central programmer can do anything with this information. But how to solve such problems?

What does the administrator or system administrator see with V8 Search XE?

You get the same information as in the Activity Monitor when a table block occurs. For example, in our example you can see that SPID 54 is blocked by SPID 55. You the the Waitype and the Wairesource of the SQL Server. The type of information provided in almost all monitoring tools as basic information and many, many more details about the SQL Server behavior on the transaction.


But what the SQL Server monitoring tools don't give you is the transaction information from Dynamics Server. And this is where the new information from Dynamics Business Central version 18 or higher comes into play. The V8 Search XE Performance Analyzer offers the possibility to retrieve this information "live"!


You finally have the connection to the database lock on the SQL server and in real time which Dynamics AL object caused the problem.

You can see in this information the SPID that caused the "blocking" in the SQL server. With this information, administrators / system administrators and AL programmers together should be able to solve many problems of Dynamics users and thus provide a performance optimized Dynamics Business Central system to the company.

V8 Search XE offers a large number of integrated performance tools for SQL Server and Dynamics Business Central/NAV. With V8 Search XE you will optimize your Dynamics Business Central system for the highest performance.

We will be happy to personally answer any further questions you may have on this topic.
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

SQL Server Buffer Cache

Posted on | by InfoMaster

SQL Server Buffer Cache

What is the SQL Server Buffer Cache and how does it affect the performance of, for example, Microsoft Dynamics ERP systems.

In SQL Servers, the "Buffer Cache" is the memory used by applications such as Dynamics 365 Business Central to quickly retrieve frequently accessed data. When data is written to or read from a SQL Server database, the Buffer Manager copies it to the "Buffer Cache" (also referred to as Buffer Pool - buffer memory of a database management system). When buffer pool is full, older or less frequently used data pages ("data pages") are moved to disk.

Why should you monitor the buffer cache?

Memory usage can have a significant impact on performance. If there is not enough memory, data pages are often deleted from the buffer cache. This slows down queries because SQL Server must go to disk to find the data page. Then the server must restore the data page to the buffer cache and then read the page before returning the query result.

There are many reasons why queries execute slowly. But if you want to rule out memory problems, take a look at what's going on in the buffer cache. A look inside shows which database, table, or index is loading memory and putting pressure on the buffer.

Use the following SQL query to determine which database is using the most memory (for a Dynamics 365 Business Central system, this should always be the "live" database):

SELECT CASE database_id
		WHEN 32767
			THEN 'ResourceDb'
		ELSE db_name(database_id)
		END AS database_name
	,COUNT(1) / 128 AS megabytes_in_cache
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id)
	,database_id
ORDER BY megabytes_in_cache DESC;

Run this query on the database you want to examine to identify the table or index that occupies the most memory.

USE [your database]

SELECT COUNT(1) / 128 AS megabytes_in_cache
	,name
	,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN (
	SELECT object_name(object_id) AS name
		,index_id
		,allocation_unit_id
	FROM sys.allocation_units AS au
	INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id
		AND (
			au.type = 1
			OR au.type = 3
			)
	
	UNION ALL
	
	SELECT object_name(object_id) AS name
		,index_id
		,allocation_unit_id
	FROM sys.allocation_units AS au
	INNER JOIN sys.partitions AS p ON au.container_id = p.partition_id
		AND au.type = 2
	) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name
	,index_id
ORDER BY megabytes_in_cache DESC;

Manage memory with metrics

Although it is helpful to check databases and indexes for memory overuse, tracking buffer cache metrics is the best way to identify and troubleshoot performance issues.

Here are the top five metrics to monitor to improve memory-related performance issues (SQL Server Buffer Cache):

1. Buffer Cache Hit Ratio

  • This metric shows how SQL Server uses the buffer cache
  • The hit ratio indicates the percentage of page requests that were executed by data pages from the buffer cache compared to all data page requests
  • Pages that are not found in the buffer cache are read from the hard disk, which is much slower
  • The ideal buffer-to-cache ratio is 100 (i.e. SQL Server reads all pages from the buffer cache and none from disk)
  • The recommended buffer cache value is greater than 90.

2. page life expectancy (PLE)

  • The page life expectancy measures how long (in seconds) a data page remains in the buffer cache
  • The longer the PLE is, the better the chance that SQL Server will read the pages from the buffer cache and not have to go to disk
  • When there is not enough memory, data pages are deleted from the buffer cache more frequently to free up memory for new pages
  • In the past, a "normal" PLE value was 300 seconds, when systems had much less memory than today
  • For newer SQL Servers, the following formula is used to determine "good" PLE :Page lifetime = 300 seconds for every 4 GB of RAM on your server.
  • The PLE should remain stable if monitored over time
  • Rapid, frequent decreases indicate memory problems
  • A decline of more than 50% should be investigated immediately

3. Page reads / Sec. (Server)

  • This metric shows how many physical reads (i.e. reads from disk) occurred in one second across all databases in an instance
  • Physical reads are expensive and slow
  • Reduce physical reads by using a larger data cache, smart indexes, and more efficient queries, or by changing the database design
  • The recommended value is below 90.
  • A value above 90 indicates insufficient memory and indexing problems.

4. Page Writes/Sec

  • This metric indicates how many times pages were written to disk in one second at server level
  • The recommended value is below 90.

5. Pages Input/Sec and Pages Output/Sec (Memory Counters)

  • Page input / sec. Is the number of pages that are inserted from the hard disk per second
  • The page output / sec. Is the number of pages written to disk per second to make room in the buffer cache
  • Pages / sec. is the sum of page input / sec. and page output / sec.
  • If the value for pages / sec. is consistently more than 50, additional testing is required

In V8 Search XE you can find a query to measure the SQL Server buffer cache at:

V8 Search SQL Server Buffer Cache Navi

V8 Search SQL Server Buffer Cache

An error-free SQL Server buffer cache is an important component of optimizing SQL Server query speed. Although memory problems are just one of several factors that can slow down query responses, they are relatively easy to identify and troubleshoot.
Tracking these five key metrics can help you keep data pages in the buffer pool longer, so SQL Server doesn't have to waste time searching the disk before returning query results.

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

Dynamics 365 BC SQL Server Performance

Posted on | by InfoMaster

Dynamics 365 BC SQL Server Performance

Dynamics 365 BC SQL Server performance - what should you look for in a monitoring tool? What is required?

A monitoring / analysis tool such as V8 Search XE needs to give you an understanding of the often complex performance patterns that SQL databases exhibit under load. So that you can predict how you will handle database expansion or enlargement. It also needs to help us recognize the symptoms of stress and act before they become problems. The tool should reveal what happened in your database when an intermittent problem occurred and noticeably affected Dynamics 365 BC SQL Server performance.

There are three good reasons to monitor and optimize database performance:

  • Chronic performance problems: Wenn eine Datenbank langsam und konsistent reagiert, wird oft davon ausgegangen, dass Ihr System mehr Ressourcen oder eine andere Technologie benötigen. Der Kauf von „mehr Metall“ oder die Erhöhung Ihrer Cloud-Ressourcen ist teuer, wenn die vollen Kosten berücksichtigt werden. Ebenso steigen die Kosten einer neuen Technologie schnell an, sobald Lizenzen und die Schulung der Mitarbeiter berücksichtigt werden. Solche Investitionen mögen als logische Lösung erscheinen, aber ohne die Ursache für das langsame Verhalten von Dynamics 365 BC/ NAV untersuchen zu können, sind sie sinnlos. Sie müssen wissen, warum Ihre Dynamics Lösung langsam läuft und was das Problem verursacht, bevor Sie davon ausgehen können, dass ein Upgrade die Antwort ist. Das Beheben einiger unerwünschter Abfragen kann zu einer überraschend schnellen Verjüngung einer müden Datenbank führen und diese unnötigen Kosten und Verzögerungen vermeiden.

  • Zeitweise auftretende Leistungsprobleme: Wenn eine Dynamics 365 Business Central SQL Server Datenbank ein zeitweiliges Problem aufweist, ist es einfacher zu lösen, wenn Ihnen Daten zur Verfügung stehen, wann und unter welchen Umständen dies geschieht. Wenn Sie eine historische Aufzeichnung der zuverlässigsten Leistungsindikatoren oder Metriken haben, können Sie die Aktivitäten über verschiedene Zeiträume hinweg vergleichen, und es ist dann viel einfacher, das Problem schneller zu finden und zu beheben.

  • Akute Leistungsprobleme: Wenn Sie die Leistung Ihrer Dynamics SQL Datenbank nicht überwachen, verlassen Sie sich tatsächlich darauf, dass sich Ihre Benutzer beschweren, wenn die Datenbank zu langsam ist, um ihre Arbeit zu erledigen. Dies ist keine gute Idee, da nur wenige Unternehmen etwas tolerieren, das Ihr Geschäft behindert.

With Dynamics 365 BC/NAV Server responsibility, it is possible to use SQL Server's own tools to capture the diagnostic data you need to identify concerning changes or trends. Unless the person responsible for initially responding to performance problems knows where to look, the amount of information available in SQL Server is little more than a distraction. The difficulty is often finding the information needed for your system landscape from thenumerous server metrics that can indicate resource conflicts, error conditions and bottlenecks on SQL Server.

As a result, you should deploy a monitoring tool that will alert you that something is not right with the SQL Server databases and provide just enough information with the warning. Once the type of problem is sufficiently understood, you can use various additional performance metrics available in SQL Server and Dynamics NAV servers as needed.

Signs of possible performance problems

There are several trends that warn you about performance problems. Among the more important ones are:

  • The current performance is unusually slow.
  • The server is under high processor pressure.
  • There are more incidents than usual of blocking, deadlocks, and long-running queries
  • There is an increasing number of wait times of certain types
  • Performance degrades significantly beyond what would be expected as database size increases
  • Memory requirements and the amount of space needed for logging are increasing rapidly

Collecting diagnostic data

Both Windows Server, Dynamics NAV Server, and SQL Server have integrated performance tools, and we have V8 Search XE, a tool to troubleshoot performance issues. Some of these tools provide general metrics, while others are designed to check very specific conditions that can confirm a theory about what is causing a problem. For example, is the cause of a long running query (long duration) a missing index, poor query logic such as a "non-SARGable query filter"? You can use extended events (XEvents), perfmon, DMVs (Dynamic Management Views) and traces to provide this data.

If performance problems persist after the database administrators have been alerted to the problem, you can probably find the root cause with V8 Search XE. However, if the problem is temporary and intermittent, it can be very difficult because the details you need have disappeared. By the time the problem is reported, it's too late and you'll have to wait for it to occur again.

Database administrators need a tool like V8 Search XE that continuously collects the necessary diagnostic data at regular intervals so they can quickly troubleshoot problems regardless of when they occur. An effective monitoring system must work on every SQL Server to connect all the dots across the server landscape.

Performance monitoring requirements

There are a number of ways you can use your monitoring tool to identify and diagnose performance problems based on the diagnostic data you collect. Here you can cover just a few of the more obvious possibilities.

Quickly connect server stress conditions to their root cause

Suppose Dynamics users report slow database performance, and your monitoring tool indicates that CPU utilization is "high". Does this mean that "processor pressure" is causing the performance problem? It depends! If the server is busy, your processors should be working hard to process all the requests from users! A good monitoring tool will help you quickly correlate this one metric with others that confirm (or rule out) CPU pressure and with the specific queries being executed at the time of the problem.

Is the CPU only busy because the server is busy? Is the number of connections or the number of user requests per second higher than normal? The only way you can know for sure is if your monitoring tool has a baseline for each of these metrics. In other words, if you can use it to quickly compare current values with values for the same metrics yesterday at the same time, or at the same time in the last few days or weeks.

Is the CPU increase accompanied by a significant increase in the time that user processes spend just waiting for the CPU? These frequent signal waits, along with high CPU utilization, may indicate CPU pressure. Again, you need a baseline for signal wait times to determine this, and your monitoring tool should make it very easy to add a metric to collect this data if needed.

Your performance metrics baselines may show that CPU pressure is normal during the last week of each month. At this point in the performance investigation, you want to directly associate the server stress condition, whether high CPU, memory, or I/O utilization (or all three), with long-running queries during that period. Excessive CPU utilization is often caused by long-running, complex queries that simply require a lot of processing power. Poorly tuned T-SQL statements and poorly designed indexes can lead to high loads on all resources.

Are there ways to reduce the pressure by optimizing these queries or the database indexes? If not, there are steps you can take to optimize CPU allocation for this period. For example, if they are cloud-based servers, can you elastically expand them to use more processors, optimizing the performance of your important reporting queries?

Without the tools to determine if the bottlenecks are caused by poorly written queries or missing indexes, the temptation is to increase the number of cores. Using a monitoring tool, it is often possible to find a small number of queries that are causing most of the bottlenecks. Once these queries are identified, only the queries and indexes need to be optimized.

Analyze server wait times

Wait in the statistics area with the powerful tool to determine the biggest bottleneck on your SQL servers. Each time a request is forced to wait, SQL Server records the length of the wait and the cause of the wait (the wait type), which generally indicates the resource the request was waiting for but could not retrieve. Wait times can be related to parallelism, I/O slowness, or blocking, for example. This is reported as total aggregate values recorded since the server was last restarted. DBAs typically check wait times to understand loads on the server.

Again, knowing that a particular wait time metric is "high" doesn't mean a problem in itself. Your monitoring tool needs to help you correlate it with other metrics and analyze those wait times to find the actual queries that are affected by them:

A good monitoring tool will also report the most frequent wait times over a period of time so that the DBA can easily identify trends or outliers.

Let's say you observe high CXPACKET waiting times. Is this a problem? This type of waiting occurs whenever queries are executed on multiple processors in parallel. A common misdiagnosis with very high CXPACKET wait times is that the parallel workload is overloading the hard disk I/O subsystem. However, first check the queries affected by these wait times. If it is an OLTP system such as Dynamics 365 BC or NAV, where transactions should be short and fast, you should examine the queries that have parallel schedules and look for optimization opportunities. Similarly, excessive I/O latency, for example, may simply be a sign that a large table is being repeatedly scanned and that an index can support the query.

Fast troubleshooting in real time

As proactive as a DBA wants to be in resolving issues before they become real problems, the monitoring tool always plays an important role in resolving real-time performance issues.

If a important business server is currently experiencing serious performance problems, you should know very quickly which connections and sessions are affected and what activities are taking place on the server. One way a professional monitoring tool can really help is to not only detect excessive "blocking"or report that a deadlock has occurred, for example, but also provide within the alert a list of active processes and some simple visualizations of the blocking or deadlocking chain. The following image shows a blocking chain:

The blocking in SQL Server is like a junction in a traffic jam. The query at the heart of the problem affects other queries, making them wait until the problem query has executed or finished. Common causes are excessive I/O due to scans for large tables or transactions that remain open after errors. To resolve the problem in the short term, the faulty query must be found and possibly the connection broken. A good monitoring tool, such as V8 Search XE identifies the cause of the blockage so that appropriate action can be taken.

A deadlock is a circular lock chain because each processing in the blocking chain waits for one or more other processes in the same blocking chain, so none can complete. This is a severe error condition that SQL Server resolves by terminating and resetting one of the processes. The DBA must immediately investigate to find out what caused the deadlock and take action to prevent it from occurring again.

Before and after: Baselines

Another important use of the monitoring data we collect is to create visual baselines. This helps the DBA understand if performance is predictably degrading over certain time periods or if the server's current performance is typical or unusual. They can also watch for changes as data grows or the system becomes busier over time.

How often do we hear, "Dynamics NAV was slow yesterday" or "It was faster before we released the upgrade" or "NAV is slow right now"? To validate these claims and figure out the root cause, we need resource usage data from yesterday to compare to today.

However, you may notice that the CPU utilization of this processor is higher now than last week. Is this a concerning trend? To support potential optimization efforts, you need to determine if you are seeing what can be described as normal behavior. Or, you are actually seeing a trend that is leading to pressure on this resource. Unless you are taking action, such as looking for ways to better distribute the load among the available processors during these busy times.

Conclusion

SQL databases are extremely complex even before you try to understand their behavior under load in production operations. You need this understanding of performance to predict how you will handle expansion or scale. You need to prevent stress symptoms from becoming problems that impact service. You need the information to make intelligent decisions about hosting. You also need to understand what happened in the Dynamics 365 BC/NAV SQL database when an intermittent problem occurred.

All RDBMSs have diagnostic tools. However, they are best used after understanding the general nature and context of a performance problem. Especially when time is short and problems need to be fixed before users complain. As operations staff workloads increase, it pays to have a system that keeps an eye on the Dynamics 365 Business Central / NAV system and issues alerts when a combination of metrics shows a concerning trend.

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

Dynamics 365 Business Central Wave 2

Posted on | by InfoMaster

Dynamics 365 Business Central 2020 Release Wave 2 is the first release that does not include the classic development environment (also referred to as C/SIDE). With 2020 Release Wave 2, users access Dynamics 365 Business Central via the browser, the Windows 10 desktop app, mobile apps on Android and iOS, or in Outlook.

Dynamics 365 Business Central 2020 Release Wave 2

Microsoft is accelerating its investment in speed and productivity features for the modern browser experience, reaching a major milestone in transforming it into a world-class desktop experience for new and experienced Dynamics users. The modern clients now support so many productivity features that the older Dynamics NAV Windows client for Dynamics 365 Business Central will not be available in the future. The older Dynamics NAV client will continue to be supported in earlier versions according to the support lifecycle.

Strengthening of independent software vendors (ISV)

Meanwhile, the 2019 Release Wave 2 update offers a number of features designed to simplify independent software vendor (ISV) development for new solutions and, in particular, streamline migration from the source code customization model of Dynamics NAV to Dynamics 365 Business Central. In addition, Microsoft will focus on streamlining the path for independent software vendors (ISVs) to bring their solutions - and therefore their customers - online to Dynamics 365 Business Central.

Modern developer tools

The modern development environment based on Visual Studio Code with Azure DevOps and an AL language now supports large app development. Therefore, C/SIDE for Dynamics 365 Business Central will be discontinued in the future. Therefore, we have adjusted ourselves and our performance analysis tool "V8 Search XE" to the new situation. Nevertheless, there will be some changes in the area of performance optimization of Dynamics 365 BC Wave 2 and SQL Server.

 How to work with performance issue in Dynamics 365 Business Central 2020 Release Wave 2 in the future....

What do you do when users complain that "it's slow" in the new modern browser environment? In this section, we would like to describe a troubleshooting process that can help you find the main cause of the problem.
Before you start solving a performance tuning problem, it is often helpful to define and quantify "slow". Try to negotiate acceptable values for the execution time of "slow" operations with BC users. This is sometimes referred to as "establishing a baseline".

To solve a performance problem, the following iterations are often performed:

  • Measure system performance and capture performance data from SQL Server and Dynamics 365 BC servers
  • Search for a bottleneck
  • Remove the bottleneck

and continue until the "slow" operations are comparable to the established baseline.

Monitoring and analysis of telemetry Business Central 2020 Release Wave 2 and higher.

Business Central sends telemetry data for various activities and operations. V8 Search XE collects the telemetry data for Business Central On-Premises via the V8 Services. This means the data is stored centrally for all servers in the V8 database.
By monitoring the telemetry, you get a overview of the activities and overall health of your Dynamics system. It helps you diagnose problems and analyze processes that affect performance.

The following article may help you find more on this topic:
Monitoring and Analyzing Telemetry Business Central | Microsoft Docs

Conclusion

In our opinion, Microsoft has provided many new features in the area of performance optimization for Dynamics Business Central 2020 Release Wave 2. On the other hand, system maintainers and administrators will also have to deal with AL programming just like C/AL developers. We agree with the widespread opinion that we may continue to have our fun with the Dynamics Business Central / NAV onPremise versions for some time with the C/AL programming language. Of course, the know-how about the older NAV / BC versions will continue to play a crucial role for an ERP developer and administrators in the future.
Things change but sometimes also very quickly.

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

Optimize SQL Server performance with Dynamics 365 Business Central on-prem

Posted on | by InfoMaster

Optimize SQL Server performance with Dynamics 365 Business Central on-prem and Dynamics NAV (all previous versions). There are so many factors that contribute to performance issues in the SQL world that it can be downright confusing where to even start.

As the Dynamics application and associated SQL databases grow, things change. Rows are changed, schema is updated, and often things slow down. These performance issues can occur suddenly or intermittently, but it's important to know how to distinguish performance issues so you can fix the individual problem.

The post looks at some steps you should take if your SQL database is behaving strangely. So you can diagnose exactly what is going wrong and focus on speeding things up. Optimizing SQL Server Performance with Dynamics 365 Business Central should start with checking your SQL Server configuration, if possible.


Is it the database? Or the server?

If you have access to the server, you can use a tool like System Monitor or Perfmon to check the processes running on the server itself. High memory usage, high CPU usage, and slow network traffic are red flags where the problem may not be the database itself.

There are many other system diagnostic tools e.g. V8 Search XE that can help with this process. However, one of them should at least point you in the right direction before you spend countless hours looking at tables, indexes and more.

SQL Server has an excellent profiling tool that can both fix existing problems and isolate slow-running queries. XEvent Profiler displays a live view of extended events. It's also a fantastic way to retrieve additional data about your database for performance optimization purposes (e.g. see which queries are executed most often, examine things like memory and CPU usage, identify potential blocks, etc.).

In V8 Search XE, optimizing SQL Server performance with Dynamics 365 Business Central is divided into several areas.


SQL Server System Check
V8 Search XE SQL Optimierung
V8 Search XE SQL Optimierung

V8 Search XE is infinitely useful and a good first step in diagnosing poor performance (or errors in general). It is useful in both development and production environments.

By default, all databases in the SQL Server instance are eligible for the various database-specific checks, and you can use the optional parameter to restrict these checks to specific databases. Valid from SQL Server 2012 onwards.


Performs checks:
  • Processor
  • Memory
  • Pagefile
  • I/O
  • Server
  • Service Accounts
  • Instance
  • Database and tempDB
  • Performance
  • Indexes and Statistics
  • Naming Convention
  • Security
  • Maintenance and Monitoring

These various performance-related queries may be worth reviewing periodically to find potential problems that may arise over time.


Found the perpetrator? Find out now why the query is slow.

Once you have identified a particular query that has been identified as running slowly in the Analysis views in the V8 Search XE "SQL Server Check", you can view the execution plan to see exactly what SQL Server is doing behind the scenes. You can access it from a specific query simply by, the "Show Plan" button in the toolbar or from the ribbon:

V8 Query Plan

Execution plans can easily reveal potential indexing issues that cause your entire table to be scanned instead of looking for exactly what it needs. At first glance, they may seem incredibly complex (and they can be), but once you've worked with them, you'll learn to identify patterns and what they're associated with (e.g., the X operation indicates a missing index for a table, etc.).


A few things to look for here:
  • Look for Warnings - Warnings like "No Join Predicate" should be very obvious red flags that you probably need to address. Most warnings should at least warrant further investigation.
  • Order of operations (cost) - Consider the most expensive operations and determine whether such ordering makes sense. Does a simple join consume 90% of the processing power for the entire call? If so, something could be wrong.
  • Scans vs. Seeks - Neither is necessarily bad, but if one takes much longer than expected (one of them), it's probably worth determining if an index is missing (i.e., SQL Server scans the entire table instead of grabbing a well-defined seek value).

Understanding execution plans comes with experience, and hopefully you won't have to deal with them too often. But if you do, know that they can be a valuable ally in the fight against poor performance.


Investigate possible bad / missing indexes

SQL indexes are the A and O for performance optimization in your Dynamics NAV SQL database. You can think of them as similar to the indexes you might find in a book, in that they allow SQL to "know" where to look for specific data, rather than arbitrarily flipping through page after page.

It's worth noting that indexes are not "error-free" and as with most things, if you use them incorrectly, they can do more harm than good (i.e. imagine going on a scavenger hunt with exceptionally trails or lousy clues).

One great thing is that you don't always have to do this work yourself. People like us, have developed tools like V8 Search XE to recommend indexes based on table usage. Invaluable for optimizing SQL Server performance with Dynamics 365 Business Central on-prem and Dynamics NAV!


Statistics

As valuable as it can be to optimize SQL queries, it's worth noting that SQL Server does some of these "well" and others "poorly"by itself. This is accomplished by using statistics to monitor calls made, caching execution plans, and making judgments about how a particular call could/should best be executed.
Note that we mentioned "good or bad" and that is intentional. Statistics, while incredibly valuable, can also cause problems if they are not captured correctly or are outdated. In most scenarios, you probably won't need to deal with them on a regular basis, but it's important to know that they exist.


As with almost any technology, it is very unlikely that just one of these items will cure all of your performance issues with SQL Servers and Dynamics 365 Business Central or Dynamics NAV. Consider all of these as valuable tools in your troubleshooting arsenal and use them together when problems arise.


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

Troubleshooting performance problems

Posted on | by InfoMaster


Troubleshooting performance issues with Dynamics Business Central or NAV with SQL Servers is difficult.
We the team at dynamicsproject.com are constantly thinking about how we can improve V8 Search XE in the fight against performance issues in Dynamics Business Central or NAV.

Extended Events (XE) is a great diagnostic tool introduced in SQL Server 2008. We use Extended Events (XE) as the main basis for data analysis in V8 Search XE. The V8 XE Profiler allows to run "Full SQL Tracing" in parallel via extended event tracing on all Dynamics NAV instances to capture table locks that may occur on any instance "live".
After turning on tracing, large volumes can be generated in shortest time with the advanced event tracing data. V8 Search XE reads the XEL files created by the asynchronous extended event file target. One event is returned per line in XML format. Reading large result sets may take a long time. By default, the V8_FullSQL_NAV_Trace event creates 5 files of 1 GB. With 5 GB of XEL data (approx. 1 million data records in XML format) the reading into the database tables for analysis purposes could take 45 minutes or longer.

That's a long wait, so we thought and about how to make importing faster.

The new V8 XE Loader.

This utility of V8 Search XE (from version 2.4.5) allows to quickly load the contents of the V8_FullSQL_NAV_Trace*.xel extended event files into a SQL Server database. The basic idea here is to provide the utility with a set of XEL files from the same extended event session. The utility reads the events parallel in multiple threads. This method reduced the time required to process a single file by a factor of ten. In our test, we were able to process the 5 GB XML files in just under 5 minutes and thus had the complete transaction including C/AL codes of the Dynamics NAV/BC session responsible for the table snapshot on the SQL server relatively faster.


This is the kind of C/AL code they should see to troubleshoot performance issues in Dynamics Business Central or NAV!


V8 XE Profiler Result
V8 XE Profiler Result


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

Why is Dynamics NAV slow part 2

Posted on | by InfoMaster

Why is Dynamics NAV slow part 2



Why is Dynamics NAV slow part 2 - we would like to once again bring up the topic for you. The new view of things.

Imagine having a experienced Dynamics NAV partner for the topic of functionalities and customizations in Dynamics NAV, who knows that he has to earn your trust every day. A partner who understands your needs, plans, builds and implements the appropriate solutions.

After some time, NAV often runs very sluggishly, and that in time-critical situations of all things? You have invested a lot to build your ERP system. Time, money and resources have been used, but as soon as you call item card in NAV... you are already annoyed!
Why - because you waited a perceived eternity for the data to be visible. Or important information on orders from the connected web store are not processed quickly... does this sound familiar to you?
Since the NAV system is "live", the support of the ERP system by the on-site partner works only very ...

And what do most Dynamics NAV managers in the company do?

Little to nothing. You talk to the NAV partner. But he can't really get to grips with the problems either. And now? Search for a new Dynamics NAV partner? Search for the "1-click program" for NAV problems on the web?

There is no "one click everything will be fine solution" for NAV problems!!!!



NAV performance optimization tools are unfortunately not so many on the software market. Why actually? Quite simply - it's a gratuitous job to develop something like that. Nevertheless, we develop new products for you with our creativity and passion.

For example, our latest tool V8 Search XE - Dynamics NAV and SQL Server Performance Anaylzer. Why such a new mammoth task again?

"Because we love it... and we're a little nuts!"

Since 2012, with introduction of Dynamics NAV 2013, we have had a vision full of vigor and enthusiasm - to develop tools for Dynamics NAV and SQL Server to make life easier. We planned a whole product line called "Vision 8" . The eight in the name is actually not a "8" but a infinity sign "∞" (could be badly displayed when writing).


Why is Dynamics NAV slow part 2

Companies should focus on the needs of the user during product development. Ideally, the user will find the product useful and easy to use.

Make it simple: It's so easy to say. In reality, simplicity is often the more difficult task.

The challenge lies in the functional diversity of a "NAV Performance Analyzer

The fact that user-centered product development has become increasingly important in companies, especially in recent years, is due to the growing complexity of products such as Dynamics NAV and the SQL Server in users' everyday work.

The more complex the IT infrastructure, the more difficult it is to keep it high-performance and secure. Can high availability for critical enterprise software such as Dynamics NAV still be guaranteed at all? And what possibilities are there to simplify the complex topic of performance analysis?

There are a variety of tools for performance monitoring and optimization of SQL servers. Free monitoring tools with tips, tricks and know-how for performance problems - alternatives to expensive, large and complex IT infrastructure monster tools.

Whether free or expensive; will these tools help you with table locks or NAV clients that don't respond?

NO!

What exactly is the V8 Search XE tool and what makes it so special? What makes V8 Search XE different from other similar diagnostic tools - which actually don't exist; or have you found one on the Internet?

Why is Dynamics NAV slow Part 2 - Conclusion

No matter which way you choose, you should end up making your Dynamics NAV system fit for the future...
So why not go with V8 Search XE ...

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

See also

https://www.dynamicsproject.com/allgemein/sql-server-performance-fuer-dynamics-business-central.html

ASYNC_NETWORK_IO Dynamics NAV

Posted on | by InfoMaster

ASYNC_NETWORK_IO Dynamics NAV

 

Der ASYNC_NETWORK_IO Wartetyp des SQL Servers gehört zu jenen Wartetypen, die sehr oft von DBAs im Aktivitätsmonitor gesehen werden. Beunruhigend ist es, wenn hohe Werte im Monitoring auftauchen, da dieser Wartetyp mit am schwierigsten zu beheben ist. Vor allem in Verbindung mit Dynamics NAV.

Der ursprüngliche Name des „ASYNC_NETWORK_IO“ Wartetyp stammt aus der Zeit der langsamen Ethernet-Geschwindigkeiten von 10 Mbit/s und 100 Mbit/s, die bis Mitte der 2000er Jahre häufig verwendet wurden.

In den meisten Fällen beziehen sich die hohen Werte für diesen Wartetyp nicht tatsächlich auf Netzwerkprobleme (das ist sehr seltener Fall), vor allem mit den sehr schnellen Ethernet-Geschwindigkeiten von 40 Gigabit oder 100 Gigabit Geschwindigkeit.

Übermäßige ASYNC_NETWORK_IO Werte können unter zwei Szenarien auftreten:

Die Sitzung muss darauf warten, dass z. B. der Dynamics NAV RTC Client die von SQL Server empfangenen Daten verarbeitet, und dann das Signal an den SQL Server zu senden, dass der Client neue Daten für die Verarbeitung akzeptiert. Dies ist ein allgemeines Szenario, das schlechtes Anwendungsdesign widerspiegeln kann, und ist die häufigste Ursache für übermäßige „ASYNC_NETWORK_IO“ Warte-Typ-Werte.

Oder die Netzwerk-Bandbreite ist ausgereizt. Ein verstopftes Ethernet ist Verantwortlich für die langsame Datenübertragung. Dadurch wird die Effizienz der Anwendung stark beeinträchtigt.


Ein Problem mit der Client-Anwendung (z. B. Dynamics NAV RTC)


Der häufigste Grund für hohe Werte des „ASYNC_NETWORK_IO“ Wartetyp ist, dass die Anwendung die Daten, die aus SQL Server schnell genug kommen, nicht verarbeiten kann. Wenn von der Anwendung eine große Datenergebnismenge anfordert, wird eine langsame Datenverarbeitung dazu führen, dass sich der Datenspeicher füllt, wodurch verhindert wird, das SQL Server neue Daten an den Client senden kann.

„Row by Agonizing Row“ (RBAR) Verarbeitung (Dynamics NAV = „REPEAT … UNTIL NEXT …“) ist oft die Ursache für ein solches Verhalten und hohe ASYNC_NETWORK_IO Warte Typ Werte. In der RBAR-Anwendungsprogrammierung wird jeweils nur eine Zeile aus der von SQL Server gesendeten Ergebnismenge verarbeitet. In einem solchen Szenario wird die komplette Ergebnismenge, die für die Verarbeitung verfügbar ist, zwischengespeichert und dann wird SQL Server benachrichtigt, dass der Datensatz „verarbeitet“ wurde. Dies ermöglicht es SQL Server, einen neuen Datensatz zu senden, während die Anwendung die Daten aus dem zwischengespeicherten Ergebnissatz verarbeitet.


 

Was tun, wenn hohe „ASYNC_NETWORK_IO“ Warte-Typ-Werte auf dem SQL Server auftreten?


Erste Möglichkeit -> Sie sprechen mit uns.

Bei der Untersuchung der übermäßigen ASYNC_NETWORK_IO Warte-Typ-Werte sollte folgendes überprüft werden:

Stellen Sie sicher, dass für die Clientanwendung entsprechende Sichten erstellt werden, dass die Datenfilterung von der SQL Server-Instanz durchgeführt wird und dadurch eine deutlich geringere Datenmenge an die Clientanwendung gesendet wird. Überprüfen Sie, ob es die Möglichkeit gibt, den angeforderten Datensatz in einer Weise zu reduzieren, um die Datenfilterung auf dem SQL Server direkt durchzuführen.

Im Falle von Einzel- oder Ad-Hock-Abfragen ist sicherzustellen, dass eine WHERE-Klausel (NAV = SETFILTER…, SETRANGE…) überall dort hinzugefügt wird, wo es möglich ist und dass die Abfrage ordnungsgemäß optimiert ist, um den angeforderten Datensatz nur auf die erforderlichen Daten zu beschränken.

Die Verwendung einer berechneten Spalte, die mit einer benutzerdefinierten Funktion (in NAV „FlowFields“) mit einer großen Datenbank definiert ist, ist ein weiterer häufiger Grund für die hohen ASYNC_NETWORK_IO Warte-Typ-Werte aufgrund von RBAR.

Um den den Client mit einer nicht optimalen Abfrage der Datenmenge eingrenzen zu können, sollten Sie im SQL Server die DMV [sys].[dm_exec_sessions] abfragen und dort überprüfen, welcher Task „suspended“ ist und dessen wait_status „ASYNC_NETWORK_IO“ ist.

Wenn die oben beschriebenen Punkte in Koordination mit den Anwendungsentwicklern abgearbeitet sind und der SQL Server immer noch hohe ASYNC_NETWORK_IO Warte-Typ-Werte anzeigt, dann ist es Zeit, zu prüfen, ob das Netzwerk ein solches Verhalten verursacht. Es gibt verschiedene Ursachen, durch die Einschränkung des physischen Netzwerkes, Fehlfunktionen oder einfach wegen falscher Netzwerkeinrichtungen. Folgendes sollte sorgfältig überprüft werden, um das Netzwerk die verursachten ASYNC_NETWORK_IO Wartezeiten zu beheben.


Probleme mit dem Netzwerk?

Überprüfen Sie die Netzwerkbandbreite zwischen dem SQL Server und dem NAV Client. Langsame Netzwerkadapter mit Bandbreite, die nicht der geschätzten Menge an Daten entspricht, die auf der Client-Seite verarbeitet werden sollen, sind oft der Grund für hohe ASYNC_NETWORK_IO Wartezeiten. In vielen Netzwerken sind oft noch 100 Mbit/s Adapter vorhanden. Diese Adapter können oft nicht auf die Anforderungen moderner SQL Server-Datenbanken und die damit verbunde Menge der verarbeiteten Daten antworten. Sogar mit der Umstellung auf 1 Gigabit-Adapter bleiben viele System noch unter den Anforderungen aktueller SQL Serverdatenbanken. 10 Gigabit Netzwerkadaptern werden als das Minimum für die meisten Umgebungen betrachtet, während 100 Gigabit bis 400 Gigabits größen sind, auf die viele Unternehmen in naher Zukunft wechseln werden, wenn sie das nicht schon gemacht haben!

Stellen Sie sicher, dass alle Netzwerk-Komponenten zwischen der SQL Server Instanz und dem Client, z. B. Router, Switches, Kabel ordnungsgemäß konfiguriert sind, voll funktionsfähig und der benötigten Bandbreite entsprechend.

Überprüfen Sie die Batch-Anfragen(SQL Server Performance Counter „Batch Requests“) des SQL Servers. Dieser Performance Counter repräsentiert die Anzahl der SQL-Anweisungen, die pro Sekunde auf dem Server ausgeführt werden. Unserer Meinung nach ist dies eine der besten Metriken für Performanceinformation die, der SQL Server liefert. Allerdings sollte diese Metrik nicht alleine betrachtet werden. Es ist notwendig, die Metrik „Batch Requests“ mit anderen Metriken (insbesondere CPU-Auslastung) zu korrelieren, um einen Gesamteindruck der Leistung Ihres Servers zu erhalten. Server mit Batch-Requests pro Sekunde größer als 1.000 werden als „busy“ betrachtet. Der Wert könnte stark von der tatsächlichen Systemkonfiguration, dem Aktivitätsniveau und der Anzahl der verarbeiteten Transaktionen abhänge

Achten Sie auf den SQL Server Performance Counter „Batch Requests“. Überprüfen Sie die „Batch Requests“ Zählerwerte, da dies auch ein Grund für die hohe ASYNC_NETWORK_IO im Aktivitätsmonitor sein können. Das Ziel ist es, die größte Anzahl von Batch-Anfragen zu erreichen, während die Ressourcen wie CPU, Disk und Speicher niedrig sein sollten.
Öffnen Sie den Windows „Perfmon“ oder einem Third-Party Monitoring Tool (z. B. V8 NAV SQL Studio) und fügen Sie diesen Zähler hinzu. Dieser Zähler befindet sich im Leistungsmonitor unter SQLServer: SQL Statistics: Batch Requests / sec.

Überprüfen Sie die NIC-Bandbreitenauslastung.

Die Netzwerknutzung ist über folgende Formel einfach zu berechnen:

Netzwerkauslastung% = ((Gesamtbytes\Sekunde * 8) / aktuelle Bandbreite) * 100

Sollte das Monitoring regelmäßig Werte größer als 60 % aufweisen, ist eine Umstellung auf einen schnelleren Netzwerkadapter / Netzwerkbandbreite sehr ratsam, um sicherzustellen, dass bei der Datenverarbeitung genügend Bandbreite zugewiesen werden kann.

Stellen Sie sicher, dass Auto-Negotiate der Netzwerkkarte die Netzwerkbandbreite richtig erkennt.

Um die aktuelle Geschwindigkeit aller aktiven Netzwerkverbindungen zu überprüfen, verwenden Sie den folgenden CLI-Befehl:

wmic NIC where NetEnabled=true get Name, Speed

Für den Fall, dass die Auto-Negotiation für einen bestimmten Adapter nicht die richtige Netzwerk-Geschwindigkeit auswählt wurde, ist es möglich, die NIC-Geschwindigkeit in der NIS-Eigenschaften manuell einzurichten.




Wie „busy“ ist Ihr SQL Server und Ihre Dynamics NAV Server?

Sie sind noch kein Kunde? Wir unterstützen Sie.
Mit dem richtigen Dynamics Project Plan können Sie Ihre Lösung optimal einsetzen und dafür sorgen, dass Ihr System nicht nur gut funktioniert, sondern stets die bestmögliche Performance bietet.

Haben Sie Fragen zu Netzwerkkomponenten? Unser Partner Netram Memory GmbH hilft Ihnen gerne weiter.


We will be happy to personally answer any further questions you may have on this topic. Simply contact us via our contact form or by e-mail to info@dynamicsproject.com!

 

Your team from DynamicsProject.com