I am a beginner SQL DBA and I experienced a weird thing in SQL server.
Symptom:
- Session is in
Running
state (not runnable, not suspended). - CPU time is zero, or if it is not, after a while it stops growing.
- Reads\Writes\Logical Reads are zero, or if is not, after a while they stop growing as well.
- No result set that is returned.
- 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.
Best Answer
I would check the following
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.