SQL Profiler with NAV 2013 R2

bbrown
bbrown Member Posts: 3,268
edited 2014-09-22 in SQL Performance
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?
There are no bugs - only undocumented features.

Comments

  • ara3n
    ara3n Member Posts: 9,258
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • JasminkaT
    JasminkaT Member, Microsoft Employee Posts: 34
    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
    RSA_Tech Member Posts: 65
    Also if you just need to see who is blocking what try sp_who3

    http://www.sqlservercentral.com/scripts/sp_who3/69906/
  • bbrown
    bbrown Member Posts: 3,268
    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.

    There are no bugs - only undocumented features.
  • RemkoD
    RemkoD Member Posts: 100
    edited 2018-02-27
    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.

Categories