How can I create an SSMS extension that opens from the execution plan window?

Question

I would like to create an SQL Server Management Studio v18 extension that opens from the execution plan window. I believe that this is technically possible because there is a third party tool that already does this:

So far, I've been able to create a basic extension in SSMS v18 using the guide located here. I was also able to move the placement of the button by referencing the IDs in the documentation. However, I can't figure out how to modify the .vsct file in order to move my button to be within the execution plan window.

How can I create an SSMS extension that opens from the execution plan window?

Answer 1

I figured it out.

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).

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.

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);
    }

}