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

by Mona   Last Updated December 07, 2017 11:06 AM

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.



Related Questions





Patching SSRS on Always on Availability Groups

Updated September 22, 2017 21:06 PM