Unanswered: Showing all portions of parallel execution...
I need to know how I can use the new dynamic management views in SQL Server 2005 to view parallel query execution. I have a batch that generally runs in parallel that I'm using to test because it executes for over two hours. "SELECT * FROM sysprocesses" returns (in this case) between 1 and 5 rows depending on when I happen to catch the process. Selecting from sys.dm_exec_[sessions | requests | connections | cursors] always returns only one row. Despite the fact that the sysprocesses query returns absolutely no metrics for CPU, Physical I/O or MemUsage (they're always zero) for the "child" rows (one row is always active and displays values for the aforementioned metrics) I wonder how to get the same output from the dm_ views. Laughably, although sysprocesses is marked for deprecation, and we're advised to stop using it, sp_who and sp_who2 still reference it in 2005. Any ideas?
"My life is in the hands of any fool who makes me lose my temper."
- Joseph Hunter