I was recently tasked with determining why a NAV 2018 installation was occasionally performing poorly. I suspected that one or more users were running particular queries or functions but I needed to find a way to prove that. The catch was I am not a SQL DBA and I know even less about NAV. But that’s how it is sometimes. It falls on you when no one else can figure it out.
I did a lot of Googling but the consensus seems to be that it’s not possible to find the specific NAV user running any given SQL query because from the perspective of SQL every query is run as the NAV service account. I found posts that explain how to enable SQL/NAV debugging to try and capture the user name in real time but this puts a lot of extra load on an already poorly performing system and we didn’t know exactly when the issue would occur.
I decided to build a NAV 2018 lab environment with multiple users to see if I can find a way to determine which user was running which queries. I came up with something that seems to work and wanted to share it in case it benefits others.
In the screenshot below, I have 2 users called ADMIN-RV and JSMITH. I used the NAV Client to perform various NAV functions with each user. The report below shows how long each query took to execute, the full SQL query (not limited to just 4,000 characters) and most importantly of all, the actual NAV user account that executed the query. The rows that do not include a username are internal system queries and are not associated with any end user. The report below shows all queries for testing but in production we would limit it to queries that ran for longer durations.