search

amachanic / sp_whoisactive

sp_whoisactive
GNU General Public License v3.0
1.17k stars 289 forks source link

An upcoming change in waitresource format will break sp_whoisactive #56

Closed dimitri-furman closed 2 years ago

dimitri-furman commented 3 years ago

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 https://github.com/amachanic/sp_whoisactive/blob/459d2bcef5474baaf5a9e207ab60ee2f55ab48b2/who_is_active.sql#L1993 and https://github.com/amachanic/sp_whoisactive/blob/459d2bcef5474baaf5a9e207ab60ee2f55ab48b2/who_is_active.sql#L2074 with 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.

amachanic commented 3 years ago

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?

erikdarlingdata commented 3 years ago

@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.

dimitri-furman commented 3 years ago

@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.

erikdarlingdata commented 3 years ago

@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!