2

We have an application that uses MSSync to download data from a server into a client's SQL Server Express 2005. The client's have a dual core with hyperthreading so, a total result of 4 cores.

I have done a lot of research on SQL Server Express' limitations, and I think it comes down to 1 physical socket, but using up to 4 cores in that socket. But, that raises some questions in contrast with what I perceive in practice.

Our sync process is maxing out 1 core, drowning the sync process, resulting in 10-20 times slower performance than on devices that have higher GHz and not maxing out the single core that is being used by SQL Server Express 2005.

But: if SQL Server is allowed to use 4 cores in 1 socket, why is it using only 1 for our sync process? Is this because 1 connection has a dedicated core? Or am I not understanding the limit specs correctly? Would upgrading SQL Server Express to a modern version be useful to get it to use more cores?


I have done some more research. I used SQL Server Express 2012 and even SQL Server Developer 2012 and ALL max out only 1 core. So, apparently, this has nothing to do with an Express restriction.

It is probably a technical restriction where your querying within a single connection/transaction just stays on a single core. Likely, this is a logical requirement for guaranteeing transactional consistency.

What I do see as of SQL Server 2012, is that the load will swap to another core now and then, but it will never use more than 1 core at the same time. Not even the Dev edition.

If anyone could confirm these assumptions, that would be welcome.

2
  • As per MSDN Bol SQL Server 2005 Express has support like Limited to one CPU, where CPU means sockets, not limited to cores and hyperthreading. Up to 2008 this was the policy and there isn't any documentation left for the unsupported 2005 version but the 2008 R2 documentation still states:

SQL Server supports the specified number of processor sockets multiplied by the number of logical CPUs in each socket. For example, the following is considered a single processor for purposes of this table:

  • A single-core, hyper-threaded processor with 2 logical CPUs per socket.

  • A dual-core processor with 2 logical CPUs.

  • A quad-core processor with 4 logical CPUs.

  • The 2012 Express edition supports 1 CPU with up to 4 cores. For 2012 the calculation of cores and sockets has become a lot more complex (accounting for virtualisation) as can be seen in the documentation which still states

Limited to lesser of 1 Socket or 4 cores

  • The 2012 Developer edition supports the same as the Enterprise edition, i.e. no limits. If you see limits, they are due to your use, settings and workload (only one connection, MAXDOP settings, queries not chosen for parallel plans).

Further References:

If your workload isn't going parallel across cores regardless of the license that could have several reasons such as

  • MaxDOP set to one
  • The execution plan choosing a non-parallel plan for some reason
  • Your tool generating a single query at a time instead of launching queries simultaneously
5

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:

sys.dm_os_schedulers

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.