SQL session that is 'never end', always stay in a running status but does not return a result set (Until I kill it)

Question

I am a beginner SQL DBA and I experienced a weird thing in SQL server.

Symptom:

1. Session is in Running state (not runnable, not suspended).
2. CPU time is zero, or if it is not, after a while it stops growing.
3. Reads\Writes\Logical Reads are zero, or if is not, after a while they stop growing as well.
4. No result set that is returned.
5. They will run until I kill all these sessions. (These can be days, but these queries run within 3 mins usually)

More info:

• This is static database.
• Indexes are not fragmented.
• Statistics are updated
• CPU is not under stress, used 10% only
• Memory is more than enough
• SQL Server 2016 is used

Is there any idea what can be the reason for this behaviour?

I'm very grateful for all the answers.

Answer 1

I would check the following

• is the session you normally kill, waiting on something
• is the session you normally kill, being blocked
• is the session you normally kill, in a transaction that someone forgot to end?

you can use the below query to check the first 2. You may be able to check the third if the query gets picked up in the last column.

SELECT [spid]
--      ,[kpid]
      ,[blocked]
      ,[open_tran]
--      ,[waittype]
      ,[waittime]
      ,[lastwaittype]
      ,[waitresource]
      ,[name]
      ,CAST([cpu] AS NUMERIC) cpu
      ,[physical_io]
      ,[loginame]
      ,[hostname]
      ,[program_name]
--      ,[hostprocess]
      ,[cmd]
--      ,[nt_domain]
--      ,[nt_username]
--      ,[net_address]
      ,[net_library]
--      ,[uid]
      ,[memusage]
      ,[login_time]
      ,[last_batch]
--      ,[ecid]
      ,[status]
--      ,[sid]
--      ,[context_info]
      ,[sql_handle]
--      ,[stmt_start]
--      ,[stmt_end]
--      ,[request_id]
        ,[text]
FROM [master].[sys].[sysprocesses] p
JOIN SYS.databases d
    ON p.dbid = d.database_id
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
--WHERE status = 'runnable' 
--WHERE name = 'runnable' 
--ORDER BY blocked DESC
--ORDER BY cpu DESC
ORDER BY waittime DESC