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