April 28, 2014 at 5:20 pm
Hi All,
First the configuration details.
SQL 2008R2 Standard, SP2, 64Bit on Windows 2008R2 EE 64 Bit.
HP DL 380 with 2x4 processor (hyper-threading OFF) and 128GB RAM
Server MaxDop set to 4 (based on various recommendations) and Optimize for AdHoc is True.
I have several many:-P queries that usually go parallel and a lot of them probably should not or should have better index support or should have more up to date indexes or ...
So I am trying to sort out which is which. I developed a little query to return data from Sys.dm_os_tasks, sys.dm_exec_requests, sys.dm_exec_connections, and sys.dm_exec_sessions.
I am confused about a number of things that are returned in my query. First I think I have the join order and conditions set correctly.
From sys.dm_os_tasks as ot
inner join sys.dm_exec_requests as R
on R.request_id = ot.request_id
and R.session_id = ot.session_id
Inner JOIN sys.dm_exec_connections As C
ON R.connection_id = C.connection_id
AND R.session_id = C.most_recent_session_id
Inner JOIN sys.dm_exec_sessions As S
ON C.session_id = S.session_id
Here is some sample results from the query. One thing that really confuses me is how SPID 99 can have 12 Task Contexts when Server Max DOP is 4. The query is a straight forward "Insert into .. Select From" and it is appropriate to go parallel. But why isn't it just 4 Contexts? (or maybe 5).
SPID DB Id Task State Pending Task Task Task
I/O Context Context Scheduler
Switches Id Id
91 5 SUSPENDED 267 556 0 3
91 5 SUSPENDED 0 10727 1 1
91 5 SUSPENDED 0 19447 2 3
91 5 SUSPENDED 0 9449 3 0
91 5 SUSPENDED 0 9944 4 2
91 5 SUSPENDED 3419 1135 5 0
91 5 SUSPENDED 3200 1519 6 3
91 5 SUSPENDED 3425 1087 7 1
91 5 SUSPENDED 3122 1040 8 2
91 5 SUSPENDED 0 5 9 1
91 5 SUSPENDED 0 5 10 0
91 5 SUSPENDED 0 5 11 3
91 5 SUSPENDED 0 2 12 2
99 5 SUSPENDED 844 7527 0 4
99 5 SUSPENDED 332 971 1 5
99 5 SUSPENDED 314 135 3 6
99 5 SUSPENDED 384 206 4 4
I am also a confused by the Context Switches and Pending I/O results. Does this mean that 5, 6, 7, and 8 are doing all of the work (based on pending I/O) or that 1, 2, 3, and 4 are doing the work based on Context Switches?
Any hints will be appreciated.
April 28, 2014 at 5:24 pm
Sorry I thought the results table was formatted but I guess I messed it up.
The columns are:
1. - SPID
2. - DB Id
3. - Task State
4. - Task Pending I/O
5. - Task Context Switches
6. - Task Context Id
7. - Task Scheduler Id
All of the "Task xxx" columns come from sys.dm_os_tasks
Thanks for any assistance
April 29, 2014 at 2:41 am
MAXDOP limits the number of *runnning* tasks, not the total number of tasks.
If four operators in a query all parallel and MAXDOP is 4, you can easily see 16 or 20 threads. Max 4 of them will be running.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 29, 2014 at 8:25 am
Thanks Gail,
If I understand it correctly then I could see many Context Ids associated with a Session Id but I should never see more than 4 Scheduler Ids at any one time. Is that correct?
Can you tell me how I would tell if one, or only a few of the threads are doing all of the work?
April 29, 2014 at 9:07 am
You'll never see more than 4 threads in the RUNNABLE or RUNNING states. Scheduler ID is just which scheduler the thread is associated with.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply