public static SqlXml xfnCheckPlanXml(
[SqlFacet(MaxSize = 512, IsNullable = true)]
SqlString validationConfig,
[SqlFacet(MaxSize = 128, IsNullable = false)]
SqlString dbName,
[SqlFacet(MaxSize = -1, IsNullable = true)]
SqlXml planXml
)
{
if (planXml.IsNull)
{
return(SqlXml.Null);
}
var xml = planXml.Value;
string config = null;
if (!validationConfig.IsNull)
{
config = validationConfig.Value;
}
var planValidator = new PlanValidator(config);
var results = planValidator.ValidateSqlPlan(xml).ToList();
// ReSharper disable once SimplifyLinqExpression
if (!results.Any(r => r.Category != PlanCategory.Trace))
{
return(SqlXml.Null);
}
var resultElement = planValidator.GenerateResultsElement(results);
resultElement.Add(new XAttribute("dbName", dbName.Value));
SqlXml ret;
using (var reader = new StringReader(resultElement.ToString()))
using (var xmlreader = new XmlTextReader(reader))
{
ret = new SqlXml(xmlreader);
}
return(ret);
}
public void ShowAnalysis(ref bool Handled)
{
//do NOT dispose of these controls. As we are attaching to existing controls. If you do, you will make the execution plan unusable in the ssms ui
var focus = NativeMethods.GetFocus();
var control = Control.FromChildHandle(focus);
var activeWindowCaption = this.ActiveWindowCaption;
var parentControl = _activeScriptEditorControl == null
? FindParentControl(control, _showPlanControl)
: FindControlRecursive(_activeScriptEditorControl, _showPlanControl);
if (parentControl == null)
{
MessageBox.Show(Resources.PlanAnalysis_ShowAnalysis_Could_not_find_the_parent_control, _ProductName);
return;
}
var planXml = GetPlanXml(_showPlanControl, _getShowPlanXml, _dataBindings, _getGraphPanel, parentControl);
try
{
var directory = Path.Combine(Path.GetTempPath(), "PlanAnalysis");
Directory.CreateDirectory(directory);
var planValidator = new PlanValidator();
PlanTransformer.ExtractResources(directory);
var results = planValidator.ValidateSqlPlan(planXml);
var planAnalysisXml = planValidator.UpdatePlanWithAnalysis(planXml, results);
var fileName = Regex.Replace(activeWindowCaption, "[^A-Za-z0-9_-]", "");
fileName = Path.Combine(directory, fileName + ".analysis.html");
File.WriteAllText(fileName, PlanTransformer.TransformHtmlPage(activeWindowCaption, planAnalysisXml));
Process.Start(fileName);
Handled = true;
}
catch (Exception ex)
{
MessageBox.Show(Resources.PlanAnalysis_ShowAnalysis_Exception_generating_plan_analysis + ex, _ProductName);
}
}
static void Main(string[] args)
{
string dir;
var openDir = false;
var parser = new Parser((settings) =>
{
settings.CaseSensitive = false;
settings.HelpWriter = Console.Error;
settings.IgnoreUnknownArguments = true;
});
var options = new CmdLineOptions();
if (!parser.ParseArguments(args, options))
{
Console.WriteLine(HelpText.AutoBuild(options).ToString());
return;
}
dir = options.PlanDirectory.Trim('\\', '"');
if (!Directory.Exists(dir))
{
Console.WriteLine(Resources.Program_Main_Could_not_find_the_supplied_directory____0, dir);
Console.WriteLine(Resources.Program_Main_Continue);
Console.ReadKey(false);
return;
}
var files = Directory.EnumerateFiles(dir, "*.sqlplan").ToList();
if (files.Count == 0)
{
Console.WriteLine(Resources.Program_Main_No_execution_plan_files_found_at___0, dir);
Console.WriteLine(Resources.Program_Main_Continue);
Console.ReadKey(false);
return;
}
try
{
Console.WriteLine(Resources.Program_Main_Validating);
var sw = Stopwatch.StartNew();
var planValidator = new PlanValidator();
var writeXml = options.OutPutTypes == OutPutType.Both || options.OutPutTypes == OutPutType.Xml;
var writeHtml = options.OutPutTypes == OutPutType.Both || options.OutPutTypes == OutPutType.Html;
if (writeHtml)
{
PlanTransformer.ExtractResources(dir);
}
foreach (var file in files)
{
var planXml = File.ReadAllText(file);
var results = planValidator.ValidateSqlPlan(planXml).ToList();
var xmlFile = $"{file}.analysis.xml";
var htmlFile = $"{file}.analysis.html";
//first delete any old files in case this is a re-run
if (File.Exists(xmlFile))
{
File.Delete(xmlFile);
}
if (File.Exists(htmlFile))
{
File.Delete(htmlFile);
}
var hasChecks = results.Any(r => r.Result != PlanResult.StatementCost && r.Category != PlanCategory.Trace);
if (!hasChecks)
{
Console.WriteLine($"No broken checks for: {Path.GetFileName(file)}");
continue;
}
if (writeXml)
{
var resultElement = planValidator.GenerateResultsElement(results);
File.WriteAllText(xmlFile, resultElement.ToString());
}
if (writeHtml)
{
var planAnalysisXml = planValidator.UpdatePlanWithAnalysis(planXml, results);
File.WriteAllText(htmlFile, PlanTransformer.TransformHtmlPage(file, planAnalysisXml));
}
Console.WriteLine(Resources.Program_Main_Validated_File, Path.GetFileName(file));
}
sw.Stop();
Console.WriteLine(Resources.Program_Main_Validated, files.Count(), sw.Elapsed.TotalSeconds);
//they browsed to the directory, so lets open it for them
if (openDir)
{
Process.Start(dir);
}
}
catch (Exception ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(ex);
Console.WriteLine();
Console.ResetColor();
Console.WriteLine(Resources.Program_Main_Continue);
Console.ReadKey(false);
}
if (!Debugger.IsAttached)
{
return;
}
Console.WriteLine(Resources.Program_Main_Continue);
Console.ReadKey(false);
}