INSERT SELECT uses only 1 CPU core with TABLOCK option - how to optimize?

• svenflock

  SSC-Addicted

  Points: 459

  More actions

  June 5, 2019 at 9:05 am

  #3650315

  Hi all,

  I am currently into ETL scripts performance tuning. What I do is, that I try to optimize logic of queries to get as high CPU utilization as possible. It's a 16 core machine with SQL Server 2016 and I want to avoid having a query run only on a single core.

  As I am new to the optimization theme, I still have to learn.

  I am using the activity monitor to track CPU and I/O performances.

  A lot of queries just do simple transformations from input to output, .e.g.:

  INSERT INTO <targetTable> WITH (TABLOCK)
  SELECT IIF(<Logic 1>) AS <targetColumn1>, IIF(<Logic 2>) AS <targetColumn1>
  FROM <sourceTable>
  WHERE <filterColumn> = "<filterCritera>"
  

  As no other service does any transaction to the target table, I am using TABLOCK option to speed things up (arround 20% better INSERT times).

  However, when running such queries only one CPU core is used. I have tried it with all 3 types of log levels of the database, but nothing changed (I have read that from SQLSVR2008+ the engine uses minimal logging automatically, doesn't it?)

  How can I optimize INSERT SELECT queries?

   

  Thanks!

• svenflock

  SSC-Addicted

  Points: 459

  More actions

  June 5, 2019 at 9:31 am

  #3650319

  Just forgot to mention that this is mostly the case when using window functions like row_number() or DISTINCT SELECTs.