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.
Best Answer
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: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