Perspectives on Microsoft, SQL, Business Intelligence, and other things

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:

This query returns something like the following:

(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