Category: sp_WhoIsActive

Updates to sp_PressureDetector, sp_HumanEvents, sp_QuickieStore, And More!

Posted on by Erik Darling

Updates!

If you use any of my stored procedures, I’ve been doing a lot of work on them lately to improve performance, add features, and fix bugs.

There have been a lot of changes over the past few months, and the full list would be a little unwieldy.

  • sp_HumanEvents: Mostly adding useful columns the output and improving the XML processing
  • sp_HumanEventsBlockViewer: This is a newer procedure to examine a blocked process report extended event session, and not a lot of folks know about it. Try it out!
  • sp_PressureDetector: Added tempdb configuration and usage details, some more wait stats, additional CPU details, etc. A fan favorite.
  • sp_QuickieStore: Added the ability to analyze multiple databases, improve speed, and add more helpful details to the output
  • sp_WhoIsActive Logging: Added a procedure to create a view over all of the daily logging tables

If you use any of these, get those updates going. If you’re not using them: what are you waiting for? They’re free!

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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

What To Do When Your Query Runs For Too Long To Get An Actual Execution Plan

Posted on by Erik Darling

Willfully

While I do enjoy answering questions about SQL Server performance, it can be frustrating trying to get the right set of information.

One thing that comes up pretty often when you ask for an actual execution plan, is that the query “never finishes” and one can’t be captured.

Good news! There are ways for you to get execution plans in progress with some of the actual query plan elements inside.

You don’t have to let the query run to completion, but generally if you give it a few minutes you can capture where things are going wrong.

Longer is usually better, but I understand that some queries are real crushers, and cause big server problems.

Option None: Live Query Plans

The problem with Live Query Plans is that they’re really unreliable. Many times I’ve tried to use them and the Live Plan never shows up, and worse I’ll be unable to close the SSMS tab without killing the process in Task Manager.

When it does work, it can be sort of confusing. Here’s a completed query with Live Plans enabled:

SQL Server Query Plans
nope.

It took about 49 seconds, but… What took 49 seconds? Repartition Streams? Clustered Index Scan? Something else?

Here’s another really confusing one:

SQL Server Query Plan
means of deduction

Apparently everything took three minutes and thirty six seconds.

Good to know.

Screw these things.

Option One: Get Plans + sp_WhoIsActive

Good ol’ sp_WhoIsActive will check to see if you have the right bits and views in your SQL Server, and return in-progress actual plans to you.

To do that, it looks at dm_exec_query_statistics_xml. That’s SQL Server 2016 and up, for those of you playing along at home. Hopefully you have it installed at home, because you sure don’t at work.

But anyway, if you enable either Actual Execution plans, OR Live Query Plans, and then run sp_WhoIsActive in another window, you’ll get much more sensible plans back. Usually.

SQL Server Query Plan
udderly

This accurately shows where time is spent in the query, which is largely in the scan of the Posts table.

Where There’s Still A Prioblem

Where things fall apart is still when a spool is built. If you can follow along a bit…

SQL Server Query Plan
not even almost
  • The top query plan shows time accurately distributed amongst operators, from the Actual Plan run to completion
  • The bottom query plan shows time quite inaccurately, getting the plan from dm_exec_query_statistics_xml after the query had been running for three minutes

I guess the bottom line is if you see an Eager Index Spool in your query plan, you should fix that before asking any questions.

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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

SQL Server Community Tools: The Wrap Up And Combined Link

Posted on by Erik Darling

I am a heading

Over the past month (plus or minus a couple days), I’ve shown you in a series of quick posts how I use different SQL Server Community Tools that are free and open source to troubleshoot SQL Server issues.

Here’s the full list of posts.

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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

SQL Server Community Tools: How I Log sp_WhoIsActive To A Table

Posted on by Erik Darling

Been There

A lot of the code to do this is in the sp_WhoIsActive documentation.

But since I have to do things a little bit differently, I decided to open source the way I do things.

You can get the full script here: sp_WhoIsActive Logging

That’s the full script to the SQL Server Agent job I use.

The big tweak that I make to things is to create a new table for every day of logging. I dislike the approach of logging everything to one big table and automatically managing the retention for two reasons:

  • You might delete something important
  • It sucks querying giant tables full of query logging details

I sort of stopped short of automating creating date constraints on each table and creating a view to union them all together. I just don’t need to do that often enough.

The full command I use for logging is this:

EXEC sp_WhoIsActive
    @get_transaction_info = 1,
    @get_outer_command = 1,
    @get_plans = 1,
    @get_task_info = 2,
    @get_additional_info = 1,
    @find_block_leaders = 1,
    @get_memory_info = 1;

Which means you do have to be on the latest version of sp_WhoIsActive. If you haven’t updated in a while, uh… I promise it’s a fast and painless process.

Just hit F5.

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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

SQL Server Community Tools: Filtering sp_WhoIsActive Results

Posted on by Erik Darling

Oh Baby You

On really busy SQL Servers, or on SQL Servers where some ninny activated Service Broker on hundreds of databases so you have hundreds of sessions constantly sitting there waiting for conversations, being able to filter out stuff you don’t want to look at is an absolute killer feature.

And boy can sp_WhoIsActive do that really well.

You can filter in or out:

  • session id
  • program name
  • database name
  • login name
  • host name

Most of the uses I have for these filters is to watch activity for a specific session id or for a specific database.

In a moderate bit of fairness to Service Broker, I’ve only had to screen out hundreds of sessions from it a few dozen times.

Innies

To only include certain sessions in the output, do something like this:

EXEC sp_WhoIsActive
    @filter = '138',               
    @filter_type = 'session' /*session, program, database, login, host*/;

EXEC sp_WhoIsActive
    @filter = 'StackOverflow',               
    @filter_type = 'database' /*session, program, database, login, host*/;

What’s really cool is that you can use wildcards in your filters (except session) to do something like:

EXEC sp_WhoIsActive
    @filter = 'CrappyApplication%',               
    @filter_type = 'program' /*session, program, database, login, host*/;

And of course, you can filter stuff out, too.

Outies

To do that, you just need to use the not filters:

EXEC sp_WhoIsActive
    @not_filter = '138',               
    @not_filter_type = 'session' /*session, program, database, login, host*/;

EXEC sp_WhoIsActive
    @not_filter = 'StackOverflow',               
    @not_filter_type = 'database' /*session, program, database, login, host*/;

EXEC sp_WhoIsActive
    @not_filter = 'CrappyApplication%',               
    @not_filter_type = 'program' /*session, program, database, login, host*/;

Which, now that I’ve copy and pasted it in, I can’t think of too many reasons to ignore a single session id, but whatever.

This is a great way to filter out hundreds of Service Broker sessions that pollute the results because some ninny activated it in hundreds of databases, though.

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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

SQL Server Community Tools: Changing The Output Of sp_WhoIsActive In Different Ways

Posted on by Erik Darling

But Why?

As a procedure, sp_WhoIsActive is pretty perfect. It’s not all things to all people, but it does what it’s supposed to do really well.

One thing I really like about it is that you can change a couple things about the way it returns data to you (or to a logging table) in different ways:

  • The set of columns
  • The sorting of columns

There are definitely good uses for these options, especially when you’re beyond the “what the hell is going on here?” phase and on to the “troubleshooting a specific problem” phase.

Just as a couple examples, if you were specifically troubleshooting:

  • tempdb issues, you’d wanna sort and focus on tempdb related columns
  • blocking issues, you’d wanna sort and focus on blocking related columns

Let’s take a look at how to do that.

Focus On tempdb

If you want to focus on just what’s using a lot of tempdb without any other noise, and you want to find out what’s using the most tempdb right at the top, you can do this:

EXEC sp_WhoIsActive
    @sort_order = '[tempdb_current] DESC',
    @get_plans = 1,
    @output_column_list = '[start_time][session_id][sql_text][query_plan][wait_info][temp%]';

It will:

  • Sort the results by what currently has the most stuff in tempdb
  • Only output columns related to tempdb use with some other identifying details

What’s nice about this is that not only does it help you totally focus right in on what you care about.

And if you’re taking screenshots to document what hellish things are happening, you don’t have to spend a bunch of time editing them to cut distractions out.

Focus On Blocking

This is one I use a whole bunch when clients are hitting blocking issues.

EXEC sp_WhoIsActive
    @sort_order = '[blocked_session_count] DESC',
    @find_block_leaders = 1,  
    @get_plans = 1,
    @get_task_info = 2,
    @get_additional_info = 1,
    @output_column_list = '[start_time][session_id][sql_text][query_plan][wait_info][block%][additional_info]';

Here’s what it does:

  • Finds which queries have the most blocked sessions under them
  • Sort the results by which sessions are doing the most blocking
  • Gets additional information about the session, like isolation level, locks, etc.
  • Only outputs columns you need to see relevant blocking details

If you don’t have an extended event session set up to capture blocking and stuff like that, this is a great way to get a lot of the same information on the fly.

What You Do

There are probably other variations on this that would be useful to you at various times. These are just to get you started.

You can sort by any one of these columns:

session_id, physical_io, reads, physical_reads, writes, tempdb_allocations,

tempdb_current, CPU, context_switches, used_memory, physical_io_delta, reads_delta,

physical_reads_delta, writes_delta, tempdb_allocations_delta, tempdb_current_delta,

CPU_delta, context_switches_delta, used_memory_delta, tasks, tran_start_time,

open_tran_count, blocking_session_id, blocked_session_count, percent_complete,

host_name, login_name, database_name, start_time, login_time, program_name

Meaning, if you were troubleshooting high CPU, it might make sense to sort by that descending or something.

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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

SQL Server Community Tools: Using sp_WhoIsActive To Track Down Implicit Transactions

Posted on by Erik Darling

In The Annals

When people talk about bad ideas in SQL Server, implicit transactions are pretty high on the list.

I’ve talked about why in the past, but up until recently sp_WhoIsActive didn’t do much to surface queries using them.

By “not much” I mean “nothing at all”. Which isn’t to knock the procedure at all; it hasn’t quite lived long enough to become a villain.

But anyway, you should avoid implicit transactions as much as possible. They most often show up in the Microsoft JDBC driver queries that only people who hate you use.

When I first started to realize how bad they are, I wrote a bunch of checks into the Blitz scripts (I’ll cover those next week) that check for them.

I also opened an issue to add the check to sp_WhoIsActive, because it’s rather popular, I hear.

In The Actions

To see when queries are using implicit transactions, you need to do this:

EXEC sp_WhoIsActive
    @get_transaction_info = 1;

Which, I mean, seems like a rational design choice. Want information about implicit transactions? Get transaction info!

In the results, you’ll see this helpful column that will tell you if something is setting the implicit transactions property to true for a session.

SQL Server Implicit Transactions
please don’t

If you see this, burn the application down. It’s time to start fresh.

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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

SQL Server Community Tools: How To Use sp_WhoIsActive To Get Memory Grant Information

Posted on by Erik Darling

Woah woah woah

Up until now, I’ve written about scripts that I wrote from scratch. But they’re far from the only tools I use in my every day life.

A big one is, and always has been, sp_WhoIsActive. It’s great. You hit F5, and it tells you everything running on your server.

What more could you ask for?

In the past, I’ve written a bit about it:

And get this! It’s still under active development. It’s slow, but it happens. I’m going to spend the first couple posts this week talking about cool new features in the most recent release that I’ve been using lately.

Get It

A recent addition to sp_WhoIsActive in version 12 is collecting a bunch of memory grant information for running queries.

To get the extra details via a nifty clickable XML column, use this:

EXEC sp_WhoIsActive
    @get_memory_info = 1;

You’ll get some new top-level columns in the results that look like this:

SQL Server Memory Grants
kaboom

But you’ll also get an XML clickable column called memory_info that has much more detail in it.

When a query is running normally, the first XML fragment will look like this:

<memory_grant>
  <request_time>2022-08-25T19:05:26.663</request_time>
  <grant_time>2022-08-25T19:05:26.663</grant_time>
  <requested_memory_kb>5927872</requested_memory_kb>
  <granted_memory_kb>5927872</granted_memory_kb>
  <used_memory_kb>1833160</used_memory_kb>
  <max_used_memory_kb>1833160</max_used_memory_kb>
  <ideal_memory_kb>9573888</ideal_memory_kb>
  <required_memory_kb>4864</required_memory_kb>
  <dop>8</dop>
  <query_cost>3380.9998</query_cost>
</memory_grant>

When a query is running (or not running, depending on how you look at it) abnormally, and waiting on RESOURCE_SEMAPHORE, it will look like this:

<memory_grant>
  <request_time>2022-08-25T19:05:27.030</request_time>
  <wait_time_ms>1656</wait_time_ms>
  <requested_memory_kb>5927872</requested_memory_kb>
  <ideal_memory_kb>9573888</ideal_memory_kb>
  <required_memory_kb>4864</required_memory_kb>
  <queue_id>8</queue_id>
  <wait_order>0</wait_order>
  <is_next_candidate>1</is_next_candidate>
  <dop>8</dop>
  <query_cost>3380.9998</query_cost>
</memory_grant>

This can be really valuable information to get, similar to what sp_PressureDetector will give you.

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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Correlating Data From sp_WhoIsActive to Query Store Or The Plan Cache

Posted on by Erik Darling

sp_QuickiePost

If you’re the type of person who logs sp_WhoIsActive to a table to capture executing queries, you may want to find some additional details about the statements that end up there.

Out of the box, it’s arduous, tedious, and cumbersome to click around on a bunch of columns and grab handles and hashes and blah blah.

Now, these two queries depend on you grabbing a couple specific columns in your output. If you’re not getting these, you’re kinda screwed:

From query plans, you can get the plan handle and plan hash:

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
   session_id,
   query_plan,
   additional_info,
   query_hash = 
       q.n.value('@QueryHash', 'varchar(18)'),
   query_plan_hash = 
       q.n.value('@QueryPlanHash', 'varchar(18)')
FROM dbo.WhoIsActive AS w
CROSS APPLY w.query_plan.nodes('//StmtSimple') AS q(n);

From additional info, you can get the SQL Handle and Plan Handle:

SELECT
  session_id,
  query_plan,
  additional_info,
  sql_handle =
      w.additional_info.value('(//additional_info/sql_handle)[1]', 'varchar(131)'),
  plan_handle = 
      w.additional_info.value('(//additional_info/plan_handle)[1]', 'varchar(131)')
FROM dbo.WhoIsActive AS w;

Causation

For the plan cache, you can use your favorite script. Mine is, of course, sp_BlitzCache.

You you can use the @OnlyQueryHashes or @OnlySqlHandles parameters to filter down to queries you’re interested in.

For Query Store, you can use my script sp_QuickieStore to do the same thing.

It has parameters for @include_query_hashes, @include_plan_hashes or @include_sql_handles.

You might want to add some other filtering or sorting to the queries up there to find what you’re interested in, but this should get you started.

I couldn’t find a quick or easy way to combine the two queries, since we’re dealing with two different columns of XML data, and the query plan XML needs a little special treatment to be queried.

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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

sp_WhoIsActive Version 12 Is Out!

Posted on by Erik Darling

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.