So, CTEs (Common Table Expressions) are fast and give you flexibility. This is a fact.…
If you check in the Task Manager on the Server and find that it looks like this:
Why are not all cores used? If we make som further investigations like SQL Server properties:
SQL Server has detected all 8 cores and if we then check the Processor settings:
All cores will be used, nothing that explains the behavior.
If we than check the dmv:s we get that 4 cores is offline, what can cause that?
Finally we check the SQL Server log and in one of the startup messages you can get the solution:
SQL Server detected 8 sockets with 1 core per socket and 1 logical processors per socket 8 total logical processors; using 4 logical processors based on SQL Server licensing. This is an informational message; no user action is required
In this case we can change the vmWare setup and give the VM 2 sockets with 4 cores per sockets instead, then SQL Server can detect and use 8 cores.
/Christian