The CPU usage is low, around 15 %. CPU is 4770K. The SQL server data file is split in 8 parts and are stored on EVO 970 SSD, along with tempdb (also split in 8 parts). The temdlog and database log are on another SSD (read/write potential around 500MB/s). I don't see any high disk usage in task manager below (10 MB/s), and RAM usage is not high either (have 32 GB). Max parallelization is set to 8.
It seems SQL server is mostly using 1 core, but I can't figure out why. Does not seem to be bound by disk nor ram, so how can I get it to work a bit harder (use more CPU)?
Tested Dan Guzman's query and confirmed the cpu is more utilised although it is hard to have time to see in task manager (query time 2 seconds). Good to know the developer version isn't locked to one core.
Tested the queries from Dan Brown's link. The parallelized version got my cpu usage over 95 % so it seems case is closed; the low cpu usage is because of the query.
I've a lot of correlated subqueries (select in select), which does not look good for performance. For maintenance it's been easiest to calculate several statistics in one query, but it seems only one core is used in that way. I tested splitting them so that I only calculate one metric per query, and that way sql server starts using more cores.
In the future I think I will test the performance of Spark for the same queries and see how it compares to sql server. Perhaps also by running the queries against sql server through spark on several workers (in that case I need to split the work by partitioning according to e.g. modulo of pk).
For the interested: what I need to calculate are various metrics for races, so that for each race and each competitor I fetch it's top record e.g. during last 3 months, last 6 months etc. or average time last 6 months. When the query time is around 10 hours it starts affecting the analytics work I want to do (one iteration to change or correct something takes too long).