1

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

migrated from stackoverflow.com Oct 19 '18 at 19:36

This question came from our site for professional and enthusiast programmers.

  • Maybe you are not able to provide a good test workload? – gotqn Oct 19 '18 at 7:28
  • What exactly is the problem? It doesn't sound like you have any performance issues. Sometimes, queries are more efficient being run on one CPU, so either SQL Server is determining that's the best course of action, or the Max DOP in the query has specifically been set. – Apep Oct 19 '18 at 7:28
  • 1
    stackoverflow.com/questions/24810905/… if you want to attempt to force higher usage in a test. – Dave Brown Oct 19 '18 at 7:34
  • the problem is that it does not seem that there are any bottlenecks in hardware (ram low, cpu low, low disk use). That makes me think the hardware is not fully utilised, there should be at least on bottleneck that prevents higher usage on the other. @DaveBrown I will test using the test queries and see how much cpu is used. – user979899 Oct 19 '18 at 8:25
  • 1
    im afraid the developer edition is somehow locked to use only one core, although it states it should be a full version. – user979899 Oct 19 '18 at 8:27
4

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy