0

Environment is SQL Server 2014, 64gb RAM, 6 processors. 2TB disk, with almost 400gb free space.

I have a procedure that is called by job. It creates temp table then joins several dimension tables to that table and inserts into fact table. It worked cleanly until monday running between 2 and 10 minutes. On monday it lasted nearly 5 hours without doing anything. idles process was at 98%, no reads no writes, state is suspended. There are no locks, no blocking sessions, literally nothing that I can pin down as culprit.

As soon as it's called it immediately goes to suspended state and I cannot find out why. It's supposed to be waiting for something, but I can't find what it's waiting for. It's blocking entire process and no data is being loaded.

I would really appreciate help.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Obi Mark
  • 1
  • 1
  • 1

3 Answers3

1

A process goes into suspended mode because its waiting for a system resource become available. What specifically that resource is in your case, I'm not sure. If you re-run it and it continues to happen, I'd run a profiler trace on the procedure and see what it's doing at the moment it becomes suspended.

Xedni
  • 3,662
  • 2
  • 16
  • 27
1

@XObi Mark, In short, you'll need to look at the wait types and the query plan. Here's a query to capture the details of the query plan:

SELECT dm_ws.wait_duration_ms,
dm_ws.wait_type,
dm_es.status,
dm_t.TEXT,
dm_qp.query_plan,
dm_ws.session_ID,
dm_es.cpu_time,
dm_es.memory_usage,
dm_es.logical_reads,
dm_es.total_elapsed_time,
dm_es.program_name,
DB_NAME(dm_r.database_id) DatabaseName,
-- Optional columns
dm_ws.blocking_session_id,
dm_r.wait_resource,
dm_es.login_name,
dm_r.command,
dm_r.last_wait_type
 FROM sys.dm_os_waiting_tasks dm_ws
  INNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_id
  INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_id
  CROSS APPLY sys.dm_exec_sql_text (dm_r.sql_handle) dm_t
  CROSS APPLY sys.dm_exec_query_plan (dm_r.plan_handle) dm_qp
   WHERE dm_es.is_user_process = 1

To analyze wait types, follow the advice on this link from Marcello Miorelli and steoleary.

How to find out why the status of a spid is suspended? What resources the spid is waiting for?

plditallo
  • 701
  • 2
  • 13
  • 31
0

Tnx all for your input, I tried the above query and checked the helpful link you provided. It did give me a lot of info. I finally traced the cause. It appears that procedure in question had a very inefficient way of getting dates from date table. It used SELECT max(date) FROM d_date WHERE date_id = @myDateFrom In the table date_id is integer, while date is, well, date. This max was paralyzing the query. I realize that normally it's used to insure only a single row is returned from table, but in this case even without max only one row should be retrieved from d_date table. Removing the max from query returned execution time to roughly previous values.

Thank you all for your effort.

Obi Mark

Obi Mark
  • 1
  • 1
  • 1