public void CreateEventSession() { // Drop the session if it exists try { xe.Sessions["PS_DemoAPI"].Drop(); } catch (Exception ex) { // Session didn't exist so no issue } Session ses = xe.CreateSession("PS_DemoAPI"); // Create an array of event names to add to the session string[] events = { "sqlserver.rpc_completed", "sqlserver.sp_statement_completed", "sqlserver.sql_batch_completed", "sqlserver.sql_statement_completed" }; // Create an array of action names to add to the events string[] actions = { "sqlserver.client_app_name", "sqlserver.client_hostname", "sqlserver.database_id", "sqlserver.nt_username", "sqlserver.server_principal_name", "sqlserver.session_id" }; // Add the events to to the session foreach (string event_name in events) { Event evnt = ses.AddEvent(event_name); // Add the actions to this event foreach (string action in actions) { evnt.AddAction(action); } // Add a predicate to the event evnt.PredicateExpression = @"sqlserver.session_id > 50"; } // Add the ring_buffer target to the event session Target target = ses.AddTarget("package0.ring_buffer"); // Generate a DDL Script to output ISfcScript scr = ses.ScriptCreate(); string ses_DDL = scr.ToString(); Console.Write(ses_DDL); Console.WriteLine(); Console.WriteLine("Event Session created, press any key to exit!\n"); Console.ReadKey(); }
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); }