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 StatePendingTask Task Task
I/O ContextContext Scheduler
Switches IdId
915SUSPENDED26755603
915SUSPENDED01072711
915SUSPENDED01944723
915SUSPENDED0944930
915SUSPENDED0994442
915SUSPENDED3419113550
915SUSPENDED3200151963
915SUSPENDED3425108771
915SUSPENDED3122104082
915SUSPENDED0591
915SUSPENDED05100
915SUSPENDED05113
915SUSPENDED02122
995SUSPENDED844752704
995SUSPENDED33297115
995SUSPENDED31413536
995SUSPENDED38420644
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