0

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.

0

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
  • Have you heard of sp_WhoIsActive? – Erik Darling Dec 7 '17 at 18:35
  • Thank for all the answers. I tried lots of queries to figure out what the problem is. All of them have\had runnable status (I checked all of suspicious sessions in sys.dm_exec_sessions but they were running, because there is no runnable status in it, only running). But I experienced that this happens only when I use sqlcmd to run a query. If I use a simple declaration there is no problem. Why is this? Maybe different driver? Thx, – Mona Dec 12 '17 at 10:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.