SQL Server 2005 Express supports a single socket with up to four cores.
Each core is mapped to a SQLOS scheduler. There is one VISIBLE ONLINE scheduler per core in the sys.dm_os_schedulers dynamic management view. The following screenshot was taken from an instance of SQL Server 2005 Express running on a machine with a single socket CPU containing four cores:
The general SQLOS model is:
- Statements in a client batch are mapped to one or more tasks.
- Each task is bound to a worker thread.
- A worker thread is bound to a scheduler.
In a serial execution plan, there is just one task, and only one scheduler is used. SQL Server Express Edition only supports serial execution. Therefore, each statement can only use a single core.
Other editions of SQL Server are capable of generating parallel execution plans, where a single statement may generate multiple tasks, which can then run on multiple schedulers concurrently. This is generally limited to operations that read data (not write it).
Before SQL Server 2014, parallel data modifications were limited to index (re)build plans. General INSERT, UPDATE, DELETE, and MERGE statements could only use parallelism on the read side of the execution plan; the data modifications were always serial (using a single task/worker/scheduler).
SQL Server 2014 Enterprise Edition (and equivalent Developer and Evaluation Editions) added the ability to do parallel writes for SELECT INTO statements only. SQL Server 2016 Enterprise Edition further adds parallel writes for some INSERT...SELECT statements.
All this means that SQL Server Express Edition can only use one scheduler (= one core) per client statement batch. Having four cores simply means that four separate statements (from different clients/connections) can be executing concurrently.
If the affinity mask bit is not set for a particular scheduler, Windows may choose which physical execution unit to run the scheduler on, and this may vary over timeslices. If the affinity mask bit is set, the scheduler is bound to a particular execution unit, and Windows cannot move it around.
It is still possible (in the right circumstances) for SQL Server Express to use multiple logical processors to concurrently bulk load a single unindexed heap table. This requires four separate statements (on four separate connections) targeting the same table, and specific conditions must be met. The details are contained in The Data Loading Performance Guide. There is an element of chance as well, in that the separate statements must be bound to different schedulers at connection time by SQL Server. That is a complex topic of its own.
I am not familiar with "MSSync", and the question doesn't state exactly what the task is, but in many cases achieving minimally-logged inserts is more than enough to meet performance objectives. Avoiding sorts on the server side is often an important factor as well. High CPU utilization is often an indication of server-side sorting when loading data. It is often avoidable.
A properly configured minimally-logged bulk load (without sorting) should saturate the server's I/O abilities before it runs out of CPU. Perhaps a follow up question could be asked with details of the exact task being performed. There may be other improvement options as well.
