Maxdop 1 but still utilize all CPU

Question

I have execute one simple select query in our Dev DB server with maxdop 1 but i can see this query utilize all 4 CPU, supposedly it must utilize only 1 CPU but why it utilize all 4 CPU. Server level maxdop is 0.

Scripts as below;

/****** Script for SelectTopNRows command from SSMS ******/
SELECT [Id]
,[ReferenceNo]
,[Date]
,[ServiceId]
,[CustomerId]
,[ReceiverId]
,[ProcessingCode]
,[Status]
,[Amount]
,[IsVoid]
,[Remarks]
,[OtpRefString]
,[InvoiceNo]
,[STANNo]
,[IsSettled]
,[BatchNumber]
,[TerminalId]
,[MerchantId]
,[AuthIdResponse]
,[RetRefNumber]
,[DeviceType]
,[CardId]
,[UtilitiesCode]
,[IsOnlineTransaction]
,[LocationDetails]
,[LatLong]
,[LocationPermission]
,[CouponId]
,[IsPayrollTransaction]
,[ApprovalCode]
FROM [testtable].[dbo].[Transaction]
option (maxdop 1)

Answer 1

in the older versions of SQL Server, a single session would stay on a given processor core. Spid 51 would be on CPU 0, 52 on 1, etc, wrapping around. On more recent versions, a spid/thread could potentially bounces between cores. You can see this in other applications as well. So most likely a single thread was allocated for this query, it just happen to bounce between cores.
Keep in mind the OS quantum is 4 ms. so potentially, a thread/spid could be bouncing every 4ms, and this effect is too brief to see in Task Manager. it appears to be running simultaneously on multiple.
Run with SET STATISTICS TIME ON, if CPU is less that elapsed time, then you were likely using a single thread. The actual execution plan will also show the threads

Answer 2

Your query did not used parallelism that is obvious from the query plan and the "query plan" should be used to check whether parallel plan was used or not. It would not be correct to refer to CPU as CPU is used by complete Windows server, its quite possible that others tasks were running and using CPU and it would be incorrect to say that SQL Server single query ran on all the 4 cores.

Answer 3

Also, just because SQL Server weren't using a CPU, the work SQL Server does might have a side effect on the system. I.e., SQL server tell the OS to do something, and in order for the OS to do this, the OS uses CPU.

Answer 4

Hi @Mario2286 ,

The maximum degree of parallelism (MAXDOP) can be defined through three ways:Instance, Database ,Query . A query hint always overrides the database and instance configuration.

Your query didn't use a parallel execution plan.

Please refer to following articles:

Understanding the new MAXDOP settings in SQL 2016
https://sqltechblog.com/2016/10/04/understanding-the-new-maxdop-settings-in-sql-2016/

how can I tell that a query uses a parallel execution plan
https://www.mssqltips.com/sqlservertip/5404/parallelism-in-sql-server-execution-plan/

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

You didn't capture SQL Server CPU usage, so that may not be directly related to your maxdop 1.

There are many reasons for high CPU. The primary reason is low RAM. Low RAM causes high CPU due to page swapping and disk IO. How much RAM is installed in the server?