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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy