Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

sp_BlitzWho always shows milliseconds as 000 #2321

Closed
Adedba opened this issue Mar 10, 2020 · 1 comment
Closed

sp_BlitzWho always shows milliseconds as 000 #2321

Adedba opened this issue Mar 10, 2020 · 1 comment
Assignees
Labels

Comments

@Adedba
Copy link
Contributor

@Adedba Adedba commented Mar 10, 2020

Version of the script
SELECT @Version = '7.93', @VersionDate = '20200217';

What is the current behavior?
Duration always shows the milliseconds as 000

If the current behavior is a bug, please provide the steps to reproduce.

Run a relatively long running query in one window in SSMS
Open another SSMS query window and 'EXEC sp_BlitzWho;'
The elapsed_time column will show the seconds incrementing but millieseconds will show 000

What is the expected behavior?
Show the milliseconds aswell as the rest of the duration

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?

Tested on SQL 2012

Found the part of the code with the issue:

There are two dynamic strings depending on SQL version but both of these lines are identical:

Replace:

SELECT @StringToExecute = N' COALESCE( CONVERT(VARCHAR(20), (ABS(r.total_elapsed_time) / 1000) / 86400) + '':'' + CONVERT(VARCHAR(20), DATEADD(SECOND, (r.total_elapsed_time / 1000), 0), 114) , CONVERT(VARCHAR(20), DATEDIFF(SECOND, s.last_request_start_time, GETDATE()) / 86400) + '':'' + CONVERT(VARCHAR(20), DATEADD(SECOND, DATEDIFF(SECOND, s.last_request_start_time, GETDATE()), 0), 114) ) AS [elapsed_time] ,

With:

SELECT @StringToExecute = N' COALESCE( CONVERT(VARCHAR(20), (ABS(r.total_elapsed_time) / 1000) / 86400) + '':'' + CONVERT(VARCHAR(20), (DATEADD(SECOND, (r.total_elapsed_time / 1000), 0) + DATEADD(MILLISECOND, (r.total_elapsed_time % 1000), 0)), 114), CONVERT(VARCHAR(20), DATEDIFF(SECOND, s.last_request_start_time, GETDATE()) / 86400) + '':'' + CONVERT(VARCHAR(20), DATEADD(SECOND, DATEDIFF(SECOND, s.last_request_start_time, GETDATE()), 0), 114) ) AS [elapsed_time] ,

To test the code in islolation outside of BlitzWho you can you this:

`DECLARE @p1 INT = 1250
DECLARE @p2 DATETIME = DATEADD(MILLISECOND,-@p1,GETDATE())

SELECT
COALESCE(
CONVERT(VARCHAR(20), (ABS(@p1) / 1000) / 86400)
+ ':'
+ CONVERT(VARCHAR(20), DATEADD(SECOND, (@p1 / 1000), 0), 114),
CONVERT(VARCHAR(20), DATEDIFF(SECOND,@p2, GETDATE()) / 86400) + ':'
+ CONVERT(VARCHAR(20), DATEADD(SECOND, DATEDIFF(SECOND, @p2, GETDATE()), 0), 114)
) AS [old code]
,
COALESCE(
CONVERT(VARCHAR(20), (ABS(@p1) / 1000) / 86400)
+ ':'
+ CONVERT(VARCHAR(20), (DATEADD(SECOND, (@p1 / 1000), 0) + DATEADD(MILLISECOND, (@p1 % 1000), 0)), 114),
CONVERT(VARCHAR(20), DATEDIFF(SECOND,@p2, GETDATE()) / 86400) + ':'
+ CONVERT(VARCHAR(20), DATEADD(SECOND, DATEDIFF(SECOND, @p2, GETDATE()), 0), 114)
) AS [new code]

`

@BrentOzar BrentOzar self-assigned this Mar 23, 2020
@BrentOzar BrentOzar added this to the 2020-03 Release milestone Mar 23, 2020
BrentOzar added a commit that referenced this issue Mar 23, 2020
Shows accurate milliseconds counts. Closes #2321.
BrentOzar added a commit that referenced this issue Mar 23, 2020
…onds

#2321 sp_BlitzWho milliseconds
@BrentOzar

This comment has been minimized.

Copy link
Member

@BrentOzar BrentOzar commented Mar 23, 2020

Great catch! Added to the dev branch, will be in the March release.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

2 participants
You can’t perform that action at this time.