sp_BlitzLock - Arithmetic overflow in the column wait_time_hms of the chopsuey CTE when dp.wait_time adds up to more than 2147483647

[VladDBA]

I already have the fix for this and I've forked the Dev branch to implement it and make a pull request.

Version of the script
8.12

What is the current behavior?
At the moment, the following block of code

         SELECT
             database_name =
                 dp.database_name,
             dow.object_name,
             wait_days =
                 CONVERT
                 (
                     nvarchar(30),
                     (
                         SUM
                         (
                             CONVERT
                             (
                                 bigint,
                                 dp.wait_time
                             )
                          ) / 1000 / 86400
                     )
                 ),
             wait_time_hms =
                 CONVERT
                 (
                     nvarchar(30),
                     DATEADD
                     (
                         MILLISECOND,
                         (
                             SUM
                             (
                                 CONVERT
                                 (
                                     bigint,
                                     dp.wait_time
                                  )
                             )
                         ),
                         0
                    ),
                    14
                 )
            FROM #deadlock_owner_waiter AS dow
            JOIN #deadlock_process AS dp
              ON (dp.id = dow.owner_id
                  OR dp.victim_id = dow.waiter_id)
              AND dp.event_date = dow.event_date
            WHERE 1 = 1
            AND (dp.database_name = @DatabaseName OR @DatabaseName IS NULL)
            AND (dp.event_date >= @StartDate OR @StartDate IS NULL)
            AND (dp.event_date < @EndDate OR @EndDate IS NULL)
            AND (dow.object_name = @ObjectName OR @ObjectName IS NULL)
            AND (dp.client_app = @AppName OR @AppName IS NULL)
            AND (dp.host_name = @HostName OR @HostName IS NULL)
            AND (dp.login_name = @LoginName OR @LoginName IS NULL)
            GROUP BY
                dp.database_name,
                dow.object_name

Produces the following error message:

Finished at Jan 26 2023  3:58:31:743PM
Check 11 deadlock wait time per object Jan 26 2023  3:58:31:980PM
Msg 8115, Level 16, State 2, Procedure sp_BlitzLock, Line 2415 [Batch Start Line 0]
Arithmetic overflow error converting expression to data type int.

If the current behavior is a bug, please provide the steps to reproduce.
This requires an instance with a shameful amount of deadlocks and related wait times, or just run the following query:

SELECT CONVERT(NVARCHAR(30),DATEADD
                     (MILLISECOND,2147483648,0),14);

What is the expected behavior?
To not error out
Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
13.0.5865.1, but it's not version specific, it's because DATEADD only accepts INT increments

[VladDBA]

Just realized that my fix might not be the best, since it ends cutting accuracy down to seconds in the case of case sum(dp.wait_time) > max int

[BrentOzar]

Thanks for the pull request! I agree, lower accuracy totally makes sense here. Great idea. Merging this into the dev branch, will be in the next release with credit to you in the release notes.