MicrosoftMicrosoft
Microsoft
Hello goran_schwarz
Thanks for your question (s). I think this sample output will answer most (if not all of them).
The following is an output of a parallel query (actually ALTER INDEX job) that shows multiple rows for a single session_Id. This was a join between sysprocesses and sys.dm_exec_requests on one of my colleague's SQL Server machines while he was running a re-indexing job against his AdventureWorks database. Actually this output comes from queries generated by this PerfrStats script and is trimmed for brevity
session_id request_id ecid task_state open_trans request_cpu_time request_logical_reads request_reads request_writes tran_name scheduler_id command program_name
---------- ----------- ----------- --------------- ----------- ---------------- --------------------- -------------------- -------------------- ---------------------------- --------------- --------------------------------------------------
51 0 3 RUNNING 2 226549 5860461 1427 1086421 ALTER INDEX 1 ALTER INDEX Microsoft SQL Server Management Studio - Query
51 0 7 RUNNABLE 2 226549 5860461 1427 1086421 ALTER INDEX 3 ALTER INDEX Microsoft SQL Server Management Studio - Query
51 0 6 RUNNING 2 226549 5860461 1427 1086421 ALTER INDEX 2 ALTER INDEX Microsoft SQL Server Management Studio - Query
51 0 0 SUSPENDED 2 226549 5860461 1427 1086421 ALTER INDEX 6 ALTER INDEX Microsoft SQL Server Management Studio - Query
51 0 4 RUNNING 2 226549 5860461 1427 1086421 ALTER INDEX 0 ALTER INDEX Microsoft SQL Server Management Studio - Query
52 0 0 SUSPENDED 0 36 48 0 0 0 WAITFOR SQLCMD
Note that session_id=51 shows up multiple times in the output as separate rows - one for each thread servicing this request (strictly speaking in this output there were more threads but a filter was applied so some do not show up). You will observe this even if you do a simple SELECT * FROM sys.dm_exec_requests DMV while a parallel query is running.
One of the more useful columns in sys.sysprocesses system view for me personally is the ECID. ECID shows the execution context ID (an auto-generated number) for a request. When a query runs serially, the ECID is always 0. When a query runs multiple threads - parallelly - then the parent ECID is 0 but all the children threads spawned have numbers > 0. In this case actually not all parallel threads are showing because of some WHERE clause filters but you can see that ECID =7 is the highest number here. This means that the request to rebuild an index used at least 8 threads running in parallel (0 through 7).
Note also how tasks assigned to each thread had a different state - some are running, some are waiting to be scheduled (Runnable), some are suspended. Finally, notice how each of the threads are scheduled on a different scheduler_id (CPU).
There are several other useful columns in these two DMVs that can help you with identifying prallelism that I want to turn your attention to. One is parallel_worker_count in sys.dm_exec_requests DMV. The other one is kpid column in sysprocesses, which identifies the Windows thread ID that is executing this part of the task. So with these, you can build a query that meets your needs.
Finally calling your attention to the request_cpu_time, request_logical_reads, request_reads, request_writes in the above output to help answer your additional questions. These are aliased column names of columns you can find in both sys.dm_exec_requests and sys.sysprocesses to measure ongoing CPU, Reads, Writes, etc.