-1

I have a process in sql server that seems to never end. To spot if there is a block in this process I used EXEC sp_who2 and I seen that the SPID is 197.

The status is runnable and there is no block. Command is inserting. The weird thing is the CPU time which is the biggest: 68891570 and the DISK IO operations: 16529185.

This process truncates two tables and then insert the data from a another table to these two tables. It is true that there is a lot of information (101962758 rows in the origin table) but I think that there is too much time.

What can I do to accelerate this process or to spot what is happening?

Thank you

mrc
  • 2,845
  • 8
  • 39
  • 73
  • Review execution plans and the code itself. What indexes and so on do you have. – Ivan Starostin Nov 18 '16 at 08:52
  • I don't know, but you can *accelerate the process of getting answers* by showing: the structure of those tables (both source and target) including primary/foreign keys, and the insert query, and all the indexes for those tables, and the execution plan. – Peter B Nov 18 '16 at 09:00

2 Answers2

0

From the info you provided,it seems the query is still running...

This process truncates two tables and then insert the data from a another table to these two tables. It is true that there is a lot of information (101962758 rows in the origin table) but I think that there is too much time.

Can you follow below process (instead of your approach), assuming your tables are Main, T1, T2.

  1. Select * into t1_dup,t2_dup from main

  2. rename t1,t2 to t1dup,t2dup

  3. rename t1_dup,t2_dup to t1,t2

  4. drop t1dup,t2dup

Yuri
  • 1,748
  • 2
  • 23
  • 26
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
0

It depends on the scenario here. I recommend the following steps in order to decide where to move next.

Find most expensive queries

Using the following SQL to determine the most expensive queries:

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
 qs.execution_count,
 qs.total_logical_reads, qs.last_logical_reads,
 qs.total_logical_writes, qs.last_logical_writes,
 qs.total_worker_time,
 qs.last_worker_time,
 qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
 qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
 qs.last_execution_time,
 qp.query_plan
FROM sys.dm_exec_query_stats qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
 CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

Execution plan

This could help to determine what is going on with your actual query. More information could be found here.

Performance tips

  • Indexes. Remove all indexes, except for those needed by the insert (SELECT INTO)
  • Constraints and triggers. Remove them from the table.
  • Choosing good clustered index. New records will be inserted at the end of the table.
  • Fill factor. Set it to 0 or 100 (the same as 0). This will reduce the number of pages that the data is spread across.
  • Recovery model. Change it to Simple.

Also

Consider reviewing Insert into table select * from table vs bulk insert.

Community
  • 1
  • 1
Yuri
  • 1,748
  • 2
  • 23
  • 26
  • thanks for your detailed answer. I would like to ask something, the first query find the top 10 queries that are executing now? For example the second expensive query is one of my procedure but the last_execution_time is 2016-11-18 04:37:26.487. It seems then that is it not running right now... There is no way to know in which step of the procedure is the execution? – mrc Nov 18 '16 at 11:02
  • Nope. It is stats for all queries (expensive) and you could choose the order that you need. In order to find out what exactly is causing issue - go with execution plan. – Yuri Nov 18 '16 at 11:30
  • I have the procedure execution plan. How represents that I know what I am looking for? For example the second more expensive query have 44%cost in the index insert, so maybe the next time I should drop index but right now is it no running. I don't knwo the step that is executing in the procedure. – mrc Nov 18 '16 at 11:38
  • It requires some time. Please, refer to the link in this answer. Could you post an execution plan? – Yuri Nov 18 '16 at 15:11