I figured it out.
![enter image description here]()
In the <Symbols> element of the *.vsct file add
<GuidSymbol name="foo1" value="{33F13AC3-80BB-4ECB-85BC-225435603A5E}">
<IDSymbol name="foo2" value="0x0080"/>
</GuidSymbol>
Then change
<Parent guid="guidSHLMainMenu" id="IDM_VS_MENU_TOOLS"/>
to
<Parent guid="foo1" id="foo2"/>
as described here.
(You may also want to set the priority on the parent <Group element to 0x0001 if eager to see your extension higher up in the menu)
My mechanism for determining the magic Guid was initially quite laborious and is in the edit history for this Answer but a somewhat less laborious method would be to do set the registry key
[HKEY_CURRENT_USER\SOFTWARE\Microsoft\SQL Server Management Studio\18.0_IsoShell\General]
"EnableVSIPLogging"=dword:00000001
And then open SSMS, get an execution plan and Ctrl + Shift + Right click to bring up a message box as below (128 decimal is 0x80).
![enter image description here]()
Now what?
It wasn't immediately apparent to me how to do anything useful inside the menu click event so I think it is informative to add an example.
As a POC I tried to colour nodes with a subtree cost above a threshold and/or where the underlying execution plan operators met some potentially problematic conditions.
![enter image description here]()
For this I changed the code in the Execute method in the template generated code to
private void Execute(object sender, EventArgs e)
{
ThreadHelper.ThrowIfNotOnUIThread();
Dictionary<string, Color> coloringRules = new Dictionary<string, Color>
{
[@".//ns:Intrinsic[@FunctionName = ""GetRangeThroughConvert""]"] = Color.Yellow,
[@".//ns:Intrinsic[@FunctionName = ""GetRangeWithMismatchedTypes""]"] = Color.MediumPurple
};
ProofOfConcept.ColorInterestingNodes(coloringRules, costThreshold: 0.0032831);
}
this relies on the following nested class
private class ProofOfConcept
{
private const string ShowPlanControlTypeFullName = "Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ShowPlan.ShowPlanControl";
private const string GraphControlTypeFullName = "Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.GraphControl";
private const string ShowPlanNamespaceUri = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
private static readonly XNamespace ns = ShowPlanNamespaceUri;
[DllImport("user32.dll")]
public static extern IntPtr GetFocus();
public static void ColorInterestingNodes(Dictionary<string, Color> coloringRules, double costThreshold)
{
IntPtr focus = GetFocus();
Control activeControl = Control.FromChildHandle(focus);
Control rootControl = FindRootControl(activeControl);
List <Control> graphControls = new List<Control>();
FindAllDescendantControlsOfType(rootControl, graphControls, GraphControlTypeFullName);
XElement[] qpElements = GetShowPlanXMLQueryPlans(rootControl);
//TODO: More robust method of matching up the query plan XML elements with the display elements.
//e.g. "Use database;" statement will show a graph in "estimated" plan but not "actual" - and not have a QueryPlan element in the XML
if (graphControls.Count != qpElements.Count())
{
MessageBox.Show("Mismatch between graph control count (" + graphControls.Count + ") and query plan count (" + qpElements.Count() + "). Exiting");
return;
}
for (var index = 0; index < graphControls.Count; index++)
{
Control graphControl = graphControls[index];
XElement qpElement = qpElements[index];
Dictionary<int, Color> nodeBackgroundColors = GetNodeBackgroundColors(qpElement, coloringRules);
foreach (dynamic item in ((dynamic)graphControl).Nodes)
{
var nodeId = item.NodeOriginal["NodeId"] ?? -1;
if (item.NodeOriginal.Cost >= costThreshold)
{
item.TextColor = Color.Red;
item.BackgroundColor = Color.White;
item.UseBackgroundColor = true;
}
if (nodeBackgroundColors.TryGetValue(nodeId, out Color color))
{
item.BackgroundColor = color;
item.UseBackgroundColor = true;
}
}
graphControl.Refresh();
}
}
private static Dictionary<int, Color> GetNodeBackgroundColors(XElement queryPlan, Dictionary<string, Color> coloringRules)
{
var returnValue = new Dictionary<int, Color>();
NameTable nt = new NameTable();
XmlNamespaceManager namespaceManager = new XmlNamespaceManager(nt);
namespaceManager.AddNamespace("ns", "http://schemas.microsoft.com/sqlserver/2004/07/showplan");
foreach (var coloringRule in coloringRules)
{
var foundElements = queryPlan.XPathSelectElements(coloringRule.Key, namespaceManager);
foreach (var foundNode in foundElements)
{
var nodeId = foundNode.AncestorsAndSelf(ns + "RelOp").FirstOrDefault()?.Attribute("NodeId")?.Value;
if (nodeId != null)
{
returnValue[int.Parse(nodeId)] = coloringRule.Value;
}
}
}
return returnValue;
}
private static XElement[] GetShowPlanXMLQueryPlans(Control rootControl)
{
List<Control> showPlanControls = new List<Control>();
FindAllDescendantControlsOfType(rootControl, showPlanControls, ShowPlanControlTypeFullName);
Assembly sqlEditorsAssembly = Assembly.Load("SQLEditors");
Type showPlanControlType = sqlEditorsAssembly.GetType(ShowPlanControlTypeFullName);
MethodInfo GetShowPlanXmlMethod = showPlanControlType.GetMethod("GetShowPlanXml", BindingFlags.Instance | BindingFlags.NonPublic);
string xplan = GetShowPlanXmlMethod.Invoke(showPlanControls[0], null) as string;
XDocument doc = XDocument.Parse(xplan);
return doc.Descendants(ns + "QueryPlan").ToArray();
}
private static Control FindRootControl(Control control)
{
while (control.Parent != null)
control = control.Parent;
return control;
}
private static void FindAllDescendantControlsOfType(Control control, List<Control> graphControls, string typeFullName)
{
if (control.GetType().FullName == typeFullName)
graphControls.Add(control);
foreach (Control child in control.Controls)
FindAllDescendantControlsOfType(child, graphControls, typeFullName);
}
}
