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.
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.
Returns information about each request that is executing in SQL Server.
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.
Returns one row for each task that is active in the instance of SQL Server. A task is the basic unit of execution in SQL Server.
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 ...
...also known as SOS scheduler, manages worker threads that require processing time to carry out work on behalf of tasks. Each scheduler is mapped to an individual processor (CPU). The time a worker can remain active in a scheduler is called the OS quantum, with a maximum of 4 ms. After its quantum time expires, a worker yields its time to other workers that need to access CPU resources, and changes its state. This cooperation between workers to maximize access to CPU resources is called cooperative scheduling, also known as non-preemptive scheduling. In turn, the change in worker state is propagated to the task associated with that worker, and to the request associated with the task.
The threads are not bound to a specific core/processor unless process affinity has been turned on a the OS level.
(emphasis mine)
By default, each instance of SQL Server starts each thread, and the operating system distributes threads from instances of SQL Server among the processors (CPUs) on a computer, based on load. If process affinity has been enabled at the operating system level, then the operating system assigns each thread to a specific CPU. In contrast, the SQL Server Database Engine assigns SQL Server worker threads to schedulers that distribute the threads evenly among the CPUs.
To carry out multitasking, for example when multiple applications access the same set of CPUs, the operating system sometimes moves worker threads among different CPUs.
Reference: Thread and Task Architecture Guide (Microsoft | SQL Docs)
So, my question is...will all the serial plans with a cost less than 55 use only the first core?
No, your serial tasks (not plans) will be executed on any available core depending on the overall workload of the system.
A serial plan can be "bound" to any core that is enabled (the scheduler is ONLINE). It is not bound to the first core.
