August 17, 2013 at 10:08 am
I have enforced MAXDOP on my query using hint, i have enforce it to 8 but when i look at the task manager i see all 16 cores being utilized? There is nothing else, i mean literally nothing else running on this server other than my process. If i set the hint to 4, i see 8 core being utilized? Any thoughts?
August 17, 2013 at 4:17 pm
How does the query plan look like? If there are two parallel sections, each of them could use 8 threads for a total of 16.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 17, 2013 at 7:20 pm
curious_sqldba (8/17/2013)
I have enforced MAXDOP on my query using hint, i have enforce it to 8 but when i look at the task manager i see all 16 cores being utilized? There is nothing else, i mean literally nothing else running on this server other than my process. If i set the hint to 4, i see 8 core being utilized? Any thoughts?
Any chance that you have hyper-threading running?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 19, 2013 at 12:12 pm
Jeff Moden (8/17/2013)
curious_sqldba (8/17/2013)
I have enforced MAXDOP on my query using hint, i have enforce it to 8 but when i look at the task manager i see all 16 cores being utilized? There is nothing else, i mean literally nothing else running on this server other than my process. If i set the hint to 4, i see 8 core being utilized? Any thoughts?Any chance that you have hyper-threading running?
The server on which i see this issue doesn't have hyperthreading enabled ( msinfo32 shows physical=logical).Is that the reason why i see twice the number of cores being used?
August 19, 2013 at 2:45 pm
What are the results of this query:
SELECT
cpu_count / hyperthread_ratio AS CPUs, *
FROM sys.dm_os_sys_info
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 19, 2013 at 4:13 pm
curious_sqldba (8/19/2013)
The server on which i see this issue doesn't have hyperthreading enabled ( msinfo32 shows physical=logical).Is that the reason why i see twice the number of cores being used?
So have you looked at the query plan yet, to see if my theory that there are two regions of parallelism is correct?
Also, have you checked the process in sys.dm_os_tasks while the query is running?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 20, 2013 at 8:45 am
Erland Sommarskog (8/19/2013)
curious_sqldba (8/19/2013)
The server on which i see this issue doesn't have hyperthreading enabled ( msinfo32 shows physical=logical).Is that the reason why i see twice the number of cores being used?So have you looked at the query plan yet, to see if my theory that there are two regions of parallelism is correct?
Also, have you checked the process in sys.dm_os_tasks while the query is running?
In sys.sysprocesses wait type is always 'SOS_SCHEDULER_YIELD' when my CTP is 5 and MAXDOP is 0, what do you want me to check in sys.dm_os_tasks ?
August 20, 2013 at 9:01 am
curious_sqldba (8/20/2013)
In sys.sysprocesses wait type is always 'SOS_SCHEDULER_YIELD' when my CTP is 5 and MAXDOP is 0, what do you want me to check in sys.dm_os_tasks ?
How many rows there are for the session_id running the query.
Which for matter you also can see in sysprocess, as there is one row per execution context id (ecid).
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 20, 2013 at 9:24 am
Erland Sommarskog (8/20/2013)
curious_sqldba (8/20/2013)
In sys.sysprocesses wait type is always 'SOS_SCHEDULER_YIELD' when my CTP is 5 and MAXDOP is 0, what do you want me to check in sys.dm_os_tasks ?How many rows there are for the session_id running the query.
Which for matter you also can see in sysprocess, as there is one row per execution context id (ecid).
Or just run:
SELECT
cpu_count / hyperthread_ratio AS CPUs, *
FROM sys.dm_os_sys_info
which will directly tell you.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 20, 2013 at 1:49 pm
ScottPletcher (8/20/2013)
Erland Sommarskog (8/20/2013)
curious_sqldba (8/20/2013)
In sys.sysprocesses wait type is always 'SOS_SCHEDULER_YIELD' when my CTP is 5 and MAXDOP is 0, what do you want me to check in sys.dm_os_tasks ?How many rows there are for the session_id running the query.
Which for matter you also can see in sysprocess, as there is one row per execution context id (ecid).
Or just run:
SELECT
cpu_count / hyperthread_ratio AS CPUs, *
FROM sys.dm_os_sys_info
which will directly tell you.
That query just tells us how many CPUs are in the machine and whether the physical CPUs expose more than one logical CPU per. It says nothing about how many CPUs a particular session might be using at a given time while a query is executing.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply