DISCOVER_SESSIONS Rowset

Provides resource usage and activity information about the currently opened sessions on the server.

Rowset Columns

The DISCOVER_SESSIONS rowset contains the following columns.

Column name

Type indicator

Length

Description

SESSION_COMMAND_COUNT

DBTYPE_I4

 

The number of commands that started execution since the beginning of the session.

SESSION_CONNECTION_ID

DBTYPE_I4

 

The connection identifier for the session.

SESSION_CPU_TIME_MS

DBTYPE_UI8

 

The CPU time, in milliseconds, consumed by all requests since the beginning of the session.

SESSION_CURRENT_DATABASE

DBTYPE_WSTR

 

The name of the database that is being used by the current command execution, or the database that was used by the last command executed.

SESSION_ELAPSED_TIME_MS

DBTYPE_UI8

 

Elapsed time, in milliseconds, since the start of the session.

SESSION_ID

DBTYPE_WSTR

 

The session unique identifier, as a GUID.

SESSION_IDLE_TIME_MS

DBTYPE_UI8

 

The idle time, in milliseconds, since the start of the session.

SESSION_LAST_COMMAND

DBTYPE_WSTR

 

The text of the current command executing or the last command executed.

SESSION_LAST_COMMAND_CPU_TIME_MS

DBTYPE_UI8

 

The CPU time, in milliseconds, consumed by SESSION_LAST_COMMAND.

SESSION_LAST_COMMAND_ELAPSED_TIME_MS

DBTYPE_UI8

 

The elapsed time, in milliseconds, since the start of SESSION_LAST_COMMAND.

SESSION_LAST_COMMAND_END_TIME

DBTYPE_DBTIMESTAMP

 

The UTC server time at the moment the last command finished executing.

SESSION_LAST_COMMAND_START_TIME

DBTYPE_DBTIMESTAMP

 

The UTC server time at the moment the last command started executing.

SESSION_PROPERTIES

DBTYPE_WSTR

 

Reserved for future use.

SESSION_READ_KB

DBTYPE_UI8

 

The accumulated value of data read from disk, in kilobytes, since the start of the session.

SESSION_READS

DBTYPE_UI8

 

The accumulated number of disk reads since the start of the session.

SESSION_SPID

DBTYPE_I4

 

The session ID.

SESSION_START_TIME

DBTYPE_DBTIMESTAMP

 

The date and time the session started as UTC time to the server.

SESSION_STATUS

DBTYPE_I4

 

The activity status of the session.

0 means "Idle": No current activity is ongoing.

1 means "Active": The session is executing some requested task.

2 means is "Blocked": The session is waiting for some resource to continue executing the suspended task.

3 means "Cancelled": The session has been tagged as cancelled.

SESSION_USED_MEMORY

DBTYPE_I4

 

The current size of memory used by the session in kilobytes. The value reported is RAM usage by SPID, with no distinction between shrinkable and non-shrinkable memory. Unlike other DMVS that report on memory usage, DISCOVER_SESSIONS does not break out memory usage by category.

Note that SESSION_USED_MEMORY tends to under-report actual memory usage because it excludes objects shared across multiple sessions. Only those objects that are unique to the session are represented in the memory calculation.

SESSION_USER_NAME

DBTYPE_WSTR

 

The session user name.

SESSION_WRITE_KB

DBTYPE_UI8

 

The accumulated value of data written to disk, in kilobytes, since the start of the session.

SESSION_WRITES

DBTYPE_UI8

 

The accumulated number of disk writes since the start of the session.

This schema rowset is not sorted.

Restriction Columns

The DISCOVER_SESSIONS rowset can be restricted on the columns listed in the following table.

Column name

Type indicator

Restriction State

SESSION_ID

DBTYPE_WSTR

Optional.

SESSION_SPID

DBTYPE_I4

Optional.

SESSION_CONNECTION_ID

DBTYPE_I4

Optional.

SESSION_USER_NAME

DBTYPE_WSTR

Optional.

SESSION_CURRENT_DATABASE

DBTYPE_WSTR

Optional.

SESSION_ELAPSED_TIME_MS

DBTYPE_UI8

Optional.

SESSION_CPU_TIME_MS

DBTYPE_UI8

Optional.

SESSION_IDLE_TIME_MS

DBTYPE_UI8

Optional.

SESSION_STATUS

DBTYPE_I4

Optional.

See Also

Reference

XML for Analysis Schema Rowsets