Exploring query execution DMVs
Several different DMVs may be relevant when analyzing the activity that is currently happening in a SQL Database Engine. In this section, we will cover a few of the most common DMVs, along with some examples of the information that they can provide.
sys.dm_exec_sessions
The sys.dm_exec_sessions DMV lists information about all the sessions that are currently active on the server. This includes both user sessions and system sessions, and it also includes idle sessions that are connected but are not currently executing any queries.
Tip
Idle sessions can be identified by looking for rows that have a status of sleeping. When using connection pooling especially, it is common to have several user sessions in a sleeping status.
This DMV can be used to view information that is relevant to the session, such as login_name, host_name, program_name, and other properties that would be set at the session level. This can be helpful when trying to identify...
