Why low cpu usage on sql server developer edition 2014

Question

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)?

Update

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).

Answer 1

SQL Server Developer Edition can use all available cores unless you've specifically configured it otherwise. Whether or not it will use all cores depends on the workload.

If you are running a query like SELECT * FROM dbo.YourTable, it will likely be a single-threaded plan and only use one CPU. You'll need to run multiple instances of such a query to see more CPUs used.

A single query that uses parallelism should be able to use multiple cores. The example below uses all 12 cores of my box, although total CPU averages about 40%.

WITH 
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t1k AS (SELECT 0 AS n FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,t1g AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t1k AS a CROSS JOIN t1k AS b CROSS JOIN t1k AS c)
SELECT COUNT(*) AS CountOfRows
FROM t1g;