private void InsertEvent(WorkloadEvent evnt)
{
if (!(evnt is ExecutionWorkloadEvent))
{
return;
}
ExecutionWorkloadEvent evt = (ExecutionWorkloadEvent)evnt;
string sql = @"
INSERT INTO Events (
event_sequence,
start_time,
client_app_name,
client_host_name,
database_name,
server_principal_name,
session_id,
sql_text
)
VALUES (
$event_sequence,
$start_time,
$client_app_name,
$client_host_name,
$database_name,
$server_principal_name,
$session_id,
$sql_text
);";
if (conn == null)
{
conn = new SQLiteConnection(connectionString);
conn.Open();
}
if (cmd == null)
{
cmd = new SQLiteCommand(sql, conn);
}
cmd.Parameters.AddWithValue("$event_sequence", event_sequence++);
cmd.Parameters.AddWithValue("$start_time", evt.StartTime);
cmd.Parameters.AddWithValue("$client_app_name", evt.ApplicationName);
cmd.Parameters.AddWithValue("$client_host_name", evt.HostName);
cmd.Parameters.AddWithValue("$database_name", evt.DatabaseName);
cmd.Parameters.AddWithValue("$server_principal_name", evt.LoginName);
cmd.Parameters.AddWithValue("$session_id", evt.SPID);
cmd.Parameters.AddWithValue("$sql_text", evt.Text);
cmd.ExecuteNonQuery();
}
private WorkloadEvent ReadEvent(SQLiteDataReader reader)
{
WorkloadEvent.EventType type = (WorkloadEvent.EventType)reader.GetInt32(reader.GetOrdinal("event_type"));
long row_id = reader.GetInt64(reader.GetOrdinal("row_id"));
try
{
switch (type)
{
case WorkloadEvent.EventType.PerformanceCounter:
CounterWorkloadEvent cr = new CounterWorkloadEvent();
cr.StartTime = reader.GetDateTime(reader.GetOrdinal("start_time"));
ReadCounters(row_id, cr);
return(cr);
case WorkloadEvent.EventType.WAIT_stats:
WaitStatsWorkloadEvent wr = new WaitStatsWorkloadEvent();
wr.StartTime = reader.GetDateTime(reader.GetOrdinal("start_time"));
wr.Type = type;
return(wr);
case WorkloadEvent.EventType.Error:
ErrorWorkloadEvent er = new ErrorWorkloadEvent();
er.StartTime = reader.GetDateTime(reader.GetOrdinal("start_time"));
er.Type = type;
er.Text = GetString(reader, "sql_text");
return(er);
default:
ExecutionWorkloadEvent result = new ExecutionWorkloadEvent();
result.EventSequence = GetInt64(reader, "event_sequence");
result.ApplicationName = GetString(reader, "client_app_name");
result.StartTime = reader.GetDateTime(reader.GetOrdinal("start_time"));
result.HostName = GetString(reader, "client_host_name");
result.DatabaseName = GetString(reader, "database_name");
result.LoginName = GetString(reader, "server_principal_name");
result.SPID = reader.GetInt32(reader.GetOrdinal("session_id"));
result.Text = GetString(reader, "sql_text");
result.CPU = GetInt64(reader, "cpu");
result.Duration = GetInt64(reader, "duration");
result.Reads = GetInt64(reader, "reads");
result.Writes = GetInt64(reader, "writes");
result.Type = type;
return(result);
}
}
catch (Exception e)
{
throw new InvalidOperationException($"Invalid data at row_id {row_id}", e);
}
}
private void InsertExecutionEvent(WorkloadEvent evnt)
{
ExecutionWorkloadEvent evt = (ExecutionWorkloadEvent)evnt;
events_cmd.Parameters.AddWithValue("$event_sequence", event_sequence++);
events_cmd.Parameters.AddWithValue("$event_type", evt.Type);
events_cmd.Parameters.AddWithValue("$start_time", evt.StartTime);
events_cmd.Parameters.AddWithValue("$client_app_name", evt.ApplicationName);
events_cmd.Parameters.AddWithValue("$client_host_name", evt.HostName);
events_cmd.Parameters.AddWithValue("$database_name", evt.DatabaseName);
events_cmd.Parameters.AddWithValue("$server_principal_name", evt.LoginName);
events_cmd.Parameters.AddWithValue("$session_id", evt.SPID);
events_cmd.Parameters.AddWithValue("$sql_text", evt.Text);
events_cmd.Parameters.AddWithValue("$cpu", evt.CPU);
events_cmd.Parameters.AddWithValue("$duration", evt.Duration);
events_cmd.Parameters.AddWithValue("$reads", evt.Reads);
events_cmd.Parameters.AddWithValue("$writes", evt.Writes);
events_cmd.ExecuteNonQuery();
}
public override void ReadEvents()
{
EventCount = 0;
SqlTransformer transformer = new SqlTransformer();
using (QueryableXEventData eventstream = new QueryableXEventData(
ConnectionString,
SessionName,
EventStreamSourceOptions.EventStream,
EventStreamCacheOptions.DoNotCache))
{
var eventsEnumerator = eventstream.GetEnumerator();
while (!stopped && eventsEnumerator.MoveNext())
{
PublishedEvent evt = eventsEnumerator.Current;
ExecutionWorkloadEvent evnt = new ExecutionWorkloadEvent();
string commandText = String.Empty;
if (evt.Name == "rpc_completed")
{
commandText = (string)TryGetValue(evt, FieldType.Field, "statement");
evnt.Type = WorkloadEvent.EventType.RPCCompleted;
}
else if (evt.Name == "sql_batch_completed")
{
commandText = (string)TryGetValue(evt, FieldType.Field, "batch_text");
evnt.Type = WorkloadEvent.EventType.BatchCompleted;
}
else if (evt.Name == "attention")
{
object value = TryGetValue(evt, FieldType.Action, "sql_text");
try
{
if (value is string)
{
commandText = (string)value;
}
else if (value is byte[])
{
commandText = Encoding.Unicode.GetString((byte[])value);
}
else
{
throw new ArgumentException("Argument is of the wrong type");
}
}
catch (Exception e)
{
logger.Error(e, $"Unable to extract sql_text from attention event. Value is of type ${value.GetType().FullName}");
}
evnt.Type = WorkloadEvent.EventType.Timeout;
}
else if (evt.Name == "user_event")
{
int num = (int)TryGetValue(evt, FieldType.Field, "event_id");
if (num == 83)
{
commandText = (string)TryGetValue(evt, FieldType.Field, "user_data");
evnt.Type = WorkloadEvent.EventType.Error;
}
}
else
{
evnt.Type = WorkloadEvent.EventType.Unknown;
continue;
}
try
{
evnt.ApplicationName = (string)TryGetValue(evt, FieldType.Action, "client_app_name");
evnt.DatabaseName = (string)TryGetValue(evt, FieldType.Action, "database_name");
evnt.HostName = (string)TryGetValue(evt, FieldType.Action, "client_hostname");
evnt.LoginName = (string)TryGetValue(evt, FieldType.Action, "server_principal_name");
object oSession = TryGetValue(evt, FieldType.Action, "session_id");
if (oSession != null)
{
evnt.SPID = Convert.ToInt32(oSession);
}
if (commandText != null)
{
evnt.Text = commandText;
}
evnt.StartTime = evt.Timestamp.LocalDateTime;
if (evnt.Type == WorkloadEvent.EventType.Error)
{
// do nothing
}
else if (evnt.Type == WorkloadEvent.EventType.Timeout)
{
evnt.Duration = Convert.ToInt64(evt.Fields["duration"].Value);
evnt.CPU = Convert.ToInt64(evnt.Duration);
}
else
{
evnt.Reads = Convert.ToInt64(evt.Fields["logical_reads"].Value);
evnt.Writes = Convert.ToInt64(evt.Fields["writes"].Value);
evnt.CPU = Convert.ToInt64(evt.Fields["cpu_time"].Value);
evnt.Duration = Convert.ToInt64(evt.Fields["duration"].Value);
}
}
catch (Exception e)
{
logger.Error(e, "Error converting XE data from the stream.");
throw;
}
if (evnt.Type <= WorkloadEvent.EventType.BatchCompleted)
{
if (transformer.Skip(evnt.Text))
{
continue;
}
evnt.Text = transformer.Transform(evnt.Text);
}
Events.Enqueue(evnt);
EventCount++;
}
}
}
public override void ConsumeBuffered(WorkloadEvent evnt)
{
if (!(evnt is ExecutionWorkloadEvent))
{
return;
}
if (evnt.Type != WorkloadEvent.EventType.RPCCompleted && evnt.Type != WorkloadEvent.EventType.BatchCompleted)
{
return;
}
ExecutionWorkloadEvent evt = (ExecutionWorkloadEvent)evnt;
ReplayCommand command = new ReplayCommand()
{
CommandText = evt.Text,
Database = evt.DatabaseName,
ApplicationName = evt.ApplicationName
};
int session_id = -1;
session_id = (int)evt.SPID;
ReplayWorker rw = null;
if (ReplayWorkers.TryGetValue(session_id, out rw))
{
rw.AppendCommand(command);
}
else
{
rw = new ReplayWorker()
{
ConnectionInfo = this.ConnectionInfo,
ReplayIntervalSeconds = 0,
StopOnError = false,
Name = session_id.ToString()
};
ReplayWorkers.TryAdd(session_id, rw);
rw.AppendCommand(command);
logger.Info(String.Format("Worker [{0}] - Starting", session_id));
}
if (runner == null)
{
runner = new Thread(new ThreadStart(
delegate
{
try
{
RunWorkers();
}
catch (Exception e)
{
try { logger.Error(e, "Unhandled exception in TraceManager.RunWorkers"); }
catch { Console.WriteLine(e.Message); }
}
}
));
runner.IsBackground = true;
runner.Start();
}
if (sweeper == null)
{
sweeper = new Thread(new ThreadStart(
delegate
{
try
{
RunSweeper();
}
catch (Exception e)
{
try { logger.Error(e, "Unhandled exception in TraceManager.RunSweeper"); }
catch { Console.WriteLine(e.Message); }
}
}
));
sweeper.IsBackground = true;
sweeper.Start();
}
}
public override WorkloadEvent Read()
{
WorkloadEvent result = null;
long commandOffset = 0;
// first I need to return the event that
// contains the total number of events in the file
// once this is done I can start sending the actual events
if (!totalEventsMessageSent)
{
totalEventsMessageSent = true;
return(totalEventsMessage);
}
// process actual events from the file
try
{
if (reader == null)
{
return(null);
}
bool validEventFound = false;
do
{
if (!reader.Read())
{
stopped = true;
return(null);
}
result = ReadEvent(reader);
// Handle replay sleep for synchronization mode
// The sleep cannot happen here, but it has to
// happen later in the replay workflow, because
// it would only delay the insertion in the queue
// and it would not separate the events during the replay
if (result is ExecutionWorkloadEvent)
{
ExecutionWorkloadEvent execEvent = result as ExecutionWorkloadEvent;
if (SynchronizationMode)
{
if (startTime != DateTime.MinValue)
{
commandOffset = (long)((result.StartTime - startTime).TotalMilliseconds);
if (commandOffset > 0)
{
execEvent.ReplayOffset = commandOffset;
}
}
else
{
startTime = execEvent.StartTime;
}
}
else
{
// Leave it at 0. The replay consumer will interpret this
// as "do not wait for the requested offset" and will replay
// the event without waiting
execEvent.ReplayOffset = 0;
}
}
// Filter events
if (result is ExecutionWorkloadEvent)
{
validEventFound = Filter.Evaluate(result);
}
else
{
validEventFound = true;
}
}while (!validEventFound);
}
catch (Exception e)
{
if (stopped)
{
return(null);
}
DateTime?eventDate = null;
if (result != null)
{
eventDate = result.StartTime;
}
logger.Error(e);
logger.Error($"Unable to read next event. Current event date: {eventDate}");
throw;
}
return(result);
}
public override WorkloadEvent Read()
{
WorkloadEvent result = null;
double msSleep = 0;
try
{
if (reader == null)
{
return(null);
}
bool validEventFound = false;
do
{
if (!reader.Read())
{
stopped = true;
return(null);
}
result = ReadEvent(reader);
// Handle replay sleep for synchronization mode
// The sleep cannot happen here, but it has to
// happen later in the replay workflow, because
// it would only delay the insertion in the queue
// and it would not separate the events during the replay
if (result is ExecutionWorkloadEvent)
{
ExecutionWorkloadEvent execEvent = result as ExecutionWorkloadEvent;
if (SynchronizationMode)
{
DateTime lastEndTime = GetLastEventEndTime((int)execEvent.SPID);
if (lastEndTime != DateTime.MinValue)
{
msSleep = (result.StartTime - lastEndTime).TotalMilliseconds;
if (msSleep > 0)
{
if (msSleep > Int32.MaxValue)
{
msSleep = Int32.MaxValue;
}
Thread.Sleep(Convert.ToInt32(msSleep));
}
}
SetLastEventEndTime((int)execEvent.SPID, execEvent.StartTime.AddMilliseconds((double)execEvent.Duration / 1000));
}
else
{
execEvent.ReplaySleepMilliseconds = 0;
}
}
// Filter events
if (result is ExecutionWorkloadEvent)
{
validEventFound = Filter.Evaluate(result);
}
else
{
validEventFound = true;
}
}while (!validEventFound);
}
catch (Exception e)
{
if (stopped)
{
return(null);
}
DateTime?eventDate = null;
if (result != null)
{
eventDate = result.StartTime;
}
logger.Error(e);
logger.Error($"Unable to read next event. Current event date: {eventDate}");
throw;
}
return(result);
}
private void ReadEvents()
{
try
{
while (trace.Read() && !stopped)
{
try
{
ExecutionWorkloadEvent evt = new ExecutionWorkloadEvent();
if (trace.GetValue("EventClass").ToString() == "RPC:Completed")
{
evt.Type = WorkloadEvent.EventType.RPCCompleted;
}
else if (trace.GetValue("EventClass").ToString() == "SQL:BatchCompleted")
{
evt.Type = WorkloadEvent.EventType.BatchCompleted;
}
else
{
evt.Type = WorkloadEvent.EventType.Unknown;
}
evt.ApplicationName = (string)trace.GetValue("ApplicationName");
evt.DatabaseName = (string)trace.GetValue("DatabaseName");
evt.HostName = (string)trace.GetValue("HostName");
evt.LoginName = (string)trace.GetValue("LoginName");
evt.SPID = (int?)trace.GetValue("SPID");
evt.Text = (string)trace.GetValue("TextData");
evt.Reads = (long?)trace.GetValue("Reads");
evt.Writes = (long?)trace.GetValue("Writes");
evt.CPU = (int?)trace.GetValue("CPU");
evt.Duration = (long?)trace.GetValue("Duration");
evt.StartTime = DateTime.Now;
if (!Filter.Evaluate(evt))
{
continue;
}
events.Enqueue(evt);
}
catch (Exception ex)
{
logger.Error(ex.Message);
if (ex.InnerException != null)
{
logger.Error(ex.InnerException.Message);
}
}
} // while (Read)
}
catch (Exception ex)
{
logger.Error(ex.Message);
if (ex.InnerException != null)
{
logger.Error(ex.InnerException.Message);
}
Dispose();
}
}
public override void ConsumeBuffered(WorkloadEvent evnt)
{
if (evnt is MessageWorkloadEvent)
{
MessageWorkloadEvent msgEvent = evnt as MessageWorkloadEvent;
if (msgEvent.MsgType == MessageWorkloadEvent.MessageType.TotalEvents)
{
try
{
totalEventCount = (long)msgEvent.Value;
}
catch (Exception e)
{
logger.Error($"Unable to set the total number of events: {e.Message}");
}
}
}
if (!(evnt is ExecutionWorkloadEvent))
{
return;
}
if (evnt.Type != WorkloadEvent.EventType.RPCCompleted && evnt.Type != WorkloadEvent.EventType.BatchCompleted)
{
return;
}
eventCount++;
if ((eventCount > 0) && (eventCount % WorkerStatsCommandCount == 0))
{
string percentInfo = (totalEventCount > 0) ? "( " + ((eventCount * 100) / totalEventCount).ToString() + "% )" : "";
logger.Info($"{eventCount} events queued for replay {percentInfo}");
}
if (startTime == DateTime.MinValue)
{
startTime = DateTime.Now;
}
ExecutionWorkloadEvent evt = (ExecutionWorkloadEvent)evnt;
ReplayCommand command = new ReplayCommand()
{
CommandText = evt.Text,
Database = evt.DatabaseName,
ApplicationName = evt.ApplicationName,
ReplayOffset = evt.ReplayOffset,
EventSequence = evt.EventSequence
};
int session_id = -1;
session_id = (int)evt.SPID;
ReplayWorker rw = null;
if (ReplayWorkers.TryGetValue(session_id, out rw))
{
// Ensure that the buffer does not get too big
while (rw.QueueLength >= (BufferSize * .9))
{
spin.SpinOnce();
}
rw.AppendCommand(command);
}
else
{
rw = new ReplayWorker()
{
ConnectionInfo = this.ConnectionInfo,
ReplayIntervalSeconds = 0,
StopOnError = false,
Name = session_id.ToString(),
DisplayWorkerStats = this.DisplayWorkerStats,
ConsumeResults = this.ConsumeResults,
QueryTimeoutSeconds = this.QueryTimeoutSeconds,
WorkerStatsCommandCount = this.WorkerStatsCommandCount,
MimicApplicationName = this.MimicApplicationName,
DatabaseMap = this.DatabaseMap,
StartTime = startTime,
FailRetryCount = this.FailRetryCount,
TimeoutRetryCount = this.TimeoutRetryCount
};
ReplayWorkers.TryAdd(session_id, rw);
rw.AppendCommand(command);
logger.Info($"Worker [{session_id}] - Starting");
}
if (runner == null)
{
runner = new Thread(new ThreadStart(
delegate
{
try
{
RunWorkers();
}
catch (Exception e)
{
try { logger.Error(e, "Unhandled exception in ReplayConsumer.RunWorkers"); }
catch { Console.WriteLine(e.Message); }
}
}
));
runner.IsBackground = true;
runner.Start();
}
if (sweeper == null)
{
sweeper = new Thread(new ThreadStart(
delegate
{
try
{
RunSweeper();
}
catch (Exception e)
{
try { logger.Error(e, "Unhandled exception in TraceManager.RunSweeper"); }
catch { Console.WriteLine(e.Message); }
}
}
));
sweeper.IsBackground = true;
sweeper.Start();
}
}
public override void ReadEvents()
{
EventCount = 0;
SqlTransformer transformer = new SqlTransformer();
using (QueryableXEventData eventstream = new QueryableXEventData(
ConnectionString,
SessionName,
EventStreamSourceOptions.EventStream,
EventStreamCacheOptions.CacheToDisk))
{
var eventsEnumerator = eventstream.GetEnumerator();
while (!stopped && eventsEnumerator.MoveNext())
{
PublishedEvent evt = eventsEnumerator.Current;
ExecutionWorkloadEvent workloadEvent = new ExecutionWorkloadEvent();
try
{
workloadEvent.EventSequence = Convert.ToInt64(TryGetValue(evt, FieldType.Action, "event_sequence"));
string commandText = String.Empty;
if (evt.Name == "rpc_completed")
{
commandText = (string)TryGetValue(evt, FieldType.Field, "statement");
workloadEvent.Type = WorkloadEvent.EventType.RPCCompleted;
}
else if (evt.Name == "sql_batch_completed")
{
commandText = (string)TryGetValue(evt, FieldType.Field, "batch_text");
workloadEvent.Type = WorkloadEvent.EventType.BatchCompleted;
}
else if (evt.Name == "attention")
{
workloadEvent = new ErrorWorkloadEvent();
object value = TryGetValue(evt, FieldType.Action, "sql_text");
if (value == null)
{
continue;
}
try
{
if (value is string)
{
commandText = (string)value;
}
else if (value is byte[])
{
commandText = Encoding.Unicode.GetString((byte[])value);
}
else
{
throw new ArgumentException("Argument is of the wrong type");
}
}
catch (Exception e)
{
logger.Error(e, $"Unable to extract sql_text from attention event. Value is of type ${value.GetType().FullName}");
}
workloadEvent.Text = commandText;
workloadEvent.Type = WorkloadEvent.EventType.Timeout;
}
else if (evt.Name == "user_event")
{
workloadEvent = new ErrorWorkloadEvent();
int num = (int)TryGetValue(evt, FieldType.Field, "event_id");
if (num == 83 || num == 82)
{
if (TryGetString(evt, FieldType.Field, "user_info").StartsWith("WorkloadTools."))
{
commandText = TryGetString(evt, FieldType.Field, "user_data");
workloadEvent.Text = commandText;
if (num == 83)
{
workloadEvent.Type = WorkloadEvent.EventType.Error;
}
else
{
workloadEvent.Type = WorkloadEvent.EventType.Timeout;
}
}
else
{
workloadEvent.Type = WorkloadEvent.EventType.Unknown;
continue;
}
}
}
else
{
workloadEvent.Type = WorkloadEvent.EventType.Unknown;
continue;
}
try
{
workloadEvent.ApplicationName = TryGetString(evt, FieldType.Action, "client_app_name");
workloadEvent.DatabaseName = TryGetString(evt, FieldType.Action, "database_name");
workloadEvent.HostName = TryGetString(evt, FieldType.Action, "client_hostname");
workloadEvent.LoginName = TryGetString(evt, FieldType.Action, "server_principal_name");
workloadEvent.SPID = TryGetInt32(evt, FieldType.Action, "session_id");
if (commandText != null)
{
workloadEvent.Text = commandText;
}
workloadEvent.StartTime = evt.Timestamp.LocalDateTime;
if (workloadEvent.Type == WorkloadEvent.EventType.Error)
{
workloadEvent.Duration = 0;
workloadEvent.CPU = 0;
}
else if (workloadEvent.Type == WorkloadEvent.EventType.Timeout)
{
workloadEvent.Duration = TryGetInt64(evt, FieldType.Field, "duration");
workloadEvent.CPU = Convert.ToInt64(workloadEvent.Duration);
}
else
{
workloadEvent.Reads = TryGetInt64(evt, FieldType.Field, "logical_reads");
workloadEvent.Writes = TryGetInt64(evt, FieldType.Field, "writes");
workloadEvent.CPU = TryGetInt64(evt, FieldType.Field, "cpu_time");
workloadEvent.Duration = TryGetInt64(evt, FieldType.Field, "duration");
}
}
catch (Exception e)
{
logger.Error(e, "Error converting XE data from the stream.");
throw;
}
if (workloadEvent.Type <= WorkloadEvent.EventType.BatchCompleted)
{
if (transformer.Skip(workloadEvent.Text))
{
continue;
}
workloadEvent.Text = transformer.Transform(workloadEvent.Text);
}
Events.Enqueue(workloadEvent);
EventCount++;
}
catch (Exception ex)
{
logger.Error($"Error converting XE data from the stream: {ex.Message}");
try
{
logger.Error($" event type : {workloadEvent.Type}");
logger.Error($" client_app_name : {TryGetString(evt, FieldType.Action, "client_app_name")}");
logger.Error($" database_name : {TryGetString(evt, FieldType.Action, "database_name")}");
logger.Error($" client_hostname : {TryGetString(evt, FieldType.Action, "client_hostname")}");
logger.Error($" server_principal_name : {TryGetString(evt, FieldType.Action, "server_principal_name")}");
logger.Error($" session_id : {TryGetString(evt, FieldType.Action, "session_id")}");
logger.Error($" duration : {TryGetString(evt, FieldType.Field, "duration")}");
logger.Error($" logical_reads : {TryGetString(evt, FieldType.Field, "logical_reads")}");
logger.Error($" writes : {TryGetString(evt, FieldType.Field, "writes")}");
logger.Error($" cpu_time : {TryGetString(evt, FieldType.Field, "cpu_time")}");
}
catch (Exception)
{
//ignore, it is only logging
}
throw;
}
}
}
}
public ExecutionWorkloadEvent ParseEvent(SqlDataReader reader)
{
if (columns.Count == 0)
{
for (int i = 0; i < reader.FieldCount; i++)
{
string colName = reader.GetName(i);
columns.Add(colName.ToLower(), colName);
}
}
ExecutionWorkloadEvent evt = new ExecutionWorkloadEvent();
int eventClass = (int)reader["EventClass"];
if (eventClass == (int)EventClassEnum.RPC_Completed)
{
evt.Type = WorkloadEvent.EventType.RPCCompleted;
}
else if (eventClass == (int)EventClassEnum.SQL_BatchCompleted)
{
evt.Type = WorkloadEvent.EventType.BatchCompleted;
}
else if (eventClass == (int)EventClassEnum.Timeout)
{
if (reader["TextData"].ToString().StartsWith("WorkloadTools.Timeout["))
{
evt.Type = WorkloadEvent.EventType.Timeout;
}
}
else
{
evt.Type = WorkloadEvent.EventType.Unknown;
return(evt);
}
if (IsValidColumn("ApplicationName") && reader["ApplicationName"] != DBNull.Value)
{
evt.ApplicationName = (string)reader["ApplicationName"];
}
if (IsValidColumn("DatabaseName") && reader["DatabaseName"] != DBNull.Value)
{
evt.DatabaseName = (string)reader["DatabaseName"];
}
if (IsValidColumn("Hostname") && reader["HostName"] != DBNull.Value)
{
evt.HostName = (string)reader["HostName"];
}
if (IsValidColumn("LoginName") && reader["LoginName"] != DBNull.Value)
{
evt.LoginName = (string)reader["LoginName"];
}
if (IsValidColumn("SPID") && reader["SPID"] != DBNull.Value)
{
evt.SPID = (int?)reader["SPID"];
}
if (IsValidColumn("TextData") && reader["TextData"] != DBNull.Value)
{
evt.Text = (string)reader["TextData"];
}
if (IsValidColumn("StartTime") && reader["StartTime"] != DBNull.Value)
{
evt.StartTime = (DateTime)reader["StartTime"];
}
if (evt.Type == WorkloadEvent.EventType.Timeout)
{
if (IsValidColumn("BinaryData") && reader["BinaryData"] != DBNull.Value)
{
byte[] bytes = (byte[])reader["BinaryData"];
evt.Text = Encoding.Unicode.GetString(bytes);
}
if (IsValidColumn("TextData") && reader["TextData"] != DBNull.Value)
{
evt.Duration = ExtractTimeoutDuration(reader["TextData"]);
}
evt.CPU = Convert.ToInt64(evt.Duration);
}
else
{
if (IsValidColumn("Reads") && reader["Reads"] != DBNull.Value)
{
evt.Reads = (long?)reader["Reads"];
}
if (IsValidColumn("Writes") && reader["Writes"] != DBNull.Value)
{
evt.Writes = (long?)reader["Writes"];
}
if (IsValidColumn("CPU") && reader["CPU"] != DBNull.Value)
{
evt.CPU = (long?)Convert.ToInt64(reader["CPU"]) * 1000; // SqlTrace captures CPU as milliseconds => convert to microseconds
}
if (IsValidColumn("Duration") && reader["Duration"] != DBNull.Value)
{
evt.Duration = (long?)reader["Duration"];
}
if (IsValidColumn("EventSequence") && reader["EventSequence"] != DBNull.Value)
{
evt.EventSequence = (long?)reader["EventSequence"];
}
}
return(evt);
}
private void ReadEventsFromFile()
{
try
{
// get first trace rollover file
var parentDir = Directory.GetParent(tracePath);
var fileName = Path.GetFileNameWithoutExtension(tracePath) + "*" + Path.GetExtension(tracePath);
List <string> files = Directory.GetFiles(parentDir.FullName, fileName).ToList();
files.Sort();
SqlTransformer transformer = new SqlTransformer();
int rowsRead = 0;
foreach (string traceFile in files)
{
using (TraceFileWrapper reader = new TraceFileWrapper())
{
reader.InitializeAsReader(traceFile);
Dictionary <string, string> ColumnNames = null;
while (reader.Read() && !stopped)
{
try
{
if (ColumnNames == null)
{
ColumnNames = new Dictionary <string, string>();
string[] colNames =
{
"EventClass",
"ApplicationName",
"HostName",
"LoginName",
"SPID",
"TextData",
"StartTime",
"Reads",
"Writes",
"CPU",
"Duration"
};
foreach (var s in colNames)
{
if (reader.HasAttribute(s))
{
ColumnNames.Add(s, s);
}
}
}
ExecutionWorkloadEvent evt = new ExecutionWorkloadEvent();
if (reader.GetValue("EventClass").ToString() == "RPC:Completed")
{
evt.Type = WorkloadEvent.EventType.RPCCompleted;
}
else if (reader.GetValue("EventClass").ToString() == "SQL:BatchCompleted")
{
evt.Type = WorkloadEvent.EventType.BatchCompleted;
}
else
{
evt.Type = WorkloadEvent.EventType.Unknown;
continue;
}
if (ColumnNames.ContainsKey("ApplicationName"))
{
evt.ApplicationName = (string)reader.GetValue("ApplicationName");
}
if (ColumnNames.ContainsKey("DatabaseName"))
{
evt.DatabaseName = (string)reader.GetValue("DatabaseName");
}
if (ColumnNames.ContainsKey("HostName"))
{
evt.HostName = (string)reader.GetValue("HostName");
}
if (ColumnNames.ContainsKey("LoginName"))
{
evt.LoginName = (string)reader.GetValue("LoginName");
}
if (ColumnNames.ContainsKey("SPID"))
{
evt.SPID = (int?)reader.GetValue("SPID");
}
if (ColumnNames.ContainsKey("TextData"))
{
evt.Text = (string)reader.GetValue("TextData");
}
if (ColumnNames.ContainsKey("StartTime"))
{
evt.StartTime = (DateTime)reader.GetValue("StartTime");
}
if (ColumnNames.ContainsKey("Reads"))
{
evt.Reads = (long?)reader.GetValue("Reads");
}
if (ColumnNames.ContainsKey("Writes"))
{
evt.Writes = (long?)reader.GetValue("Writes");
}
if (ColumnNames.ContainsKey("CPU"))
{
evt.CPU = (long?)Convert.ToInt64(reader.GetValue("CPU")) * 1000; // SqlTrace captures CPU as milliseconds => convert to microseconds
}
if (ColumnNames.ContainsKey("Duration"))
{
evt.Duration = (long?)reader.GetValue("Duration");
}
if (transformer.Skip(evt.Text))
{
continue;
}
if (!Filter.Evaluate(evt))
{
continue;
}
evt.Text = transformer.Transform(evt.Text);
Events.Enqueue(evt);
rowsRead++;
}
catch (Exception ex)
{
logger.Error(ex.Message);
if (ex.InnerException != null)
{
logger.Error(ex.InnerException.Message);
}
}
} // while (Read)
} // using reader
} // foreach file
finished = true;
}
catch (Exception ex)
{
logger.Error(ex.Message);
if (ex.InnerException != null)
{
logger.Error(ex.InnerException.Message);
}
Dispose();
}
}
private void ReadEventsFromTDS()
{
string sqlReadTrace = @"
SELECT EventSequence
,Error
,TextData
,BinaryData
,DatabaseID
,HostName
,ApplicationName
,LoginName
,SPID
,Duration
,StartTime
,EndTime
,Reads
,Writes
,CPU
,EventClass
,DatabaseName
FROM fn_trace_gettable(@path, {0})
";
string sqlPath = @"
SELECT path
FROM sys.traces
WHERE id = @traceId;
";
long lastEvent = -1;
string lastTraceFile = "";
try
{
while (!stopped)
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConnectionInfo.ConnectionString;
conn.Open();
SqlCommand cmdPath = conn.CreateCommand();
cmdPath.CommandText = sqlPath;
if (traceId == -1)
{
traceId = GetTraceId(conn, Path.Combine(tracePath, "sqlworkload"));
if (traceId == -1)
{
throw new InvalidOperationException("The SqlWorkload capture trace is not running.");
}
}
var paramTraceId = cmdPath.Parameters.Add("@traceId", System.Data.SqlDbType.Int);
paramTraceId.Value = traceId;
string currentTraceFile = null;
try
{
currentTraceFile = (string)cmdPath.ExecuteScalar();
}
catch (Exception e)
{
logger.Error(e.StackTrace);
throw;
}
string filesParam = "1";
string pathToTraceParam = currentTraceFile;
// check if file has changed
if (lastTraceFile != currentTraceFile && !String.IsNullOrEmpty(lastTraceFile))
{
// when the rollover file changes, read from the last read file
// up to the end of all rollover files (this is what DEFAULT does)
filesParam = "DEFAULT";
pathToTraceParam = lastTraceFile;
// Check if the previous file still exists
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = String.Format(@"
SET NOCOUNT ON;
DECLARE @t TABLE (FileExists bit, FileIsADicrectory bit, ParentDirectoryExists bit);
INSERT @t
EXEC xp_fileexist '{0}';
SELECT FileExists FROM @t;
", lastTraceFile);
if (!(bool)cmd.ExecuteScalar())
{
pathToTraceParam = Path.Combine(tracePath, "sqlworkload.trc");
}
}
}
lastTraceFile = currentTraceFile;
String sql = String.Format(sqlReadTrace, filesParam);
if (lastEvent > 0)
{
sql += "WHERE EventSequence > @lastEvent";
}
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
var paramPath = cmd.Parameters.Add("@path", System.Data.SqlDbType.NVarChar, 255);
paramPath.Value = pathToTraceParam;
var paramLastEvent = cmd.Parameters.Add("@lastEvent", System.Data.SqlDbType.BigInt);
paramLastEvent.Value = lastEvent;
int rowsRead = 0;
SqlTransformer transformer = new SqlTransformer();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
if (reader["EventSequence"] != DBNull.Value)
{
lastEvent = (long)reader["EventSequence"];
}
ExecutionWorkloadEvent evt = new ExecutionWorkloadEvent();
int eventClass = (int)reader["EventClass"];
if (eventClass == (int)EventClassEnum.RPC_Completed)
{
evt.Type = WorkloadEvent.EventType.RPCCompleted;
}
else if (eventClass == (int)EventClassEnum.SQL_BatchCompleted)
{
evt.Type = WorkloadEvent.EventType.BatchCompleted;
}
else if (eventClass == (int)EventClassEnum.Timeout)
{
if (reader["TextData"].ToString().StartsWith("WorkloadTools.Timeout["))
{
evt.Type = WorkloadEvent.EventType.Timeout;
}
}
else
{
evt.Type = WorkloadEvent.EventType.Unknown;
continue;
}
if (reader["ApplicationName"] != DBNull.Value)
{
evt.ApplicationName = (string)reader["ApplicationName"];
}
if (reader["DatabaseName"] != DBNull.Value)
{
evt.DatabaseName = (string)reader["DatabaseName"];
}
if (reader["HostName"] != DBNull.Value)
{
evt.HostName = (string)reader["HostName"];
}
if (reader["LoginName"] != DBNull.Value)
{
evt.LoginName = (string)reader["LoginName"];
}
evt.SPID = (int?)reader["SPID"];
if (reader["TextData"] != DBNull.Value)
{
evt.Text = (string)reader["TextData"];
}
evt.StartTime = (DateTime)reader["StartTime"];
if (evt.Type == WorkloadEvent.EventType.Timeout)
{
if (reader["BinaryData"] != DBNull.Value)
{
byte[] bytes = (byte[])reader["BinaryData"];
evt.Text = Encoding.Unicode.GetString(bytes);
}
evt.Duration = ExtractTimeoutDuration(reader["TextData"]);
evt.CPU = Convert.ToInt64(evt.Duration);
}
else
{
evt.Reads = (long?)reader["Reads"];
evt.Writes = (long?)reader["Writes"];
evt.CPU = (long?)Convert.ToInt64(reader["CPU"]) * 1000; // SqlTrace captures CPU as milliseconds => convert to microseconds
evt.Duration = (long?)reader["Duration"];
}
if (transformer.Skip(evt.Text))
{
continue;
}
if (!Filter.Evaluate(evt))
{
continue;
}
evt.Text = transformer.Transform(evt.Text);
Events.Enqueue(evt);
rowsRead++;
}
}
// Wait before querying the trace file again
if (rowsRead < TraceRowsSleepThreshold)
{
Thread.Sleep(TraceIntervalSeconds * 1000);
}
}
}
}
}
catch (Exception ex)
{
logger.Error(ex.Message);
logger.Error(ex.StackTrace);
if (ex.InnerException != null)
{
logger.Error(ex.InnerException.Message);
}
Dispose();
}
}
// Read Workload events directly from the trace files
// on the server, via local path
// This method only works when the process is running
// on the same machine as the SQL Server
private void ReadEventsFromFile()
{
try
{
while (!stopped)
{
// get first trace rollover file
List <string> files = Directory.GetFiles(tracePath, "sqlworkload*.trc").ToList();
files.Sort();
string traceFile = files.ElementAt(0);
using (TraceFileWrapper reader = new TraceFileWrapper())
{
reader.InitializeAsReader(traceFile);
while (reader.Read() && !stopped)
{
try
{
ExecutionWorkloadEvent evt = new ExecutionWorkloadEvent();
if (reader.GetValue("EventClass").ToString() == "RPC:Completed")
{
evt.Type = WorkloadEvent.EventType.RPCCompleted;
}
else if (reader.GetValue("EventClass").ToString() == "SQL:BatchCompleted")
{
evt.Type = WorkloadEvent.EventType.BatchCompleted;
}
else
{
evt.Type = WorkloadEvent.EventType.Unknown;
}
evt.ApplicationName = (string)reader.GetValue("ApplicationName");
evt.DatabaseName = (string)reader.GetValue("DatabaseName");
evt.HostName = (string)reader.GetValue("HostName");
evt.LoginName = (string)reader.GetValue("LoginName");
evt.SPID = (int?)reader.GetValue("SPID");
evt.Text = (string)reader.GetValue("TextData");
evt.Reads = (long?)reader.GetValue("Reads");
evt.Writes = (long?)reader.GetValue("Writes");
evt.CPU = (long?)Convert.ToInt64(reader.GetValue("CPU")) * 1000; // SqlTrace captures CPU as milliseconds => convert to microseconds
evt.Duration = (long?)reader.GetValue("Duration");
evt.StartTime = DateTime.Now;
if (!Filter.Evaluate(evt))
{
continue;
}
Events.Enqueue(evt);
}
catch (Exception ex)
{
logger.Error(ex.Message);
if (ex.InnerException != null)
{
logger.Error(ex.InnerException.Message);
}
}
} // while (Read)
} // using reader
System.IO.File.Delete(traceFile);
} // while not stopped
}
catch (Exception ex)
{
logger.Error(ex.Message);
if (ex.InnerException != null)
{
logger.Error(ex.InnerException.Message);
}
Dispose();
}
}
// Parses all event data from the the data reader
private ExecutionWorkloadEvent parseEvent(XmlDocument doc)
{
ExecutionWorkloadEvent evt = new ExecutionWorkloadEvent();
XmlNode eventNode = doc.DocumentElement.SelectSingleNode("/event");
string name = eventNode.Attributes["name"].InnerText;
if (name == "sql_batch_completed")
{
evt.Type = WorkloadEvent.EventType.BatchCompleted;
}
else if (name == "rpc_completed")
{
evt.Type = WorkloadEvent.EventType.RPCCompleted;
}
else if (name == "attention")
{
evt.Type = WorkloadEvent.EventType.Timeout;
}
else if (name == "user_event")
{
evt.Type = WorkloadEvent.EventType.Error;
}
else
{
evt.Type = WorkloadEvent.EventType.Unknown;
return(evt);
}
DateTimeOffset timestamp = DateTimeOffset.Parse(eventNode.Attributes["timestamp"].Value);
evt.StartTime = timestamp.LocalDateTime;
foreach (XmlNode node in eventNode.ChildNodes)
{
switch ((string)node.Attributes["name"].Value)
{
case "statement":
if (evt.Type == WorkloadEvent.EventType.RPCCompleted)
{
evt.Text = (string)node.FirstChild.FirstChild.Value;
}
break;
case "batch_text":
if (evt.Type == WorkloadEvent.EventType.BatchCompleted)
{
evt.Text = (string)node.FirstChild.FirstChild.Value;
}
break;
case "sql_text":
if (evt.Type == WorkloadEvent.EventType.Timeout)
{
evt.Text = (string)node.FirstChild.FirstChild.Value;
}
break;
case "client_app_name":
evt.ApplicationName = (string)node.FirstChild.FirstChild.Value;
break;
case "database_name":
evt.DatabaseName = (string)node.FirstChild.FirstChild.Value;
break;
case "client_hostname":
evt.HostName = (string)node.FirstChild.FirstChild.Value;
break;
case "server_principal_name":
evt.LoginName = (string)node.FirstChild.FirstChild.Value;
break;
case "username":
evt.LoginName = (string)node.FirstChild.FirstChild.Value;
break;
case "session_id":
evt.SPID = Convert.ToInt32(node.FirstChild.FirstChild.Value);
break;
case "cpu_time":
evt.CPU = Convert.ToInt64(node.FirstChild.FirstChild.Value);
break;
case "duration":
evt.Duration = Convert.ToInt64(node.FirstChild.FirstChild.Value);
if (evt.Type == WorkloadEvent.EventType.Timeout)
{
evt.CPU = Convert.ToInt64(evt.Duration);
}
break;
case "logical_reads":
evt.Reads = Convert.ToInt64(node.FirstChild.FirstChild.Value);
break;
case "writes":
evt.Writes = Convert.ToInt64(node.FirstChild.FirstChild.Value);
break;
case "user_data":
evt.Text = (string)node.FirstChild.FirstChild.Value;
break;
case "event_sequence":
evt.EventSequence = Convert.ToInt64(node.FirstChild.FirstChild.Value);
break;
default:
break;
}
}
return(evt);
}
public override void ReadEvents()
{
string sqlXE = @"
DECLARE @filename nvarchar(max);
SELECT @filename = CAST(target_data AS xml).value('(/EventFileTarget/File/@name)[1]','nvarchar(max)')
FROM sys.dm_xe_{3}session_targets AS t
INNER JOIN sys.dm_xe_{3}sessions AS s
ON t.event_session_address = s.address
WHERE s.name = '{2}'
AND target_name = 'event_file';
SELECT event_data, file_offset
FROM sys.fn_xe_file_target_read_file(@filename, NULL, {0}, {1});
";
long lastEvent = -1;
try
{
while (!stopped)
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
string sql = "";
string databaseSuffix = ServerType == ExtendedEventsWorkloadListener.ServerType.AzureSqlDatabase ? "database_" : "";
if (lastEvent > 0)
{
sql = String.Format(sqlXE, "@filename", lastEvent, SessionName, databaseSuffix);
}
else
{
sql = String.Format(sqlXE, "NULL", "NULL", SessionName, databaseSuffix);
}
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
SqlTransformer transformer = new SqlTransformer();
int rowsRead = 0;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
if (reader["file_offset"] != DBNull.Value)
{
lastEvent = (long)reader["file_offset"];
}
ExecutionWorkloadEvent evt = new ExecutionWorkloadEvent();
string xmldata = (string)reader["event_data"];
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmldata);
XmlNode eventNode = doc.DocumentElement.SelectSingleNode("/event");
string name = eventNode.Attributes["name"].InnerText;
if (name == "sql_batch_completed")
{
evt.Type = WorkloadEvent.EventType.BatchCompleted;
}
else if (name == "rpc_completed")
{
evt.Type = WorkloadEvent.EventType.RPCCompleted;
}
else if (name == "attention")
{
evt.Type = WorkloadEvent.EventType.Timeout;
}
else if (name == "user_event")
{
evt.Type = WorkloadEvent.EventType.Error;
}
else
{
evt.Type = WorkloadEvent.EventType.Unknown;
continue;
}
DateTimeOffset timestamp = DateTimeOffset.Parse(eventNode.Attributes["timestamp"].Value);
evt.StartTime = timestamp.LocalDateTime;
foreach (XmlNode node in eventNode.ChildNodes)
{
switch ((string)node.Attributes["name"].Value)
{
case "statement":
if (evt.Type == WorkloadEvent.EventType.RPCCompleted)
{
evt.Text = (string)node.FirstChild.FirstChild.Value;
}
break;
case "batch_text":
if (evt.Type == WorkloadEvent.EventType.BatchCompleted)
{
evt.Text = (string)node.FirstChild.FirstChild.Value;
}
break;
case "sql_text":
if (evt.Type == WorkloadEvent.EventType.Timeout)
{
evt.Text = (string)node.FirstChild.FirstChild.Value;
}
break;
case "client_app_name":
evt.ApplicationName = (string)node.FirstChild.FirstChild.Value;
break;
case "database_name":
evt.DatabaseName = (string)node.FirstChild.FirstChild.Value;
break;
case "client_hostname":
evt.HostName = (string)node.FirstChild.FirstChild.Value;
break;
case "server_principal_name":
evt.LoginName = (string)node.FirstChild.FirstChild.Value;
break;
case "username":
evt.LoginName = (string)node.FirstChild.FirstChild.Value;
break;
case "session_id":
evt.SPID = Convert.ToInt32(node.FirstChild.FirstChild.Value);
break;
case "cpu_time":
evt.CPU = Convert.ToInt64(node.FirstChild.FirstChild.Value);
break;
case "duration":
evt.Duration = Convert.ToInt64(node.FirstChild.FirstChild.Value);
if (evt.Type == WorkloadEvent.EventType.Timeout)
{
evt.CPU = Convert.ToInt64(evt.Duration);
}
break;
case "logical_reads":
evt.Reads = Convert.ToInt64(node.FirstChild.FirstChild.Value);
break;
case "writes":
evt.Writes = Convert.ToInt64(node.FirstChild.FirstChild.Value);
break;
case "user_data":
evt.Text = (string)node.FirstChild.FirstChild.Value;
break;
default:
break;
}
}
if (evt.Type <= WorkloadEvent.EventType.BatchCompleted)
{
if (transformer.Skip(evt.Text))
{
continue;
}
evt.Text = transformer.Transform(evt.Text);
}
Events.Enqueue(evt);
rowsRead++;
EventCount++;
}
}
// Wait before querying the events file again
if (rowsRead < DEFAULT_TRACE_ROWS_SLEEP_THRESHOLD)
{
Thread.Sleep(DEFAULT_TRACE_INTERVAL_SECONDS * 1000);
}
}
}
}
}
catch (Exception ex)
{
logger.Error(ex.Message);
logger.Error(ex.StackTrace);
if (ex.InnerException != null)
{
logger.Error(ex.InnerException.Message);
}
}
}
private void _internalAdd(ExecutionWorkloadEvent evt)
{
if (rawData == null)
{
PrepareDataTables();
PrepareDictionaries();
}
string normSql = null;
var norm = normalizer.NormalizeSqlText(evt.Text, (int)evt.SPID);
if (norm != null)
{
normSql = norm.NormalizedText;
}
else
{
return;
}
if (normSql == null)
{
return;
}
long hash = normalizer.GetHashCode(normSql);
if (!normalizedQueries.ContainsKey(hash))
{
normalizedQueries.Add(hash, new NormalizedQuery {
Hash = hash, NormalizedText = normSql, ExampleText = evt.Text
});
}
int appId = -1;
if (evt.ApplicationName != null && !applications.TryGetValue(evt.ApplicationName, out appId))
{
applications.Add(evt.ApplicationName, appId = applications.Count);
}
int dbId = -1;
if (evt.DatabaseName != null && !databases.TryGetValue(evt.DatabaseName, out dbId))
{
databases.Add(evt.DatabaseName, dbId = databases.Count);
}
int hostId = -1;
if (evt.HostName != null && !hosts.TryGetValue(evt.HostName, out hostId))
{
hosts.Add(evt.HostName, hostId = hosts.Count);
}
int loginId = -1;
if (evt.LoginName != null && !logins.TryGetValue(evt.LoginName, out loginId))
{
logins.Add(evt.LoginName, loginId = logins.Count);
}
// Look up execution detail
List <ExecutionDetailValue> theList = null;
ExecutionDetailKey theKey = new ExecutionDetailKey()
{
sql_hash = hash,
application_id = appId,
database_id = dbId,
host_id = hostId,
login_id = loginId
};
ExecutionDetailValue theValue = new ExecutionDetailValue()
{
event_time = evt.StartTime,
cpu_us = evt.CPU,
reads = evt.Reads,
writes = evt.Writes,
duration_us = evt.Duration
};
if (rawData.TryGetValue(theKey, out theList))
{
if (theList == null)
{
theList = new List <ExecutionDetailValue>();
}
theList.Add(theValue);
}
else
{
theList = new List <ExecutionDetailValue>();
theList.Add(theValue);
if (!rawData.TryAdd(theKey, theList))
{
throw new InvalidOperationException("Unable to add an event to the queue");
}
}
}
public void ReadEvents()
{
EventCount = 0;
SqlTransformer transformer = new SqlTransformer();
using (QueryableXEventData eventstream = new QueryableXEventData(Path))
{
var eventsEnumerator = eventstream.GetEnumerator();
while (!stopped && eventsEnumerator.MoveNext())
{
PublishedEvent evt = eventsEnumerator.Current;
ExecutionWorkloadEvent evnt = new ExecutionWorkloadEvent();
string commandText = String.Empty;
if (evt.Name == "rpc_completed")
{
commandText = (string)TryGetValue(evt, FieldType.Field, "statement");
evnt.Type = WorkloadEvent.EventType.RPCCompleted;
}
else if (evt.Name == "sql_batch_completed")
{
commandText = (string)TryGetValue(evt, FieldType.Field, "batch_text");
evnt.Type = WorkloadEvent.EventType.BatchCompleted;
}
else if (evt.Name == "attention")
{
commandText = (string)TryGetValue(evt, FieldType.Action, "sql_text");
evnt.Type = WorkloadEvent.EventType.Timeout;
}
else
{
evnt.Type = WorkloadEvent.EventType.Unknown;
continue;
}
try
{
evnt.ApplicationName = (string)TryGetValue(evt, FieldType.Action, "client_app_name");
evnt.DatabaseName = (string)TryGetValue(evt, FieldType.Action, "database_name");
evnt.HostName = (string)TryGetValue(evt, FieldType.Action, "client_hostname");
evnt.LoginName = (string)TryGetValue(evt, FieldType.Action, "server_principal_name");
object oSession = TryGetValue(evt, FieldType.Action, "session_id");
if (oSession != null)
{
evnt.SPID = Convert.ToInt32(oSession);
}
if (commandText != null)
{
evnt.Text = commandText;
}
evnt.StartTime = evt.Timestamp.LocalDateTime;
if (evnt.Type == WorkloadEvent.EventType.Timeout)
{
evnt.Duration = Convert.ToInt64(evt.Fields["duration"].Value);
evnt.CPU = Convert.ToInt64(evnt.Duration);
}
else
{
evnt.Reads = Convert.ToInt64(evt.Fields["logical_reads"].Value);
evnt.Writes = Convert.ToInt64(evt.Fields["writes"].Value);
evnt.CPU = Convert.ToInt64(evt.Fields["cpu_time"].Value);
evnt.Duration = Convert.ToInt64(evt.Fields["duration"].Value);
}
}
catch (Exception e)
{
logger.Error(e, "Error converting XE data from the stream.");
throw;
}
if (transformer.Skip(evnt.Text))
{
continue;
}
evnt.Text = transformer.Transform(evnt.Text);
Events.Enqueue(evnt);
EventCount++;
}
finished = true;
}
}
private void ReadEventsFromStream()
{
try {
QueryableXEventData eventstream = new QueryableXEventData(
ConnectionInfo.ConnectionString,
"sqlworkload",
EventStreamSourceOptions.EventStream,
EventStreamCacheOptions.DoNotCache);
int rowsRead = 0;
SqlTransformer transformer = new SqlTransformer();
foreach (PublishedEvent evt in eventstream)
{
ExecutionWorkloadEvent evnt = new ExecutionWorkloadEvent();
string commandText = String.Empty;
if (evt.Name == "rpc_completed")
{
commandText = evt.Fields["statement"].Value.ToString();
evnt.Type = WorkloadEvent.EventType.RPCCompleted;
}
else if (evt.Name == "sql_batch_completed")
{
commandText = evt.Fields["batch_text"].Value.ToString();
evnt.Type = WorkloadEvent.EventType.BatchCompleted;
}
else if (evt.Name == "attention")
{
commandText = evt.Actions["sql_text"].Value.ToString();
evnt.Type = WorkloadEvent.EventType.Timeout;
}
else
{
evnt.Type = WorkloadEvent.EventType.Unknown;
continue;
}
if (evt.Actions["client_app_name"].Value != null)
{
evnt.ApplicationName = (string)evt.Actions["client_app_name"].Value;
}
if (evt.Actions["database_name"].Value != null)
{
evnt.DatabaseName = (string)evt.Actions["database_name"].Value;
}
if (evt.Actions["client_hostname"].Value != null)
{
evnt.HostName = (string)evt.Actions["client_hostname"].Value;
}
if (evt.Actions["server_principal_name"].Value != null)
{
evnt.LoginName = (string)evt.Actions["server_principal_name"].Value;
}
if (evt.Actions["session_id"].Value != null)
{
evnt.SPID = Convert.ToInt32(evt.Actions["session_id"].Value);
}
if (commandText != null)
{
evnt.Text = commandText;
}
evnt.StartTime = evt.Timestamp.LocalDateTime;
if (evnt.Type == WorkloadEvent.EventType.Timeout)
{
evnt.Duration = Convert.ToInt64(evt.Fields["duration"].Value);
evnt.CPU = Convert.ToInt32(evnt.Duration / 1000);
}
else
{
evnt.Reads = Convert.ToInt64(evt.Fields["logical_reads"].Value);
evnt.Writes = Convert.ToInt64(evt.Fields["writes"].Value);
evnt.CPU = Convert.ToInt32(evt.Fields["cpu_time"].Value);
evnt.Duration = Convert.ToInt64(evt.Fields["duration"].Value);
}
if (transformer.Skip(evnt.Text))
{
continue;
}
evnt.Text = transformer.Transform(evnt.Text);
Events.Enqueue(evnt);
rowsRead++;
}
}
catch (Exception ex)
{
logger.Error(ex.Message);
logger.Error(ex.StackTrace);
if (ex.InnerException != null)
{
logger.Error(ex.InnerException.Message);
}
Dispose();
}
}
private void _internalAdd(ExecutionWorkloadEvent evt)
{
if (rawData == null)
{
PrepareDataTable();
}
DataRow row = rawData.NewRow();
string normSql = null;
var norm = normalizer.NormalizeSqlText(evt.Text, (int)evt.SPID);
if (norm != null)
{
normSql = norm.NormalizedText;
}
else
{
return;
}
if (normSql == null)
{
return;
}
long hash = normalizer.GetHashCode(normSql);
if (!normalizedQueries.ContainsKey(hash))
{
normalizedQueries.Add(hash, new NormalizedQuery {
Hash = hash, NormalizedText = normSql, ExampleText = evt.Text
});
}
int appId = -1;
if (evt.ApplicationName != null && !applications.TryGetValue(evt.ApplicationName, out appId))
{
applications.Add(evt.ApplicationName, appId = applications.Count);
}
int dbId = -1;
if (evt.DatabaseName != null && !databases.TryGetValue(evt.DatabaseName, out dbId))
{
databases.Add(evt.DatabaseName, dbId = databases.Count);
}
int hostId = -1;
if (evt.HostName != null && !hosts.TryGetValue(evt.HostName, out hostId))
{
hosts.Add(evt.HostName, hostId = hosts.Count);
}
int loginId = -1;
if (evt.LoginName != null && !logins.TryGetValue(evt.LoginName, out loginId))
{
logins.Add(evt.LoginName, loginId = logins.Count);
}
row.SetField("sql_hash", hash);
row.SetField("application_id", appId);
row.SetField("database_id", dbId);
row.SetField("host_id", hostId);
row.SetField("login_id", loginId);
row.SetField("event_time", evt.StartTime);
row.SetField("cpu_ms", evt.CPU);
row.SetField("reads", evt.Reads);
row.SetField("writes", evt.Writes);
row.SetField("duration_ms", evt.Duration / 1000);
rawData.Rows.Add(row);
}
public override void ConsumeBuffered(WorkloadEvent evnt)
{
if (!(evnt is ExecutionWorkloadEvent))
{
return;
}
if (evnt.Type != WorkloadEvent.EventType.RPCCompleted && evnt.Type != WorkloadEvent.EventType.BatchCompleted)
{
return;
}
eventCount++;
if ((eventCount > 0) && (eventCount % WorkerStatsCommandCount == 0))
{
logger.Info($"{eventCount} events queued for replay");
}
ExecutionWorkloadEvent evt = (ExecutionWorkloadEvent)evnt;
ReplayCommand command = new ReplayCommand()
{
CommandText = evt.Text,
Database = evt.DatabaseName,
ApplicationName = evt.ApplicationName,
BeforeSleepMilliseconds = evt.ReplaySleepMilliseconds
};
int session_id = -1;
session_id = (int)evt.SPID;
ReplayWorker rw = null;
if (ReplayWorkers.TryGetValue(session_id, out rw))
{
// Ensure that the buffer does not get too big
while (rw.QueueLength >= (BufferSize * .9))
{
spin.SpinOnce();
}
rw.AppendCommand(command);
}
else
{
rw = new ReplayWorker()
{
ConnectionInfo = this.ConnectionInfo,
ReplayIntervalSeconds = 0,
StopOnError = false,
Name = session_id.ToString(),
DisplayWorkerStats = this.DisplayWorkerStats,
ConsumeResults = this.ConsumeResults,
QueryTimeoutSeconds = this.QueryTimeoutSeconds,
WorkerStatsCommandCount = this.WorkerStatsCommandCount,
MimicApplicationName = this.MimicApplicationName
};
ReplayWorkers.TryAdd(session_id, rw);
rw.AppendCommand(command);
logger.Info($"Worker [{session_id}] - Starting");
}
if (runner == null)
{
runner = new Thread(new ThreadStart(
delegate
{
try
{
RunWorkers();
}
catch (Exception e)
{
try { logger.Error(e, "Unhandled exception in TraceManager.RunWorkers"); }
catch { Console.WriteLine(e.Message); }
}
}
));
runner.IsBackground = true;
runner.Start();
}
if (sweeper == null)
{
sweeper = new Thread(new ThreadStart(
delegate
{
try
{
RunSweeper();
}
catch (Exception e)
{
try { logger.Error(e, "Unhandled exception in TraceManager.RunSweeper"); }
catch { Console.WriteLine(e.Message); }
}
}
));
sweeper.IsBackground = true;
sweeper.Start();
}
}