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.

Sign up for GitHub

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

Jump to bottom

sp_BlitzWho - Add @GetOuterCommand to get contents of the inputbuffer per session_id (like sp_WhoIsActive) #2887

Closed
Adedba opened this issue May 5, 2021 · 4 comments · Fixed by #2944
Closed

sp_BlitzWho - Add @GetOuterCommand to get contents of the inputbuffer per session_id (like sp_WhoIsActive) #2887

Adedba opened this issue May 5, 2021 · 4 comments · Fixed by #2944
Assignees
@Adedba
Labels
enhancement sp_BlitzWho
Milestone
2021-07 Release

Comments

@Adedba
Copy link
Contributor

Adedba commented May 5, 2021

Is your feature request related to a problem? Please describe.
New feature.

Describe the solution you'd like
Add a new parameter @GetOuterCommand which will grab the contents of DBCC inputbuffer and show as a new column called outer_command similar to sp_Whoisactive @get_outer_command = 1.

SQL 2012 and 2014 can get the input buffer from a call to dbcc inputbuffer per session_id whereas SQL2016+ would be able to obtain the same information from sys.dm_exec_input_buffer

Describe alternatives you've considered
Use sp_WhoIsActive which is amazing but so is sp_BlitzWho!

Are you ready to build the code for the feature?
Yep Happy to give this a go if it's something that people want?
Adedba added a commit to Adedba/SQL-Server-First-Responder-Kit that referenced this issue May 5, 2021
@Adedba
Initial mock up for get outer command
b820a86 
BrentOzarULTD#2887 An initial mock up for get outer command addition. Not a finished version this is just putting in place the main code less any log to table elements.
@Adedba
Copy link
Contributor Author

Adedba commented May 5, 2021

Thrown together a mock up , it's by no means finished but gives an idea of the intention. Works in ssms as long as you are not trying to log it to a table as i haven't gone any further in case this is an unwanted feature. :)

@Adedba Adedba self-assigned this May 7, 2021
Adedba added a commit to Adedba/SQL-Server-First-Responder-Kit that referenced this issue May 7, 2021
@Adedba
Logic changes and Log to table code changes added
ddf9ac5 
BrentOzarULTD#2887 Replaced the dbcc inputbuffer loop with a cursor to reduce additional reads for retrieving the next session_id to execute.

Added a new column to the output table 'outer_command'

Added relevant code to allow log to table for both @GetOuterCommand - 0 and = 1
Adedba added a commit to Adedba/SQL-Server-First-Responder-Kit that referenced this issue May 7, 2021
@Adedba
cast event_info as Nvarchar(4000) to stop truncation errors
b95bdf3 
BrentOzarULTD#2887 Truncation errors were occurring when executing against sys.dm_exec_input_buffer as the event_info from this dmv is an NVARCHAR(MAX) versus DBCC INPUTBUFFER which is NVARCHAR(4000).

Added a cast to ensure we are always getting a max of 4000 chars.
@Adedba
Copy link
Contributor Author

Adedba commented May 7, 2021

The cursor version of the inputbuffer code is only added to the dynamic SQL if sys.dm_exec_input_buffer does not exist otherwise this dmv is used as an outer apply.

I initially thought that sys.dm_exec_input_buffer was not available in SQL 2012 but I have tested with SQL 2012 SP4 and the dmv is present and working.

Depending on what version support we are currently providing for sp_BlitzWho and if we are really not feeling the cursor for this change we could perhaps skip the check if the dmv does not exist.

I will park this where it is for now until we decide what the best approach is:

  1. Leave as is and have a cursor execute dbcc inputbuffer per session_id if sys.dm_exec_input_buffer is not present when @GetOuterCommand = 1
  2. Only provide and output for outer_command if the dmv 'sys.dm_exec_input_buffer' is present and @GetOuterCommand = 1

I guess it all depends how anti cursor we are feeling ha!

@Adedba
Copy link
Contributor Author

Adedba commented Jul 1, 2021

I will give this a round of testing next week and get a PR submitted.

@Adedba Adedba mentioned this issue Jul 7, 2021
Merged
@BrentOzar BrentOzar linked a pull request Jul 13, 2021 that will close this issue
2887 sp blitz who add @get outer command to get contents of the inputbuffer per session #2944
Merged
@BrentOzar BrentOzar added this to the 2021-07 Release milestone Jul 13, 2021
@BrentOzar
Copy link
Member

Thanks for the pull request! I haven't tested on 2008/2008R2, but looks good so far. Merging into the dev branch, will be in the next release with credit to you in the release notes.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@Adedba Adedba

Labels
enhancement sp_BlitzWho
Projects
None yet
Milestone
2021-07 Release
2 participants
@BrentOzar @Adedba