SQL Profiler with NAV 2013 R2

bbrown

I find myself using SQL Profiler more often these days for a few reasons. Mainly looking into performance related issues. With prior versions, it was easy to separate the activity of different users as they each showed up as their own login. Being able to know who a SQL statement came from, let me hopefully sort of what they were doing and what area of the program needed to be looked at.

With NAV 2013 R2, the sessions all come from the same user. Mainly the login account for the NST. My question is there still a way to relate SQL Profiler activity to a specific user in NAV? Have I just been missing it?

ara3n

There is no way from sql server to see the actual user that is performing the process. It's very annoying. Especially when you are trying to find out who is locking the system.

JasminkaT

This is the equivalent of the old session monitor, and might help in this kind of scenarios:

http://navappprofiler.codeplex.com/

Downside is that it can't hold too much data in the memory, so large processes can't be monitored over longer time.
Still it could help you identify the user if you map the shorter sample from this profiler against sql trace.
Or enable full sql tracing in the profiler, that will add callstack and user id.

RSA_Tech

Also if you just need to see who is blocking what try sp_who3

http://www.sqlservercentral.com/scripts/sp_who3/69906/

bbrown

RSA_Tech wrote: »

Also if you just need to see who is blocking what try sp_who3

http://www.sqlservercentral.com/scripts/sp_who3/69906/

The "who" in this case is just going to be the NST service account. It would basically show you the service account is locking itself. It's not going to relate that information back to specific NAV users.

RemkoD

Tracing one specific user with SQL Profiler can be done by letting the user sign in through a dedicated NST where no other users are connected with at that moment. This NST has to be started with a dedicated service account OR the NST has to be installed on a machine where no other NST's connect to the same database. In SQL Profiler you can then filter on DB name and User or on DB name and Host Machine.

If you create a dedicated NST for trace purposes. You might as well disable the property Smart SQL on that Service Tier. That will make the traced queries easier to interpreted.