Identifizieren von Tabellensperren in Dynamics NAV / Business Central

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


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