search

amachanic / sp_whoisactive

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

Enhacements related to sort order and CPU_Delta #32

Closed rrg92 closed 3 years ago

rrg92 commented 4 years ago

I added some enhancements related to @sort_order parameter and CPU_Delta column.

Changes related to sort_order

Added the @numeric_sort to force whoisactive sort a column by its numeric value, not text value without losing formatted output.

Changes in CPU_Delta

Before this, If a second snapshot is taken, and new session appears after the first CPU_delta result in NULL. A "real" delta not exists, but for simplicity and better analyze current server workload (in special when multiple queries running less than delta interval) adding current CPU spent to CPU_delta can help analyze these scenarios. For example, I can sort by CPU_delta numeric value:

sp_whoisactive @delta_interval  = 1, @get_task_info = 2,@sort_order = '[CPU_Delta] DESC' ,@numeric_sort = 1

Queries that are spending many CPU since last snapshot will appear on top rows of results. Queries that appear after the first snapshot will sort following in order which its spends CPU.

amachanic commented 4 years ago

Thanks for the PR. While I think your idea for improving delta mode is a good one, I'm not a huge fan of the implementation. It seems less than ideal to add an additional parameter that only applies in this one case. A "sort" parameter side-effecting the delta output also makes it a bit strange.

What if we just eliminate this param altogether and change the way delta is output across the board, such that new sessions' data is always included in the "delta" output?

rrg92 commented 4 years ago

Hello Adam. Thanks for your answer.

About the sort parameter, this is a sample on a real production system (sql 2019): image

The idea of @numeric_sort = 1 is sort using number rules (not text) any columns that you pass in @sort_order. For example:

sp_whoisactive @delta_interval  = 1, @get_task_info = 2,@sort_order = '[CPU] DESC' ,@numeric_sort = 1
sp_whoisactive @delta_interval  = 1, @get_task_info = 2,@sort_order = '[reads_delta] DESC' ,@numeric_sort = 1

In some cases, using @sort_order don't sort correctly, and I guess this is due to formatted output generated by the format output parameter. Check this sample from the same real production system: image

I did some more tests and note this seems happens only with CPU_Delta, but this is just a guess. If confirmed, I agree that we must try to fix the reason CPU_delta doesn't sort correctly... and the @numeric_sort parameter becomes useless. I will provide a new commit removing it and changes made for it...

About the last sentence that you say: What if we just eliminate this param altogether and change the way delta is output across the board, such that new sessions' data is always included in the "delta" output?

Let me see if I understand: You want to do this same approach with CPU_Delta that I do in PR to all other "*_delta" columns? If yes, I will provide a new PR with this enhancement. I'm right?

Adam, thanks again for your time and attention!

amachanic commented 3 years ago

@rrg92 Sorry for the long delay! "You want to do this same approach with CPU_Delta that I do in PR to all other "*_delta" columns?" Yes, that was exactly my thought.

erikdarlingdata commented 3 years ago

@rrg92 we haven't heard back from you in a while about continuing to work on this PR. If you decide you want to, let us know and we'll re-open it. Thanks!