Identifying Table Locking in NAV 2013

The way that users connect to the SQL database has changed in NAV 2013. They don’t. Users make a connection to the NAV service tier from the Windows client, web client, SharePoint client or web services.

The user account that is running the NAV service tier is the only user that actually makes connections to the SQL database. There are lots of good reasons for this:

• In most cases it removes the need for the service tier account to be enabled for delegation.
• It improves security as you don’t need to create a user in SQL for each of your NAV users. As a result, security is also simpler – no need for the Enhanced security model of previous versions.
• Improved performance – the service tier takes responsibility for connections to the SQL database and will assign a connection, or more than one, to users as necessary.
• See http://msdn.microsoft.com/en-us/library/hh169480(v=nav.70).aspx for more information.

All good stuff. One side effect, however, is that is a tricky to see which SQL connections correspond to a NAV session. That’s a problem when you’re trying to resolve blocking issues.

In the days of classic client you could see the active database sessions and blocking information along with the ability to kill sessions if necessary. The Session List page in NAV 2013 shows you active sessions, but without the blocking information. So, how to get at said information?

SQL Server Profiler

To see what is going on in the database you can use SQL Server Profiler. The “SQL Tracing” option in the NAV Session List inserts comments between the entries in the profiler with the NAV code that caused the SQL query to be executed and the user that executed the NAV code.

Profiler has some tools to analyse table locks and deadlocking, but unless you are familiar with capturing and reading traces it can be difficult to track the problem down.

The Copy, Paste and Execute Method

The second method, well known and loved by many of us, is the copy from a blog/forum, paste and execute method.

This SQL query shows locks that have been requested in the current database, the object that was requested (object_name column) and the session id that requested it (request_session_id column).

SELECT tl.resource_type,
tl.resource_associated_entity_id,
OBJECT_NAME(p.object_id) AS object_name,
tl.request_status,
tl.request_mode,
tl.request_session_id,
tl.resource_description
FROM sys.dm_tran_locks tl
LEFT JOIN sys.partitions p
ON p.hobt_id = tl.resource_associated_entity_id
WHERE tl.resource_database_id = DB_ID()
AND p.object_id IS NOT NULL

You can then use Activity Monitor or SQL Server Profile to see what exactly that session is up to and kill the process if necessary.

Credit to http://www.mssqltips.com/sqlservertip/2732/different-techniques-to-identify-blocking-in-sql-server/ for the query and further information about identifying locking in the database.