In SQL Server, a sleeping process refers to a session that is currently idle and not executing any commands. This typically means that the session has completed its execution and is waiting for the application to submit another query or batch. In the output of the Dynamic Management Views (DMVs) such as sys.dm_exec_requests and sys.dm_exec_sessions, a sleeping status indicates that the Server Process ID (SPID) is awaiting a command from the application.

Sleeping processes in SQL Server can have several impacts on performance, depending on various factors such as the number of sleeping sessions, the resources they consume, and whether they hold locks.

• Resource Consumption: Generally, sleeping connections consume minimal resources. However, if there are a large number of sleeping sessions, they can collectively use significant memory and CPU resources, which might affect overall server performance
• Holding Locks: One of the unintended side effects of sleeping sessions is that they can hold locks within the SQL Server engine. This can lead to blocking issues, causing delays in processing user requests. This behaviour is often related to application design flaws where transactions are left open on sessions that have been reset for connection pooling
• System Performance: Sleeping sessions indicate that the interaction between the client and SQL Server is over, and SQL Server is waiting for the next command. While this is generally efficient, it can become problematic if the sessions hold open transactions or if there are too many idle connections

To identify sleeping processes in SQL Server, you can use the Dynamic Management Views (DMVs) such as sys.dm_exec_sessions and sys.dm_exec_requests. Here is a query that can help you find sleeping sessions:

SELECT *
FROM sys.dm_exec_sessions
WHERE status = N'sleeping'
AND open_transaction_count = 0
AND is_user_process = 1;

----------------------------------------

SELECT se.session_id,
       se.database_id,
       se.status,
       se.open_transaction_count,
       qry.text
FROM sys.dm_exec_sessions AS se
     LEFT JOIN sys.dm_exec_connections AS con ON con.session_id = se.session_id
     OUTER APPLY sys.dm_exec_sql_text(con.most_recent_sql_handle) AS qry
WHERE open_transaction_count > 0
      AND status = 'sleeping'
      AND is_user_process = 1;

----------------------------------------

DECLARE c_Users CURSOR FAST_FORWARD
FOR SELECT SPID
    FROM master..sysprocesses(NOLOCK)
    WHERE spid > 50
          AND status = 'sleeping'
          AND hostname LIKE 'D09808%' --Clive Roberts
          AND DATEDIFF(mi, last_batch, GETDATE()) >= 120
          AND spid <> @@spid;
OPEN c_Users;
FETCH NEXT FROM c_Users INTO @v_spid;
WHILE(@@FETCH_STATUS = 0)
    BEGIN
        PRINT 'KILLing '+CONVERT(VARCHAR, @v_spid)+'...';
        EXEC ('KILL '+@v_spid);
        FETCH NEXT FROM c_Users INTO @v_spid;
    END;
CLOSE c_Users;
DEALLOCATE c_Users;

This query will return all user sessions that are currently sleeping and do not have any open transactions

Also, you can examine the output of the DMVs sys.dm_exec_requests and sys.dm_exec_sessions for information on the SPIDs at the head of the blocking chain. Look for the following columns:

• sys.dm_exec_requests.status: This column shows the status of a particular request. Typically, a sleeping status indicates that the SPID has completed execution and is waiting for the application to submit another query or batch

To monitor sleeping processes in SQL Server, you can use several tools and methods:

• SQL Server Management Studio (SSMS): You can use the Activity Monitor in SSMS to view the status of processes, including sleeping processes. The Blocked By column in Activity Monitor can help you identify any blocking issues
• Standard Reports in SSMS: SSMS provides standard reports such as “Activity – All Blocking Transactions,” which can show current transactions at the head of a blocking chain
• Dynamic Management Views (DMVs): You can use DMVs like sys.dm_exec_sessions and sys.dm_exec_requests to query and monitor sleeping processes
• SQL Sentry: This tool can be used to set up alerts and monitor SQL Server performance, including sleeping processes
• Performance Monitoring and Tuning Tools: SQL Server comes with a good set of tools for monitoring events and tuning the physical database design. The choice of tool depends on the type of monitoring or tuning to be done and the particular events to be monitored
• Database Administrators Stack Exchange: There are various procedures and queries shared by the community to get information about sleeping processes that are causing blocking

From my SQL Server DBA handbook.