How to Identify Session Used by SQL Server Management Studio? – Interview Question of the Week #151

Question: How to Identify Session Used by SQL Server Management Studio?

Answer: This is indeed a very interesting question for sure. I have been working as a consultant for a while and I have yet not heard this question in any of the interviews I have participated. The best part was that this question was actually not asked by interviewer by an interview candidate. At the end of the interview when we asked if he has any question or not, he said he would love to know the answer of this question if we know.

How to Identify Session Used by SQL Server Management Studio? - Interview Question of the Week #151 sessionid-800x160

Well, let us see the answer in the simple most possible way.

To identify which sessions are consumed by SQL Server Management Studio, we can take help of sp_who2.

First run following query in SQL Server Management Studio

EXEC sp_who2

Once the result is displayed it, scroll down the resultset beyond SPID 50. Here you can check the column ProgramName. The column ProgramName will give display the name of the program which is consuming any particular SPID.

How to Identify Session Used by SQL Server Management Studio? - Interview Question of the Week #151 ssms-id

Here you will see either Microsoft SQL Server Management Studio or Microsoft SQL Server Management Studio – Query. Any Session ID used by SSMS will be prefixed by SQL Server Management Studio. Let us me know if there are any other way to answer this question. I will be interested to know your solution to identify session as well.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
When to Use IDENT_CURRENT? – Interview Question of the Week #150
Next Post
How to Kill Processes Idle for X Hours? – Interview Question of the Week #152

Related Posts

7 Comments. Leave new

  • Current tab SPID is visible (or can be toggled – as visible – in Settings) directly in SSMS status bar.

    Should be that “your login name (spid)”

    Reply
  • This is also be identified via activity monitor , you would be able to see all the sessions , under application name you would see Microsoft SQL Server Management Studio.

    Reply
  • sys.dm_exec_sessions was the first answer that came into my mind.
    In this case program_name column has a value of ‘Microsoft SQL Server Management Studio’ or ‘Microsoft SQL Server Management Studio – Query’

    Reply
  • The session id is included in the status bar at the bottom of SSMS itself :-)

    Reply
    • Not really…

      At bottom of SSMS you will only see the ID of Query Window which is open not for all the session id which SSMS consuming.

      Reply
      • Yeah, there is at least one additional connection – for Object browser. But Usually this is best correct answer.

        sp_who2 – nice, keep scrolling in 6000+ rows on production cluster :)

  • we can also try this to sys.dm_exec_sessions

    Reply

Leave a Reply

Menu