As I promised
earlier today, here is the first of many posts about the Dynamic Management Views in SQL Server 2005.
What does the sys.dm_exec_sessions view return? It looks like it is a combination of DBCC USEROPTION and sp_who. Let's for example find out how many sessions are running, sleeping or dormant
Just a quick count of all the sessions
SELECT COUNT(*) as StatusCount,
CASE status
WHEN 'Running'
THEN 'Running - Currently running one or more requests'WHEN 'Sleeping '
THEN 'Sleeping - Currently running no requests'ELSE 'Dormant – Session is in prelogin state' END status
FROM sys.dm_exec_sessionsGROUP BY status
Let's list all SPID'sSELECT session_id,CASE status
WHEN 'Running'
THEN 'Running - Currently running one or more requests'WHEN 'Sleeping '
THEN 'Sleeping - Currently running no requests'ELSE 'Dormant – Session is in prelogin state' END status
FROM sys.dm_exec_sessionsLet's just grab our own SPIDSELECT session_id,
CASE status
WHEN 'Running'
THEN 'Running - Currently running one or more requests'WHEN 'Sleeping '
THEN 'Sleeping - Currently running no requests'ELSE 'Dormant – Session is in prelogin state' END status
FROM sys.dm_exec_sessionsWHERE session_id =
@@SPIDNow let's query all user sessionsSELECT *
FROM sys.dm_exec_sessionsWHERE is_user_process =0
Then for the server you would change 0 to 1SELECT *
FROM sys.dm_exec_sessionsWHERE is_user_process =1
Just a quick count of all the transaction isolation levelsSELECT COUNT(*),CASE transaction_isolation_level
WHEN 0
THEN 'Unspecified'WHEN 1
THEN 'ReadUncomitted'WHEN 2
THEN 'Readcomitted'WHEN 3
THEN 'Repeatable'WHEN 4
THEN 'Serializable'WHEN 5
THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessionsGROUP BY transaction_isolation_level
Let's look at some of these ANSI settings and while we are at it let's use UNPIVOT to return the results verticallySELECT SPID,Value,ANSI_SETTING
FROM (
SELECT @@SPID as SPID,
CASE quoted_identifier
WHEN 1
THEN 'SET' ELSE 'OFF' END QUOTED_IDENTIFIER,
CASE arithabort
WHEN 1
THEN 'SET' ELSE 'OFF' END ARITHABORT,
CASE ansi_null_dflt_on
WHEN 1
THEN 'SET' ELSE 'OFF' END ANSI_NULL_DFLT_ON,
CASE ansi_defaults
WHEN 1
THEN 'SET' ELSE 'OFF' END ANSI_DEFAULTS ,
CASE ansi_warnings
WHEN 1
THEN 'SET' ELSE 'OFF' END ANSI_WARNINGS,
CASE ansi_padding
WHEN 1
THEN 'SET' ELSE 'OFF' END ANSI_PADDING,
CASE ansi_nulls
WHEN 1
THEN 'SET' ELSE 'OFF' END ANSI_NULLS,
CASE concat_null_yields_null
WHEN 1
THEN 'SET' ELSE 'OFF' END CONCAT_NULL_YIELDS_NULL
FROM sys.dm_exec_sessions
WHERE session_id =
@@SPID ) P
UNPIVOT (
Value for ANSI_SETTING IN(
QUOTED_IDENTIFIER,ARITHABORT,ANSI_NULL_DFLT_ON,
ANSI_DEFAULTS,ANSI_WARNINGS,
ANSI_PADDING,ANSI_NULLS,CONCAT_NULL_YIELDS_NULL
)
)
AS unpvt
Now let's see if we can duplicate DBCC USEROPTIONS
First run DBCC USEROPTIONS
And then run the following:
SELECT @@SPID as SPID,
CASE quoted_identifier
WHEN 1
THEN 'SET' ELSE 'OFF' END QUOTED_IDENTIFIER,
CASE arithabort
WHEN 1
THEN 'SET' ELSE 'OFF' END ARITHABORT,
CASE ansi_null_dflt_on
WHEN 1
THEN 'SET' ELSE 'OFF' END ANSI_NULL_DFLT_ON,
CASE ansi_defaults
WHEN 1
THEN 'SET' ELSE 'OFF' END ANSI_DEFAULTS ,
CASE ansi_warnings
WHEN 1
THEN 'SET' ELSE 'OFF' END ANSI_WARNINGS,
CASE ansi_padding
WHEN 1
THEN 'SET' ELSE 'OFF' END ANSI_PADDING,
CASE ansi_nulls
WHEN 1
THEN 'SET' ELSE 'OFF' END ANSI_NULLS,
CASE concat_null_yields_null
WHEN 1
THEN 'SET' ELSE 'OFF' END CONCAT_NULL_YIELDS_NULL,
CASE transaction_isolation_level
WHEN 0
THEN 'Unspecified'WHEN 1
THEN 'ReadUncomitted'WHEN 2
THEN 'Readcomitted'WHEN 3
THEN 'Repeatable'WHEN 4
THEN 'Serializable'WHEN 5
THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL,lock_timeout,date_first,date_format
FROM sys.dm_exec_sessionsWHERE session_id =
@@SPIDIt's probably not exactly the same and I don't know if I missed a column but below is the whole definition of the view and you can experiment with this yourself
Below is what the sys.dm_exec_sessions view returns
On the first line are the column name and the data type, below that is the description
sys.dm_exec_sessions session_id smallint
Identifies the session associated with each active primary connection.
login_time datetime
Time when session was established.
host_name nvarchar(128)
Host associated with the session.
program_name nvarchar(128)
Program associated with the session.
host_process_id int
Process ID associated with the session.
client_version int
Version of the interface used by the client to connect to the Server.
client_interface_name nvarchar(32)
Name of the interface used by the client to connect to the Server.
security_id varbinary(85)
Microsoft Windows security ID associated with the login.
login_name nvarchar(128)
SQL Login Name associated with the session.
nt_domain nvarchar(128)
Domain from which session connection was made.
nt_user_name nvarchar(128)
Name of the user associated with the session.
status nvarchar(30)
Status of the session. Possible values:
-- Running - Currently running one or more requests
-- Sleeping - Currently running no requests
-- Dormant – Session is in prelogin state
context_info varbinary(128)
CONTEXT_INFO value for the session.
cpu_time int
CPU time, in milliseconds, that was used by this session.
memory_usage int
Number of 8-KB pages of memory used by this session.
total_scheduled_time int
Total time, in milliseconds, for which the session (requests within) were scheduled for execution.
total_elapsed_time int
Time, in milliseconds, since the session was established.
endpoint_id int
ID of the Endpoint associated with the session.
last_request_start_time datetime
Time at which the last request on the session began. This includes the currently executing request.
last_request_end_time datetime
Time of the last completion of a request on the session.
reads bigint
Number of reads performed, by requests in this session, during this session.
writes bigint
Number of writes performed, by requests in this session, during this session.
logical_reads bigint
Number of logical reads that have been performed on the session.
is_user_process bit
0 if the session is a system session. Otherwise, it is 1.
text_size int
TEXTSIZE setting for the session.
language nvarchar(128)
LANGUAGE setting for the session.
date_format nvarchar(3)
DATEFORMAT setting for the session.
date_first smallint
DATEFIRST setting for the session.
quoted_identifier bit
QUOTED_IDENTIFIER setting for the session.
arithabort bit
ARITHABORT setting for the session.
ansi_null_dflt_on bit
ANSI_NULL_DFLT_ON setting for the session.
ansi_defaults bit
ANSI_DEFAULTS setting for the session.
ansi_warnings bit
ANSI_WARNINGS setting for the session.
ansi_padding bit
ANSI_PADDING setting for the session.
ansi_nulls bit
ANSI_NULLS setting for the session.
concat_null_yields_null bit
CONCAT_NULL_YIELDS_NULL setting for the session.
transaction_isolation_level smallint
Transaction isolation level of the session.
-- 0 = Unspecified
-- 1 = ReadUncomitted
-- 2 = ReadCommitted
-- 3 = Repeatable
-- 4 = Serializable
-- 5 = Snapshot
lock_timeout int
LOCK_TIMEOUT setting for the session. The value is in milliseconds.
deadlock_priority int
DEADLOCK_PRIORITY setting for the session.
row_count bigint
number of rows returned on the session up to this point.
prev_error int
ID of the last error returned on the session.
So that's it for today, the view contains a lot more than I covered. i might expand this if I have time. If you have any suggestions leave me a comment or drop me an
EmailThe link to the post with all the views is
here (as of today this is the only view that is covered)
Some good news that I am very excited about; Kalen Delaney has finished Inside SQL Server 2005: The Storage Engine. I have already pre-ordered her book but will have to wait until November 8, 2006 when it will ship (hopefully). I have her 2000 edition and it's my favorite book together with Ken Henderson's Guru series. Kalen also has started to blog on SQLblog.com 
