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.

Related Posts