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:
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:
TSQL Default TCP
2007 Microsoft Office system
Microsoft SQL Server Management Studio
TSQL Local Machine
Microsoft SQL Server Management Studio - Query
SQLAGENT90 - Id<2532>
SQLAgent - Generic Refresher
SQLAgent - Alert Engine
SQLAgent - Job invocation engine
(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
If I am correct then WHERE session_id >= 51 can be replaced with
WHERE is_user_process =1
Hi people!!! I want introduce my new year[url=http://www.xrum.977mb.com]new year foto. [/url]