sp_WhoIsActive Version 12 Is Out!

Get’em Daddy


You know, you love it, you often wondered if it would ever get a new version!

Now it has! <— Click there to get it.

What’s New?


  • New parameter, @get_memory_info, that exposes memory grant information, both in two top-level scalar columns and a new XML-based memory_info column.
  • Better handling of the newer CX* parallelism wait types that have been added post-2016
  • A top-level implicit_transaction identifier, available in @get_transaction_info = 1 mode
  • Added context_info and original_login_name to additional_info collection
  • A number of small bug fixes
  • Transition code to use spaces rather than tabs

New file name: Not an enhancement per se, but please note that starting with this release there is a new source file, sp_WhoIsActive.sql. The old file, who_is_active.sql, will be kept around for a few months and then removed. Please migrate any processes that might be using the old name.

What Does It Look Like?


For memory grant information:

You’ll wanna run like so:

EXEC sp_WhoIsActive
    @get_memory_info = 1;

You’ll get back some new columns:

SQL Server sp_WhoIsActive
clicky

In the XML, you’ll see stuff like this, which is pretty cool.

<memory_info>
  <memory_grant>
    <request_time>2021-11-11T05:08:57.870</request_time>
    <grant_time>2021-11-11T05:08:57.870</grant_time>
    <requested_memory_kb>17350600</requested_memory_kb>
    <granted_memory_kb>17747912</granted_memory_kb>
    <used_memory_kb>17743872</used_memory_kb>
    <max_used_memory_kb>17743872</max_used_memory_kb>
    <ideal_memory_kb>85373512</ideal_memory_kb>
    <required_memory_kb>1352</required_memory_kb>
    <dop>8</dop>
    <query_cost>4791.8359</query_cost>
  </memory_grant>
  <resource_semaphore>
    <timeout_error_count>0</timeout_error_count>
    <target_memory_kb>69402424</target_memory_kb>
    <max_target_memory_kb>69402424</max_target_memory_kb>
    <total_memory_kb>69402424</total_memory_kb>
    <available_memory_kb>51654512</available_memory_kb>
    <granted_memory_kb>17747912</granted_memory_kb>
    <used_memory_kb>17679048</used_memory_kb>
    <grantee_count>1</grantee_count>
    <waiter_count>0</waiter_count>
  </resource_semaphore>
  <workload_group>
    <name>default</name>
    <request_max_memory_grant_percent>25</request_max_memory_grant_percent>
    <request_max_cpu_time_sec>0</request_max_cpu_time_sec>
    <request_memory_grant_timeout_sec>0</request_memory_grant_timeout_sec>
    <max_dop>0</max_dop>
  </workload_group>
  <resource_pool>
    <name>default</name>
    <min_memory_percent>0</min_memory_percent>
    <max_memory_percent>100</max_memory_percent>
    <min_cpu_percent>0</min_cpu_percent>
    <max_cpu_percent>100</max_cpu_percent>
  </resource_pool>
</memory_info>

For parallelism information:

You’ll wanna run like so:

EXEC sp_WhoIsActive
    @get_task_info = 2;

You’ll see this in the wait_info column, if your queries are hitting parallelism waits. Previously we only support CXPACKET, but now we support CXPACKET, CXCONSUMER, CXSYNC_PORT, and CXSYNC_CONSUMER.

This can be really helpful for tracking down issues in parallel queries.

For implicit transaction information:

You’ll wanna run like so:

EXEC sp_WhoIsActive
    @get_transaction_info = 1;

You’ll see a new column that will tell you if your god-awful JDBC driver is using the absolute mistake known as implicit transactions.

sp_WhoIsActive
bars

For additional info:

You’ll wanna run like so:

EXEC sp_WhoIsActive
    @get_additional_info = 1;

You’ll get back this column:

sp_WhoIsActive
clicky

If you click on it, you’ll get back this output, which now includes original login name, and context info.

<additional_info>
  <text_size>2147483647</text_size>
  <language>us_english</language>
  <date_format>mdy</date_format>
  <date_first>7</date_first>
  <quoted_identifier>ON</quoted_identifier>
  <arithabort>ON</arithabort>
  <ansi_null_dflt_on>ON</ansi_null_dflt_on>
  <ansi_defaults>OFF</ansi_defaults>
  <ansi_warnings>ON</ansi_warnings>
  <ansi_padding>ON</ansi_padding>
  <ansi_nulls>ON</ansi_nulls>
  <concat_null_yields_null>ON</concat_null_yields_null>
  <transaction_isolation_level>ReadCommitted</transaction_isolation_level>
  <lock_timeout>-1</lock_timeout>
  <deadlock_priority>0</deadlock_priority>
  <row_count>0</row_count>
  <command_type>SELECT</command_type>
  <sql_handle>0x020000004d3842022d406c17300f7e339224b8c5e0392bbb0000000000000000000000000000000000000000</sql_handle>
  <plan_handle>0x060008004d38420210a907e34d01000001000000000000000000000000000000000000000000000000000000</plan_handle>
  <statement_start_offset>122</statement_start_offset>
  <statement_end_offset>534</statement_end_offset>
  <host_process_id>16688</host_process_id>
  <group_id>2</group_id>
  <original_login_name>sa</original_login_name>
  <context_info>0x0000008a</context_info>
</additional_info>

Which is useful for people doing really weird stuff. Questionable stuff.

Again, you can grab the new version here!

Thanks for reading!

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.



7 thoughts on “sp_WhoIsActive Version 12 Is Out!

      1. Did you put “Copyright © 2019 Erik Darling Data” after each space, sir? If no, tabbies gonna come.

  1. Excellent.

    What about a column for “Multiple Plans”, Y or N.
    If it would add time to collect, maybe a @BringThePain parameter for such?

Comments are closed.