Result of sp_WhoIsActive is periodically saved. But what can we use it for?

Question

Result of sp_WhoIsActive is periodically saved. But what can we use it for?

VoldemarG

Hall of Fame

Points: 3883
More actions
March 31, 2020 at 4:16 am

#3739192

We have a job that polls and does exec of the below every 10 minutes, and saves the result into WhoActive table.
The exact call with these parameters is below.
My question is: In what scenarios and how can we query the table WhoActive in the most meaningful way to solve a particular problem or production outage?
Does anyone know where possibly at Brent Ozar's or other sites where certain queries are published that people use to query such repository of spWhoIsActive calls ? Basically, I am trying to figure out why we are saving this info and use Gs of space for this table,? how can we best take advantage of this kind of historical info?
EXEC sp_WhoIsActive @get_outer_command = 1, @get_plans = 1,@find_block_leaders = 1,
@sort_order = '[blocked_session_count] DESC',@destination_table = WhoActive ;
Likes to play Chess

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 1

Use your imagination. 😀 You can use it to figure out what causes the most blocking at certain times of the day not to mention that if you fix such a thing, you can actually determine how much of an improvement or addition problem you've created. You can also use it to see what the worst queries are over different times of the day and also determine which queries should be fixed first.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

There are several uses:

If tempdb grows you can identify queries that use the most tempdb
You can find your most resource consuming queries based on:
1. duration
2. tempdb use
3. memory use
4. cpu use
If you do not have query store because you are on 2014 or earlier you could do some work to potentially find regressed queries or queries that are prone to parameter sniffing.

We store this information at 1 minute intervals on several servers, although I don't think we are finding block leaders there I'd have to check, but we only store the data for week to minimize storage. There isn't really a reason to keep this data for longer than a month in my opinion.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

frederico_fonseca SSCoach Points: 16171 More actions · Answer 3

lots of info can be achieved as mentioned.

On my shop for some of the critical servers we don't even use sp_whoisactive but we use the DMV's to get similar information - and we run them every 10 seconds inserting into a dba table.

with this type of information you can easily see who is blocking who and for how long and which sql was being executed by each session and what were the wait types - and a bit more info.

and you can also identify all sql's that are running for more than 10 seconds (some even less), when they are run and also who is running them.

but I do have to say that running every 10 mins will most likely not catch things that may be causing issues.