Thursday, August 16, 2018

[Query Plan] Getting out the query execution plan when SP_whoisactive gives you a big fat NULL.

Ran into a problem where I couldn't get the execution plan for a query that was taking forever. 

Found a smarter man than me who'd build a powershell cmdlet to capture it and save it to disk.
http://www.patrickkeisler.com/2013/09/the-case-of-null-queryplan.html

Here's my wrapper for it:

$SPname = "yourspnamehere"
$servername = "yourserverhere"


. c:\powershell_scripts\invoke-sqlcmd2.ps1

$query = @"
SELECT master.dbo.fn_varbintohexstr(plan_handle) as plan_handle
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE 
text LIKE '%$SPname%'
AND objtype = 'Proc'
ORDER BY usecounts DESC;
"@

$queryresults = invoke-sqlcmd2 -serverinstance $servername -query $query
$planhandle = $queryresults.plan_handle
. z:\mydocs\get-queryplan.ps1 -SqlInstance "ods_scd" -planhandle "$planhandle"

2 comments:

John Zabroski said...

This might also work if you have some control over the query:
https://www.brentozar.com/archive/2018/10/the-new-lightweight-query-plan-profile-hint/

bourgon said...

I hadn't seen that post, and changing a hint like that for us would require a deploy, but I like! Thanks, much appreciated!