DISCOVER_SESSIONS Rowset

DISCOVER_SESSIONS Rowset

 

Applies To: SQL Server 2016 Preview

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

The DISCOVER_SESSIONS rowset contains the following columns.

Column nameType indicatorLengthDescription
SESSION_COMMAND_COUNTDBTYPE_I4The number of commands that started execution since the beginning of the session.
SESSION_CONNECTION_IDDBTYPE_I4The connection identifier for the session.
SESSION_CPU_TIME_MSDBTYPE_UI8The CPU time, in milliseconds, consumed by all requests since the beginning of the session.
SESSION_CURRENT_DATABASEDBTYPE_WSTRThe 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_MSDBTYPE_UI8Elapsed time, in milliseconds, since the start of the session.
SESSION_IDDBTYPE_WSTRThe session unique identifier, as a GUID.
SESSION_IDLE_TIME_MSDBTYPE_UI8The idle time, in milliseconds, since the start of the session.
SESSION_LAST_COMMANDDBTYPE_WSTRThe text of the current command executing or the last command executed.
SESSION_LAST_COMMAND_CPU_TIME_MSDBTYPE_UI8The CPU time, in milliseconds, consumed by SESSION_LAST_COMMAND.
SESSION_LAST_COMMAND_ELAPSED_TIME_MSDBTYPE_UI8The elapsed time, in milliseconds, since the start of SESSION_LAST_COMMAND.
SESSION_LAST_COMMAND_END_TIMEDBTYPE_DBTIMESTAMPThe UTC server time at the moment the last command finished executing.
SESSION_LAST_COMMAND_START_TIMEDBTYPE_DBTIMESTAMPThe UTC server time at the moment the last command started executing.
SESSION_PROPERTIESDBTYPE_WSTRReserved for future use.
SESSION_READ_KBDBTYPE_UI8The accumulated value of data read from disk, in kilobytes, since the start of the session.
SESSION_READSDBTYPE_UI8The accumulated number of disk reads since the start of the session.
SESSION_SPIDDBTYPE_I4The session ID.
SESSION_START_TIMEDBTYPE_DBTIMESTAMPThe date and time the session started as UTC time to the server.
SESSION_STATUSDBTYPE_I4The 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_MEMORYDBTYPE_I4The 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_NAMEDBTYPE_WSTRThe session user name.
SESSION_WRITE_KBDBTYPE_UI8The accumulated value of data written to disk, in kilobytes, since the start of the session.
SESSION_WRITESDBTYPE_UI8The accumulated number of disk writes since the start of the session.

This schema rowset is not sorted.

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

Column nameType indicatorRestriction State
SESSION_IDDBTYPE_WSTROptional.
SESSION_SPIDDBTYPE_I4Optional.
SESSION_CONNECTION_IDDBTYPE_I4Optional.
SESSION_USER_NAMEDBTYPE_WSTROptional.
SESSION_CURRENT_DATABASEDBTYPE_WSTROptional.
SESSION_ELAPSED_TIME_MSDBTYPE_UI8Optional.
SESSION_CPU_TIME_MSDBTYPE_UI8Optional.
SESSION_IDLE_TIME_MSDBTYPE_UI8Optional.
SESSION_STATUSDBTYPE_I4Optional.

XML for Analysis Schema Rowsets

Community Additions

ADD
Show:
© 2016 Microsoft