As a parent, I am always curious to know how my child is doing in school. Similarly, as a database administrator (DBA), it is important to understand the behavior and activity of sessions in SQL Server. In this blog post, we will explore the concept of dormant sessions and learn how to monitor and manage them.

When working with SQL Server, DBAs often need to know who is currently accessing the server, which sessions are inactive, and which connections are using the most resources. This information is crucial for maintenance and monitoring purposes. Let’s dive into the different reports that can provide us with these insights:

Activity – All Cursors

This report provides information about the cursors used in SQL Server. Cursors are looping constructs in the T-SQL world. While it is generally recommended to avoid using cursors, there may be situations where they outperform other looping constructs. Cursors offer flexibility by providing a subset of data for manipulation in different ways. The report utilizes the DMV sys.dm_exec_cursors to gather information about open cursors in various databases.

Activity – Top Cursors

This report categorizes cursors based on different criteria:

• Top 10 Oldest Cursors – Shows the oldest cursor in SQL Server (ordered by creation_time).
• Top 10 Dormant Cursors – Displays cursors that have been idle since the last query (open or fetch) (ordered by worker_time).
• Top 10 IO Intensive Cursors – Lists cursors that consume the most IO resources (ordered by reads + writes).
• Top 10 CPU Intensive Cursors – Highlights cursors that consume the most CPU resources (ordered by dormant_duration).

Activity – All Sessions

This report provides details of all active user sessions on the SQL Server instance, organized by login. It includes information about connections, requests, and statements currently active on the server. The report utilizes the DMVs sys.dm_exec_sessions, sys.dm_exec_connections, and sys.dm_exec_requests to gather this information.

Activity – Top Sessions

This report focuses on the top 10 sessions based on different criteria:

• Top Oldest Sessions – Displays the sessions with the earliest login time.
• Top CPU Consuming Sessions – Highlights sessions that consume the most CPU time.
• Top Memory Consuming Sessions – Lists sessions that consume the most memory.
• Top Sessions By # Reads – Shows sessions with the highest number of reads.
• Top Sessions By # Writes – Highlights sessions with the highest number of writes.

Activity – Dormant Sessions

This report provides information about dormant sessions in SQL Server. A dormant session is a session that connected earlier, ran a query, and is now sitting idle. The report identifies sessions that have been inactive for more than an hour. It utilizes the DMV sys.dm_exec_sessions and applies a filter on the last_request_end_time to identify dormant sessions.

Activity – Top Connections

This report focuses on connections rather than sessions. It provides details about the top 10 connections based on different criteria:

• 10 Oldest Connections – Displays the oldest connections.
• Top Ten Connections By # Reads – Lists connections with the highest number of reads.
• Top Ten Connections By # Writes – Highlights connections with the highest number of writes.

These reports are valuable tools for DBAs to monitor and manage SQL Server sessions and connections. By understanding the behavior of sessions and identifying dormant sessions, DBAs can optimize server performance and resource allocation.

I hope you found this blog post informative. Feel free to explore these reports and let me know if you discover anything interesting or find unique ways to utilize them.