Blogs

SQL Server 2005 Metadata: Who is connected and how?

  • Comments 2
  • Likes

I find metadata fascinating.  In a previous life, I conducted conference sessions on Oracle Server metadata.  So, the new metadata views and tables in SQL Server 2005 are pretty interesting to me.

Apparently, I’m not alone.  I note that at least one blogger is investigating the topic.  (See http://sqlservercode.blogspot.com/2006/08/sysdmexecsessions.html.)  SQLTeam.com is also publishing on the topic.  (See http://www.sqlteam.com/item.asp?ItemID=23040.)  We can’t forget that MSDN provides documentation on the system views (http://msdn2.microsoft.com/en-us/library/ms188754.aspx) and system tables (http://msdn2.microsoft.com/en-us/library/ms179932.aspx).

I like the approach that SQLServerCode blogger is taking in the sense that he is focusing on how a given system table or view is used rather than (for example) simply documenting each view.

I think much of the power of the metadata comes when we use the power of the database join—combining information from two or more metadata objects.

So, in that spirit, I would like to add a query of my own dealing with session information.  I would like to know not just who is on, but I would like to know how they connected.  Combining information from the sessions view (dm_exec_sessions) with information from the endpoints view (sys.endpoints), I can determine the protocols by which my server customers are connecting:

select endpoints.name

, endpoints.protocol_desc

, sessions.host_name

, sessions.program_name

, sessions.login_name

, sessions.status

from sys.dm_exec_sessions sessions

       left outer join sys.endpoints endpoints

              on sessions.endpoint_id = endpoints.endpoint_id

where sessions.host_name is not null

order by endpoints.name

This query returns something like the following:

name

protocol_desc

host_name

program_name

login_name

status

TSQL Default TCP

TCP

ACME-W2K3S1

2007 Microsoft Office system

ACMEDOMAIN\mracme

sleeping

TSQL Default TCP

TCP

ACME-W2K3S

Microsoft SQL Server Management Studio

ACMEDOMAIN\mracme

sleeping

TSQL Local Machine

SHARED_MEMORY

ACME-XP

Microsoft SQL Server Management Studio - Query

ACMEDOMAIN\mracme

running

TSQL Local Machine

SHARED_MEMORY

ACME-XP

Report Server

NT AUTHORITY\SYSTEM

sleeping

TSQL Local Machine

SHARED_MEMORY

ACME-XP

Microsoft SQL Server Management Studio

ACMEDOMAIN\mracme

sleeping

TSQL Local Machine

SHARED_MEMORY

ACME-XP

SQLAGENT90 - Id<2532>

NT AUTHORITY\SYSTEM

sleeping

TSQL Local Machine

SHARED_MEMORY

ACME-XP

SQLAgent - Generic Refresher

NT AUTHORITY\SYSTEM

sleeping

TSQL Local Machine

SHARED_MEMORY

ACME-XP

Repl-LogReader-0-AdventureWorks-10

NT AUTHORITY\SYSTEM

sleeping

TSQL Local Machine

SHARED_MEMORY

ACME-XP

Repl-LogReader-0-AdventureWorks-10

NT AUTHORITY\SYSTEM

sleeping

TSQL Local Machine

SHARED_MEMORY

ACME-XP

SQLAgent - Alert Engine

NT AUTHORITY\SYSTEM

sleeping

TSQL Local Machine

SHARED_MEMORY

ACME-XP

Repl-LogReader-0-AdventureWorks-10

NT AUTHORITY\SYSTEM

sleeping

TSQL Local Machine

SHARED_MEMORY

ACME-XP

SQLAgent - Job invocation engine

NT AUTHORITY\SYSTEM

sleeping

(Note, by the way, how Access in the Office 2007 suite appears in “program_name”.)

I did the outer join because dm_exec_session can contain an endpoint_id that is not a valid foreign key to sys.endpoints.  However, this is not necessary when I apply the “sessions.host_name is not null” criteria.  It is interesting to note that SQLTeam limits its queries to session_id >= 51; however, on initial search, I can find no documentation indicating that this is a valid assumption.

Documentation for dm_exec_sessions may be found at http://msdn2.microsoft.com/en-us/library/ms176013.aspx.  Documentation for sys.endpoints may be found at http://msdn2.microsoft.com/en-us/library/ms189746.aspx

Comments
  • If I am correct then WHERE session_id >= 51 can be replaced with

    WHERE is_user_process =1

    Denis

  • Hi people!!! I want introduce my new year[url=http://www.xrum.977mb.com]new year foto. [/url]

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment