October 22, 2016 at 3:09 am
Hi,
I'm remotely analyzing a SQL installation on a virtual machine in a blade server...
I don't have access to the hardware configuration nor I know the virtualization software... The person responsible for that is unavailable for at least one week...
Hyper threading is enabled... (there are 2 CPUs with 8 cores and HT)
When a VM is created and 8 cores the cores are physical or can HT "cores" be assigned or it depends on the virtualization type used?!
Inside the VM is there a way of knowing the number of physical cores and HT ones, or in SQL Server (scheduler count gives 8) so I can property configure MAXDOP?!
Just another question, probably stupid, about MAXDOP....
Is it correct to have the real number of cores? (8 core cpu + HT MAXDOP = 8)
Or should it be just 6, since MAXDOP is the number of threads to use in a single query, and if I have 1000 users one query shouldn't take the hole CPU... or SQL is "smart" enough to know that there are 1000 users and so no query should use the hole CPU?!?
Thanks,
Pedro
October 23, 2016 at 6:41 am
PiMané (10/22/2016)
Hi,I'm remotely analyzing a SQL installation on a virtual machine in a blade server...
I don't have access to the hardware configuration nor I know the virtualization software... The person responsible for that is unavailable for at least one week...
Hyper threading is enabled... (there are 2 CPUs with 8 cores and HT)
When a VM is created and 8 cores the cores are physical or can HT "cores" be assigned or it depends on the virtualization type used?!
Inside the VM is there a way of knowing the number of physical cores and HT ones, or in SQL Server (scheduler count gives 8) so I can property configure MAXDOP?!
Just another question, probably stupid, about MAXDOP....
Is it correct to have the real number of cores? (8 core cpu + HT MAXDOP = 8)
Or should it be just 6, since MAXDOP is the number of threads to use in a single query, and if I have 1000 users one query shouldn't take the hole CPU... or SQL is "smart" enough to know that there are 1000 users and so no query should use the hole CPU?!?
Thanks,
Pedro
1) VMs can be created to be NUMA-aware and aligned with the hardware's NUMA. This is a good thing.
2) MAXDOP should be capped at the number of physical cores per NUMA node in your VM (if it is NUMA-aware). Or less - which is often appropriate.
3) Don't forget to adjust Cost Threshold for Parallelism too. The default of 5 is ridiculously low on modern hardware.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply