How to Join [Active Session] tables with MSSQL SQL DB session

sarma

Hi All,

Can some one help me to Join Nav's [Active Session] table info with MSSQL DB level session.

Question:- how can i join below 2 queries

Example:-

Nav session:-

SELECT [timestamp]
,[Server Instance ID]
,[Session ID]
,[User SID]
,[Server Instance Name]
,[Server Computer Name]
,[User ID]
,[Client Type]
,[Client Computer Name]
,[Login Datetime]
,[Database Name]
FROM [dbo].[Active Session]

DB session:-

SELECT s.session_id, s.login_time, s.host_name, s.program_name,
s.login_name, s.nt_user_name, s.is_user_process,
database_id, DB_NAME(s.database_id) AS [database] -- return the ID & database name
FROM sys.dm_exec_sessions s
WHERE s.is_user_process = 1

Regards

Slawek_Guzek

Question is if that many sessions is a problem for you.

An user can have a 100 of sessions fired in the background ,and they would not count towards the licensed number of users, taking up only one user license - still potentially being visible as a 100 separate connections on the SQL server.

If you don't run out of licenses and everyone can log it then I would leave it. SQL server can handle lots of concurrent connections with no harm

If you are running out of user licenses then this is a problem.

Sessions with negative IDs are from web services requests. Unfortunately they stay open for a long time. There was a discussion on Mibuso about that issue recently, the conclusion was that this is by design.

You need to assign a special license type for a user making request via a web service to have their connections gone quickly. They are also cleared when a user who made a web service request logs on later to the NAV client.

I don't think there is a good way to approach this problem, especially for existing sessions. You could look at inactivity period, and try to extract some information from there, and/or simply kill sessions which are not active for a long period of time, but users may face errors.

For future you could add one or more NSTs and separate web services loads from normal user load, and spread user load among few NSTs. If Each NST is run under separate AD account it would give you at least some idea who is creating that many sessions, and let you kill at least web services generated sessions easily - since a web service call is a stateless protocol killing the inactive web services originated session would not do any harm.

Slawek

sarma

Hi If some one knows the answer; please post it.This info is bit urgent.

Slawek_Guzek

Hi,

There is no way to do so as far as I am aware. The NST is using connection pooling, so at any given time SPID visible on the server can map to a different user.

The way around it is to create as many NST servers as you have users on NAV, each NST service shoud work under separate service account, and single user should only ust its own NST. So, to sum up, a bit like settng manually Classic Client environment . Then you can have 1:1 mapping between NAV user sessions and SQL sessions (possibly - it still wouldn't tell you which SQL session is NAV background session and which is actual user session)

Sorry

Slawek

sarma

Thanks Slawek,

While troubleshooting one of Live issues, we found 320+ DB sessions(like DB SessionID:-67) from one NAV server.How can we figure it which NAV screen/session generated this many number of DB sessions.

Any help would be appreciated.

Note:-At the time of issue, i have captured the sp_who2 output from MSSQL.

Regards

Slawek_Guzek

Question is if that many sessions is a problem for you.

An user can have a 100 of sessions fired in the background ,and they would not count towards the licensed number of users, taking up only one user license - still potentially being visible as a 100 separate connections on the SQL server.

If you don't run out of licenses and everyone can log it then I would leave it. SQL server can handle lots of concurrent connections with no harm

If you are running out of user licenses then this is a problem.

Sessions with negative IDs are from web services requests. Unfortunately they stay open for a long time. There was a discussion on Mibuso about that issue recently, the conclusion was that this is by design.

You need to assign a special license type for a user making request via a web service to have their connections gone quickly. They are also cleared when a user who made a web service request logs on later to the NAV client.

I don't think there is a good way to approach this problem, especially for existing sessions. You could look at inactivity period, and try to extract some information from there, and/or simply kill sessions which are not active for a long period of time, but users may face errors.

For future you could add one or more NSTs and separate web services loads from normal user load, and spread user load among few NSTs. If Each NST is run under separate AD account it would give you at least some idea who is creating that many sessions, and let you kill at least web services generated sessions easily - since a web service call is a stateless protocol killing the inactive web services originated session would not do any harm.

Slawek

sarma

Thanks for your help Slawek.

sarma

Hi Slawek,

Quick question, is there way can you inject NAV session ID as a comment one(like ---NAV Session:123), So in SQL we can trace which user generated this call.

Regards
Sarma

Slawek_Guzek

No. not really, I don't think so.

The simplest workaround for building mapping between SQL Server SPID and NAV user/session I can think of is to create a table with user id/session id fields as PK, and one dummy field, create a standalone codeunit with 4 functions each being a handler subscribed to one of codeunit's 1 published events: OnAfterOnGlobalInsert / Modify / Delete / Rename events. In each event handler insert or modify a log record using USERID/SESSIONID pair as PK.

NAV 2013+ (the NST) is using MARS connection to the server so hopefully all reads and updates would share a single SQL connection / SPID.

By analyzing updates on this log table you could possibly map SQL SPID to user/session, and later to join that log table to Active Session. Not sure how useful it could be and how often in real situation NST's connection pooling works. I would guess that connections are pretty static, ie a user is mapped to a connection when he/she logs in and then it stays on that connection until he/she logs out. Background sessions might be more dynamic.

All and all this just my guesswork, it may or may not line up with how it really works.

Slawek