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)', 'varchar(131)'), plan_handle = w.additional_info.value('(//additional_info/plan_handle)', 'varchar(131)') FROM dbo.WhoIsActive AS w;
For the plan cache, you can use your favorite script. Mine is, of course, sp_BlitzCache.
You you can use the
@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
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!
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.