/// <summary>
/// Gets or creates an XEvent session with the given template per the IXEventSessionFactory contract
/// Also starts the session if it isn't currently running
/// </summary>
public IXEventSession GetOrCreateXEventSession(string template, ConnectionInfo connInfo)
{
string sessionName = "Profiler";
var sqlConnection = ConnectionService.OpenSqlConnection(connInfo);
SqlStoreConnection connection = new SqlStoreConnection(sqlConnection);
XEStore store = new XEStore(connection);
Session session = store.Sessions[sessionName];
// start the session if it isn't already running
if (session == null)
{
session = CreateSession(connection, sessionName);
}
if (session != null && !session.IsRunning)
{
session.Start();
}
// create xevent session wrapper
return(new XEventSession()
{
Session = session
});
}
private static Session CreateSession(SqlStoreConnection connection, string sessionName)
{
string createSessionSql =
@"
CREATE EVENT SESSION [Profiler] ON SERVER
ADD EVENT sqlserver.attention(
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.nt_username,sqlserver.query_hash,sqlserver.server_principal_name,sqlserver.session_id)
WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.existing_connection(SET collect_options_text=(1)
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_id)),
ADD EVENT sqlserver.login(SET collect_options_text=(1)
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_id)),
ADD EVENT sqlserver.logout(
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_id)),
ADD EVENT sqlserver.rpc_completed(
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.nt_username,sqlserver.query_hash,sqlserver.server_principal_name,sqlserver.session_id)
WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.nt_username,sqlserver.query_hash,sqlserver.server_principal_name,sqlserver.session_id)
WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sql_batch_starting(
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.nt_username,sqlserver.query_hash,sqlserver.server_principal_name,sqlserver.session_id)
WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0))))
ADD TARGET package0.ring_buffer(SET max_events_limit=(1000),max_memory=(51200))
WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)";
connection.ServerConnection.ExecuteNonQuery(createSessionSql);
XEStore store = new XEStore(connection);
return(store.Sessions[sessionName]);
}
public ObjectModel()
{
SqlConnectionStringBuilder conBuild = new SqlConnectionStringBuilder();
conBuild.DataSource = "(local)";
conBuild.InitialCatalog = "master";
conBuild.IntegratedSecurity = true;
SqlStoreConnection server = new SqlStoreConnection(new SqlConnection(conBuild.ConnectionString.ToString()));
xe = new XEStore(server);
}
/// <summary>
/// Gets an existing XEvent session or creates one if no matching session exists.
/// Also starts the session if it isn't currently running
/// </summary>
private static Session GetOrCreateSession(SqlStoreConnection connection, string sessionName)
{
XEStore store = new XEStore(connection);
Session session = store.Sessions["Profiler"];
// start the session if it isn't already running
if (session != null && !session.IsRunning)
{
session.Start();
}
return(session);
}
private static BaseXEStore CreateXEventStore(ConnectionInfo connInfo, SqlStoreConnection connection)
{
BaseXEStore store = null;
if (connInfo.IsCloud)
{
if (DatabaseUtils.IsSystemDatabaseConnection(connInfo.ConnectionDetails.DatabaseName))
{
throw new NotSupportedException(SR.AzureSystemDbProfilingError);
}
store = new DatabaseXEStore(connection, connInfo.ConnectionDetails.DatabaseName);
}
else
{
store = new XEStore(connection);
}
return(store);
}
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);
}
static void Main()
{
var connectionString = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
var c = new SqlStoreConnection(new SqlConnection(connectionString));
Console.WriteLine("Crreate the Session object");
Console.WriteLine("The most important properties are : the session name and the parent");
var se = new XEStore(c);
se.Sessions[SessionName]?.Drop();
var x = new Session()
{
Parent = new XEStore(c),
MaxMemory = 2048,
EventRetentionMode = Session.EventRetentionModeEnum.AllowMultipleEventLoss,
MaxDispatchLatency = 3,
MaxEventSize = 0,
MemoryPartitionMode = Session.MemoryPartitionModeEnum.None,
TrackCausality = false,
AutoStart = false,
Name = SessionName
};
Console.WriteLine("Create the target. Be sure that C:\\TMP exists on your computer!");
var t = new Target(x, "package0.event_file");
//--== Specify target fields
var tf = t.TargetFields["fileName"];
tf.Value = @"C:\Tmp\YourSession_Target.xel";
tf = t.TargetFields["max_file_size"];
tf.Value = 2;
tf = t.TargetFields["max_rollover_files"];
tf.Value = 2;
x.Targets.Add(t);
Console.WriteLine("Create the event");
var e = new Event(x, "sqlserver.sql_statement_completed");
Console.WriteLine("Create the Action");
var a = new Microsoft.SqlServer.Management.XEvent.Action(e, "sqlserver.sql_text");
Console.WriteLine("Predicate expression");
e.PredicateExpression = "( [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text], N'%SELECT%HAVING%') )";
e.Actions.Add(a);
x.Events.Add(e);
Console.WriteLine("Create the Session");
x.Create();
Console.WriteLine("Start the Session");
x.Start();
Console.WriteLine("Press any key to exit ...");
Console.ReadLine();
}
