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);
}
}
}
public static async Task Spy(Action <ShowplanEvent> eventFunc, SpyOptions options, CancellationToken token)
{
await Task.Run(async() =>
{
var sessionName = $"{XePlanNamePrefix}-{Environment.MachineName}-{Guid.NewGuid()}";
var connectionString = ConnectionStringBuilder.Build(
"master",
options.Server,
options.UserName,
options.Password);
var store = new XEStore(new SqlStoreConnection(new SqlConnection(connectionString)));
if (options.CleanOnStart)
{
CleanUpExistingSessions(store);
}
var existingCount = store.Sessions.Count(s => s.Name.StartsWith(XePlanNamePrefix));
if (existingCount > 0)
{
Log.Logger.Warning("Found {count} existing plans that might need cleanup. Run with -c option to remove them.", existingCount);
}
var filterExpression = FilterBuilder.Build(options.Database, options.AppName);
var session = store.CreateSession(sessionName);
session.MaxDispatchLatency = 1;
session.AutoStart = false;
var showPlanEvent = session.AddEvent("sqlserver.query_post_execution_showplan");
showPlanEvent.PredicateExpression = filterExpression;
showPlanEvent.AddAction("sqlserver.sql_text");
showPlanEvent.AddAction("sqlserver.query_hash");
showPlanEvent.AddAction("sqlserver.query_plan_hash");
showPlanEvent.AddAction("sqlserver.plan_handle");
try
{
Log.Logger.Verbose("Creating new session {session}", session.Name);
session.Create();
Log.Logger.Verbose("Starting new session {session}", session.Name);
session.Start();
}
catch (Exception e)
{
Log.Logger.Fatal("Unable to create monitoring session", e);
throw;
}
Log.Logger.Verbose("Session {session} started", session.Name);
// if the task gets canceled then we need to break out of the loop and clean up the session
// we're checking in the loop for the cancellation but that will only hit if an event is triggered
// so this lets us be a bit more aggressive about quitting and cleaning up after ourselves
token.Register(() => SessionCleanup(session));
try
{
using (var eventStream = new QueryableXEventData(connectionString, sessionName,
EventStreamSourceOptions.EventStream, EventStreamCacheOptions.DoNotCache))
{
Log.Logger.Verbose("Watching new session");
foreach (var evt in eventStream)
{
if (token.IsCancellationRequested)
{
Log.Logger.Verbose("Cancelling sql spy task");
break;
}
try
{
var sqlEvent = new ShowplanEvent
{
ShowplanXml = (XMLData)evt.Fields["showplan_xml"].Value,
Duration = (ulong)evt.Fields["duration"].Value,
EstimatedCost = (int)evt.Fields["estimated_cost"].Value,
EstimatedRows = (int)evt.Fields["estimated_rows"].Value,
QueryHash = (ulong)evt.Actions["query_hash"].Value,
QueryPlanHash = (ulong)evt.Actions["query_plan_hash"].Value,
SqlStatement = (string)evt.Actions["sql_text"].Value,
PlanHandle = (byte[])evt.Actions["plan_handle"].Value
};
eventFunc(sqlEvent);
}
catch (Exception e)
{
Log.Logger.Error("Error creating event", e);
}
}
}
}
catch (Exception e)
{
if (!token.IsCancellationRequested)
{
Log.Logger.Error("Unknown error while monitoring events", e);
throw;
}
}
await Task.Delay(0, token);
}, token);
}
