An upcoming change in waitresource format will break sp_whoisactive
dimitri-furman opened this issue · 4 comments
An upcoming change in Azure SQL and future versions of SQL Server will change the format of the waitresource column in sysprocesses and several DMVs to include additional latch diagnostics for all latch waits. For example, instead of 13:1:24323201 for a PAGEIOLATCH_EX wait, we will see 13:1:24323201 (LATCH 0x0000020114CC5FD8: CLASS: BUF_LATCH KP: 0 SH: 0 UP: 0 EX: 1 DT: 0 Sublatch: 0 HasWaiters: 1 Task: 0x000001FDF5F6FC28 AnyReleasor: 1).
On MSSQL builds with this change, sp_whoisactive v11.35 fails with Msg 245, Level 16, State 1, Line 331 Conversion failed when converting the nvarchar value ' 1)' to data type int.
A crude but straightforward fix for this would be to replace RTRIM(sp2.waitresource) on lines
sp_whoisactive/who_is_active.sql
Line 1993 in 459d2bc
sp_whoisactive/who_is_active.sql
Line 2074 in 459d2bc
CASE WHEN CHARINDEX('' (LATCH '', sp2.waitresource) > 0 THEN LEFT(sp2.waitresource, CHARINDEX('' (LATCH '', sp2.waitresource) - 1) ELSE RTRIM(sp2.waitresource) END.
A separate future improvement could make use of the new latch info.
If the proposed fix is acceptable, I can send a PR.
Sounds good to me regarding the fix.
As for the future enhancement idea, I'm not sure how useful this information would be to show in the waits output. Isn't PAGEIOLATCH always a buf latch? Are there some more interesting cases you know of here?
@dimitri-furman were you still interested in working on a PR for the fixes? I'd like to get this issue assigned correctly. If it's not something you can work on quickly, I'll do it.
@amachanic this new info is for all latches, not just PAGEIOLATCH latches. It is intended for troubleshooting when a symbolized dump is available, so perhaps not much use to expose in sp_whoisactive. But we don't yet have enough data on its usefulness broadly. Can revisit later, after this has been available for some time.
@erikdarlingdata - I should get some time to work on the PR within next 1-2 weeks. Please feel free to do it if you'd like, or assign to me and I'll get to it within that timeframe.
@dimitri-furman great, I've assigned it to you. If you don't get to it in a couple weeks, I'll take it over. Thanks!