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

  • 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 ;

    Voldemar likes to play CHESS (and IS good at it!)

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • There are several uses:

    1. If tempdb grows you can identify queries that use the most tempdb
    2. You can find your most resource consuming queries based on:

      1. duration
      2. tempdb use
      3. memory use
      4. cpu use

    3. 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

  • 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.

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

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