June 5, 2019 at 9:05 am
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!
June 5, 2019 at 9:31 am
Just forgot to mention that this is mostly the case when using window functions like row_number() or DISTINCT SELECTs.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply