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!

Someone JUST asked me yesterday if this has been updated recently. Thank you for sharing!

— Erin Stellato (@erinstellato) November 10, 2021

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:

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.

For additional info:

You’ll wanna run like so:

EXEC sp_WhoIsActive
    @get_additional_info = 1;

You’ll get back this column:

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.