Tag: Sperrinformation

Identifizieren von Tabellensperren in Dynamics NAV / Business Central

Posted on | by InfoMaster

Identify table locks in Dynamics NAV

The identification of table locks in Dynamics NAV has completely changed since Dynamics NAV 2013 compared to the Navision Classic Client. The way Dynamics NAV users connect to the SQL database has been restructured by the RTC Client. Users connect to the NAV service tier from the Windows client, web client, Sharepoint client or web services. The new so-called "three-tier architecture" separates database, server and client tiers.
The user account that runs the NAV service tier is the only one that actually connects to the SQL database. There are many good reasons for this:

  • Dynamics NAV Server uses an ADO.NET interface, which is a data access layer that supports SQL Server connection pooling. This simplifies the deployment of the Microsoft Dynamics NAV three-tier architecture for deployments where the three tiers are installed on different computers. The Windows Communication Framework (WCF) is used as the communication protocol.
  • It improves security, since in the SQL database there is no need to create a SQL user with login name for each of the required NAV users. As a result, security is simpler, since there is no need for an improved security model to previous versions (client-server connection).

Great stuff! However, a side effect is that it is difficult to see which SQL Server connection (SPID) is assigned to a NAV session. This is a problem when trying to determine a Windows user to a table locking/blocking issue.
In the days of the classic client where you could see the active database sessions and blocking information along with the ability to terminate sessions if necessary are unfortunately history due to the new architecture.

Since customers keep asking us about the problem of identifying table locks in Dynamics NAV and if we didn't have a tool or solution for this problem, we developed a solution. Our first approach to a solution was our V8 NAV SQL Studio. However, the architecture of the software was suboptimal when installing Dynamics NAV service animals on different computers.

Monitor Microsoft Dynamics NAV server events:

For better understanding we would like to briefly explain the difference between Locking, Blocking and Deadlocking for all non SQL Server specialists. Locking: Locks are a mechanism used by Microsoft SQL Server to synchronize simultaneous access to the same data by multiple users.
Before a transaction retrieves a dependency for the current state of data, for example by reading or modifying the data, it must protect itself from the effects that may result if another transaction modifies the same data.

Blocking:

A BLOCKING occurs when two connections need access to the same data item at the same time and one connection is blocked because only one connection can have access at any given time.

This message then appears in NAV:

Microsoft Dynamics NAV
—————————

The operation could not be completed because a record in the '...' table was locked by another user. Perform the action again.
---------
OK
---------

Deadlocks:

DEADLOCKS occur when two tasks permanently block each other because each of the tasks maintains a lock on a resource needed by the other task.

Consequently, transaction A cannot be completed until transaction B is completed. However, transaction B is blocked by transaction A.

This message then appears in NAV:

Microsoft Dynamics NAV
—————————

The operation could not be completed because a record in the '...' table was locked by another user. Perform the action again.
---------
OK
---------

Locking is an integral part of Microsoft SQL Server to ensure parallelism and physical integrity of each transaction. Blocking is bad when a connection/transaction waits unnecessarily for a long time, and deadlocking is a phenomenon that should never occur.

V8 Search XE - Detect and fix deadlocks and blockages in Dynamics NAV Server.

Event tracing in V8 Search XE provides detailed information about what occurs on the Microsoft Dynamics NAV server when users are working with Microsoft Dynamics NAV and blocking or deadlocks occur. All data on specific Dynamics NAV trace events is captured in a V8 SQL Server database. This can help you identify and analyze issues or conditions affecting Dynamics NAV / SQL Server performance.

Identify table locks in Dynamics NAV. With V8 Search XE event tracking, you can dynamically monitor Microsoft Dynamics NAV servers without restarting the server or Microsoft Dynamics NAV clients.

You can use the V8 Search XE to track, for example, the following operations on Microsoft Dynamics NAV Server instances and the SQL Server:

  • Execution of SQL statements from Microsoft Dynamics NAV Server.
  • Execution of NAV C/AL functions.
  • Execution of Microsoft Dynamics NAV reports, queries and XMLports.
  • Process number, status, locks and commands (TSQL and C/AL or AL) executed by the active users.
  • Locked objects as well as the type of locks set up.
  • Full Dynamics NAV SQL trace of a locking process (SPID | DYNAMICS NAV USER).
  • Full Dynamics NAV SQL trace of active users by total waiting time (SPID | WAITTIME).

Which Dynamics BC / NAV Windows user locks the table?

V8 Search XE Tutorial Video



Read more…

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

Back to Top

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

Support-Anrufe – Tabelle gesperrt?

Posted on | by InfoMaster

Support-Anrufe: Welcher Microsoft Dynamics NAV Benutzer hat eine Tabelle gesperrt?

 

Viele von Ihnen kennen die Support-Anrufe von verzweifelten Kunden oder Kollegen, die etwa so klingen: „Hilfe! Niemand kann etwas Buchen!“
„Alles, was wir bekommen, ist eine Nachricht, dass die Tabelle Artikelposten von einem anderen Benutzer gesperrt ist!“

„Find the NAV Session ID that holds the blocking lock“ – gerne, aber wie?

Bis NAV 2009R2 war die Sitzungs-ID in der NAV-Sessions-Liste dasselbe wie die SQL Server SPID. Beginnend mit Microsoft Dynamics NAV 2013, entspricht die Sitzungs-ID nicht mehr den SQL SPID.

Der SQL Server erkennt, dass eine Blockierung durch das NAV Service Tier entsteht mit der SPID XXX und als User wird der Netzwerkdienst angezeigt. AAAAAAHHH!!!“
Und welcher NAV Benutzer verursacht das Problem? Wen soll man anrufen, den Netzwerkdienst??

Dem Anschein nach ist der „Leidensdruck“ noch nicht hoch genug, dass sich Dynamics NAV Benutzer/Kunden von Dynamics NAV 2013 oder höher bei Ihren Microsoft Dynamics-Partner über diesen unbefriedigende bzw. nicht vorhandene Problemlösung beschweren.

Immer wieder Fragen mich meine Kollegen, Kunden und sogar Microsoft Dynamics-Partner nach einer Lösung.

Unsere erste Lösung der V8 Search XE – leider nicht ganz optimal für NAV Service Tiere auf verschieden Computern.

Basierend auf unserer ersten Idee, haben wir aus einer Windows-Applikation einen Windows-Service entwickelt. Damit ist das Problem der NAV Service Tiere auf verschiedenen Maschinen gelöst (guter Tipp von einem NAV Entwickler Kollegen).
Alle Informationen lassen in einer SQL Server Datenbank zusammenfassen. Somit kann die NAV Session ID, User Name und SQL SPID in einer SQL Sicht anzeigt werden. Damit stehen alle Analyse Möglichkeiten des SQL Server in Bezug auf Deadlocks, Blocking Process und vieles mehr für jeden Dynamics NAV Benutzer zur Verfügung.

Die Performance Optimierung in Dynamics NAV kann beginnen, da man jeden NAV Benutzer gezielt ansprechen kann, was er gerade in Dynamics NAV macht und warum die anderen Benutzer gesperrt werden!


Let’s have big fun!


Kleiner Hinweis:
Die Dynamics NAV Benutzer können in den seltensten Fällen etwas für das Sperren der Tabellen. In 95% der Fälle ist der Dynamics NAV C/AL Source Code die Ursache – nicht der NAV Benutzer!


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.

Ihr Nutzen: weniger Support-Anrufe!

 


Unser Workshop-Angebot – V8 Performance Management Pack


  • 1 Tag Vor-Ort- oder Analyse per Remote-Meetimg / Software installation durch/mit einen Mitarbeiter unseres Dynamics Project Teams
  • Installation, Konfiguration und Verwaltung von V8 Search XE
  • Konzept zur weiteren Vorgehensweise mit Detail-Anweisungen zur Dynamics NAV und SQL Server Optimierung


 

Features | SQL Server | Dynamics NAV


  • Echtzeit-System SQL-und Performance-Daten
  • Datenbank- und Protokolldateigröße
  • Sperren und Latches | Pufferverwaltungsstatistiken
  • Arbeitsspeicher | Festplattenleistung
  • Identifizierung von aufwändigen SQL Abfragen anhand von CPU-Zeit und durchschnittlicher Dauer
  • Aktive Benutzerverbindungen
  • Datenprotokollierung als CSV Datei – Datenerfassungsintervall konfigurierbar
  • Nutzung des neuen Features von Microsoft Dynamics NAV 2013 / 2015 / 2016 zur Analyse der C/AL-Aufrufliste für SQL-Befehle
  • Ermittlung welche SQL-Anweisungen aus der selektierten Session des NAV-Client generiert wurden
  • Analyse kostspieligster C/AL Aufrufe in Bezug auf die SQL Performance
  • Sichtbarkeit der Metadaten aller Objekte aus der NAV 2013 / 2015 / 2016 Objekt-Metadatentabelle
  • C/AL Source Code Compare and Merge Tool
  • C/AL Compare Html Documentation
  • Dynamics NAV Object Store incl. Function “Where Used?””
  • XE Explorer für SQL Server Extended Events
  • SQL Live Monitor
  • Html SQL Server Dokumenter
  • Vergleichen von Datenbankschemas
  • Transact-SQL-Editor
  • PowerShell Editor
  • SQL System Health Reports


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