search

amachanic / sp_whoisactive

sp_whoisactive
GNU General Public License v3.0
1.13k stars 281 forks source link

Show Param Values with @Get_Outer_Command = 1 #73

Closed datadill closed 2 years ago

datadill commented 2 years ago

Hey Adam,

When using sp_WhoIsActive @Get_outer_command = 1, I am wondering if it is possible to include the parameter values in the returned text assuming there are any. I know that sp_WhoIsActive @Get_plans = 1 returns the plan XML, which allows you to open the plan and right-click properties on the top-leftmost node to grab the parameter list:

image

Current result of Sql_Command: image

Desired result of Sql_Command: image

If this is something you think is possible and worth adding I'd be happy to try and contribute.

erikdarlingdata commented 2 years ago

You can totally do this - it’s part of the code in sp_BlitzWho. You just have to be really careful because if you get them from a live query plan it can cause 2019 to stack dump: https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/pull/3011

/ I typed this on a phone. Thanks for understanding. /

From: janszedc @.> Sent: Friday, November 26, 2021 12:16:38 PM To: amachanic/sp_whoisactive @.> Cc: Subscribed @.***> Subject: [amachanic/sp_whoisactive] Show Param Values with @Get_Outer_Command = 1 (Issue #73)

Hey Adam,

When using sp_WhoIsActive @Get_outer_command = 1, I am wondering if it is possible to include the parameter values in the returned text assuming there are any. I know that sp_WhoIsActive @Get_plans = 1 returns the plan XML, which allows you to open the plan and right-click properties on the top-leftmost node to grab the parameter list:

[image]https://user-images.githubusercontent.com/49908138/143614166-76526371-17aa-47d6-a9b7-b76d61ddd1ee.png

If this is something you think is possible and worth adding I'd be happy to try and contribute.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHubhttps://github.com/amachanic/sp_whoisactive/issues/73, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AAQJPZPG2NXPMQHQ7MXE5ETUN66HNANCNFSM5I3CKOVQ. Triage notifications on the go with GitHub Mobile for iOShttps://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Androidhttps://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

datadill commented 2 years ago

In that case, perhaps it is best to avoid? I wonder if it is the processing/parsing of XML at the same time of querying. Do you think putting the xml into a #temp table first and then doing the parsing there would be safe even on SQL 2019?

erikdarlingdata commented 2 years ago

No idea, I can’t repro it happening locally in order to test fixes, sorry.

/ I typed this on a phone. Thanks for understanding. /

From: janszedc @.> Sent: Friday, November 26, 2021 2:25:41 PM To: amachanic/sp_whoisactive @.> Cc: Erik Darling @.>; Comment @.> Subject: Re: [amachanic/sp_whoisactive] Show Param Values with @Get_Outer_Command = 1 (Issue #73)

In that case, perhaps it is best to avoid? I wonder if it is the processing/parsing of XML at the same time of querying. Do you think putting the xml into a #temp table first and then doing the parsing there would be safe even on SQL 2019?

— You are receiving this because you commented. Reply to this email directly, view it on GitHubhttps://github.com/amachanic/sp_whoisactive/issues/73#issuecomment-980336040, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AAQJPZLWESTXQXWCSJIHODTUN7NLLANCNFSM5I3CKOVQ. Triage notifications on the go with GitHub Mobile for iOShttps://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Androidhttps://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

erikdarlingdata commented 2 years ago

@janszedc I'm going to assign this to you for now. If you decide you don't want to work on it, or we don't hear back from you in about a month I'll close it out.

amachanic commented 2 years ago

I have to admit, I'm a touch iffy about whether or not this is a good idea in sp_whoisactive, for a couple of reasons:

A) I've always shied away from XML parsing in the database, as it's expensive. And part of sp_whoisactive is supposed to be that it shouldn't exacerbate an already bad situation, if at all possible. This sort of feature is therefore somewhat of a slippery slope. B) I don't think there's any guarantee that this information is all that dependable. On older SQL Server versions, definitely not. And even in newer versions, if there's a multi-statement stored procedure, are you sure to get what you think you're going to get? It seems dicey.

To be clear, I'm not saying no. Just seems like a potentially low-ish value item with high-ish risk.

datadill commented 2 years ago

@amachanic I think you summed up my thoughts exactly when you said: "low-ish value item with high-ish risk". I will close this one out. Thanks guys!

gdoddsy commented 2 years ago

I know you've closed this out, but just wanted to leave some feedback on what was happening in 2019: