I understand that a parallel plan will use (at most) as many cores as set up in MAXDOP configuration (per parallel zone).
I have configured my MAXDOP to 4 and my “cost threshold for parallelism” to 55. So, my question is…will all the serial plans with a cost less than 55 use only the first core?
If that’s the case…if I have mostly low-cost queries, my 1st core would be exhausted while the rest of the cores would be idle.
Best Answer
Your query execution plan can, depending on the complexity, contain both serial and parallel components.
In SQL Server speak your complex or simple query/batch is called a request and as such is monitored via the sys.dm_exec_requests management view.
The request is then split into different workloads which in SQL Server is represented as a task. A request can be a number of sequential or parallel tasks which all belong to the same request. Task are monitored in the sys.dm_os_tasks management view.
The tasks in turn are handed over to worker threads which are logical representations of Operating System threads. A sqlsrvr.exe process will have a number of threads which can perform computation on the processor (core).
When the thread is waiting for example on data being returned, then it would be better if the thread could be freed to perform another task while the current task is waiting. This is performed by the scheduler which is ...
The threads are not bound to a specific core/processor unless process affinity has been turned on a the OS level.
(emphasis mine)
Reference: Thread and Task Architecture Guide (Microsoft | SQL Docs)
Answering Your Question
No, your serial tasks (not plans) will be executed on any available core depending on the overall workload of the system.