How do you overcome CPU bottlenecks – under utilization / over utilization?

Lets take a sample query (CTOP 10, MAXDOP 0)

SELECT * FROM Sales.SalesOrderDetail 
ORDER BY UnitPrice DESC

This query ran in parallel on my test machine (16 CPUS / 128GB).

Run the query 5 times, it takes about 15 seconds to complete.

Now i am going to run with OPTION (MAXDOP 8)

SELECT * FROM Sales.SalesOrderDetail 
ORDER BY UnitPrice 
OPTION (MAXDOP 8) DESC

It took about 15 seconds to complete (same as above).

There is usually a common misconception that when you give more CPU, a query runs faster. That is not actually true in most cases.

Please note that when your query uses 16 CPUs , it is actually doing a lot more work than you think. The work is being divided across 16 worker threads, and could end up being more work coordinating and merging results across more CPUs.

Also, please note that you will see thread 0, this does not process the query. Thread 0 is called a coordinator thread.

If you have an OLTP workload, would recommend to use MAXDOP as 1 or 2. Anything higher would slow down the queries in general. If your environment uses a warehouse environment, a higher MAXDOP is recommended.