sp_BlitzWho returns multiple records for some session IDs

[nirst]

Do you want to request a feature or report a bug?
Bug
Script: sp_BlitzWho
Version 5.4 - June 05, 2017 onwards

What is the current behavior?
Since this version, sp_BlitzWho returns multiple records for some session IDs due to the cross apply to tempdb allocations. This returns multiple rows when sessions have multiple exec_context_ids in sys.dm_db_task_space_usage.

What is the expected behavior?
A single row per session - I don't think the cross apply is working as expected.

SQL Version
I've seen this issue on Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64)

[BlitzErik]

Hello!

I haven't come across this. Can you add some more details to this the next time it happens? We don't join to exec context id, and we're already summing these values

I'm not calling you crazy, because we've run into multiple rows returned bugs in the past, just want to make sure i'm focusing on the right part of the code.

If it's as simple as adding a TOP 1 to the CROSS APPLY and you want to code and test it, feel free.

Thanks!

[nirst]

Nope - you're absolutely right. I'm definitely seeing multiple rows though and its only for sessions where there are multiple exec_context_ids but perhaps that was a red herring - I put 2 and 2 together based on the 5th June version being the first instance where this cropped up. I'll see if I can narrow it down further.

[nirst]

OK the issue seems to be on line 261:

The join creates multiple lines when a session has multiple records in dm_db_task_space_usage (e.g. multiple exec_content_ids).

[BlitzErik]

Hahaha, well the first thing is that left join shouldn't be there.

All the information for that part for the query comes from here:

Up in the select list, we use the bits from the outer apply.

Lemme crank out a quick version for you to test.

[BlitzErik]

Alright, this is probably going to need a TOP 1 in it, but incremental changes are probably smart.

Can you test out the version here and let me know if anything is wonky?

https://raw.githubusercontent.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/933fa6a7343dd6cdc04ac5db5f97631d67e8ed1e/sp_BlitzWho.sql

[nirst]

Yep that worked a treat!

[BlitzErik]

Awesome! I'm going to leave this open in case you spot anything else, or in case anyone else comes along with the same issue. It will likely go into the next FRK release.

Thanks!