Issue

Users may notice a large number of "sleeping" sessions in SQL and wonder if these are caused by the Stewardship Tier or if they are related to user-related operations. They may be concerned that the idle jobs in the system are consuming resources and memory without a clear purpose.

Resolution/Explanation

Sleeping sessions can be user related or can be related to an operation or process. A sleeping session indicates that the interaction between the client (be that the Stewardship Tier or a user holding a session open) and the SQL Service is over, and SQL is waiting for the next command to be executed, instead of closing the connection and opening a new one.

The most common example of holding a session open is when a user navigates and loads a page. The session remains sleeping until the user clicks another page link or a record (a Vertical of a record, for example), after which the session is resumed with a new query.

Sleeping sessions also include background users such as "process,” which is not a user. So even if there are no actual users navigating, there can still be sleeping sessions.

Of course, the busier the server is, the larger number of sleeping sessions exist.

The upside of sleeping sessions is that users and Stewardship Tier background processes don't have to establish a new session with the database server every time a query has to be executed, either by navigating or by any other actions such as request submissions. If that was the case, the performance impact would be expected to be higher, particularly on network interfaces.

Sleeping sessions do not impact performance as CPU and memory are not affected. These sessions  do affect the current size of the TempDB. As more sessions are kept open, more TempDB space is used.

Users can close the sleeping sessions, both in Stewardship Tier and on SQL Server, when they're not using either of them. Users should log out of the Stewardship Tier  when not using it, and they should close query windows when they're done launching queries (although the number of people with direct access to open query windows should be limited).

Those two measures won't avoid sleeping sessions creation completely though, since service threads and background Stewardship Tier operations will still generate them. However, closing user sessions and query windows can reduce the number of sleeping sessions by around 20%.