public static bool TryFixByHandle(SpyMonitor.SpyOptions options, ShowplanEvent showplanEvent)
{
var planByHandle = GetPlanByHandle(options, showplanEvent.PlanHandle);
if (planByHandle == null)
{
return(false);
}
showplanEvent.Showplan = ApplyBetterStatement(showplanEvent.ShowplanXml.RawString, planByHandle, out string statementText);
showplanEvent.SqlStatement = statementText;
return(true);
}
private static string GetPlanByHandle(SpyMonitor.SpyOptions options, byte[] planHandle)
{
var handleHex = BitConverter.ToString(planHandle).Replace("-", "");
Log.Logger.Verbose("Looking up better plan for query handle {handle}", handleHex);
if (Cache[handleHex] is string cached)
{
Log.Logger.Verbose("Found query handle {handle} in cache", handleHex);
return(cached);
}
const string findByPlanHandle = "select query_plan from sys.dm_exec_query_plan(@handle)";
var connString = ConnectionStringBuilder.BuildWithAppName(
options.Database,
options.Server,
"ShowplanSpy",
options.UserName,
options.Password);
using (var conn = new SqlConnection(connString))
{
conn.Open();
using (var command = new SqlCommand(findByPlanHandle, conn))
{
command.Parameters.AddWithValue("handle", planHandle);
var newPlan = command.ExecuteScalar();
if (!(newPlan is string showPlanXml))
{
return(null);
}
Log.Logger.Verbose("Found better plan for {handle} in sys tables", handleHex);
Cache.Add(handleHex, showPlanXml, DateTimeOffset.Now.AddMinutes(5));
return(showPlanXml);
}
}
}