/// <summary>
/// Creates and starts an XEvent session with the given name and create statement per the IXEventSessionFactory contract
/// </summary>
public IXEventSession CreateXEventSession(string createStatement, string sessionName, ConnectionInfo connInfo)
{
var sqlConnection = ConnectionService.OpenSqlConnection(connInfo);
SqlStoreConnection connection = new SqlStoreConnection(sqlConnection);
BaseXEStore store = CreateXEventStore(connInfo, connection);
Session session = store.Sessions[sessionName];
// session shouldn't already exist
if (session != null)
{
throw new Exception(SR.SessionAlreadyExists(sessionName));
}
var statement = createStatement.Replace("{sessionName}", sessionName);
connection.ServerConnection.ExecuteNonQuery(statement);
store.Refresh();
session = store.Sessions[sessionName];
if (session == null)
{
throw new Exception(SR.SessionNotFound);
}
if (!session.IsRunning)
{
session.Start();
}
// create xevent session wrapper
return(new XEventSession()
{
Session = store.Sessions[sessionName]
});
}
/// <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);
BaseXEStore store = CreateXEventStore(connInfo, connection);
Session session = store.Sessions[sessionName];
// start the session if it isn't already running
if (session == null)
{
session = CreateSession(connInfo, connection, sessionName);
}
if (session != null && !session.IsRunning)
{
session.Start();
}
// create xevent session wrapper
return(new XEventSession()
{
Session = session
});
}
private static Session CreateSession(ConnectionInfo connInfo, 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)";
string createAzureSessionSql =
@"
CREATE EVENT SESSION [Profiler] ON DATABASE
ADD EVENT sqlserver.attention(
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.username,sqlserver.query_hash,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.username,sqlserver.session_id)),
ADD EVENT sqlserver.login(SET collect_options_text=(1)
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.username,sqlserver.session_id)),
ADD EVENT sqlserver.logout(
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.username,sqlserver.session_id)),
ADD EVENT sqlserver.rpc_completed(
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.username,sqlserver.query_hash,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.username,sqlserver.query_hash,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.username,sqlserver.query_hash,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)";
string createStatement = connInfo.IsCloud ? createAzureSessionSql : createSessionSql;
connection.ServerConnection.ExecuteNonQuery(createStatement);
BaseXEStore store = CreateXEventStore(connInfo, connection);
return(store.Sessions[sessionName]);
}
private static void CleanUpExistingSessions(BaseXEStore store)
{
Log.Logger.Information("Cleaning existing sessions");
var sessionsToClean = store.Sessions.Where(i => i.Name.StartsWith(XePlanNamePrefix)).ToList();
foreach (var ses in sessionsToClean)
{
Log.Logger.Verbose("Deleting session {session}.", ses.Name);
SessionCleanup(ses);
}
}
/// <summary>
/// Gets a list of all running XEvent Sessions
/// </summary>
/// <returns>
/// A list of the names of all running XEvent sessions
/// </returns>
internal List <string> GetXEventSessionList(string ownerUri, ConnectionInfo connInfo)
{
var sqlConnection = ConnectionService.OpenSqlConnection(connInfo);
SqlStoreConnection connection = new SqlStoreConnection(sqlConnection);
BaseXEStore store = CreateXEventStore(connInfo, connection);
// get session names from the session list
List <string> results = store.Sessions.Aggregate(new List <string>(), (result, next) => {
result.Add(next.Name);
return(result);
});
return(results);
}
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);
}
/// <summary>
/// Gets an XEvent session with the given name per the IXEventSessionFactory contract
/// Also starts the session if it isn't currently running
/// </summary>
public IXEventSession GetXEventSession(string sessionName, ConnectionInfo connInfo)
{
var sqlConnection = ConnectionService.OpenSqlConnection(connInfo);
SqlStoreConnection connection = new SqlStoreConnection(sqlConnection);
BaseXEStore store = CreateXEventStore(connInfo, connection);
Session session = store.Sessions[sessionName];
// start the session if it isn't already running
if (session == null)
{
throw new Exception(SR.SessionNotFound);
}
if (session != null && !session.IsRunning)
{
session.Start();
}
// create xevent session wrapper
return(new XEventSession()
{
Session = session
});
}
