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

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

SELECT * FROM Sales.SalesOrderDetail 

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

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

Wait statistics

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

SELECT * FROM Sales.SalesOrderDetail 
ORDER BY UnitPrice 

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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: