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 ReadTraceData(SqlConnection conn, ReadIteration currentIteration)
{
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, @number_files)
";
if (currentIteration.StartSequence > 0)
{
sqlReadTrace += "WHERE EventSequence > @event_offset";
}
logger.Debug("Reading Trace data...");
TraceEventParser parser = new TraceEventParser();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sqlReadTrace;
var paramPath = cmd.Parameters.Add("@path", System.Data.SqlDbType.NVarChar, 260);
paramPath.Value = currentIteration.StartFileName;
var paramNumberFiles = cmd.Parameters.Add("@number_files", System.Data.SqlDbType.Int);
paramNumberFiles.Value = currentIteration.Files;
var paramInitialSequence = cmd.Parameters.Add("@event_offset", System.Data.SqlDbType.BigInt);
paramInitialSequence.Value = currentIteration.StartOffset;
// don't pass initial file name and offset
// read directly from the initial file
// until we have some rows read already
if (
EventCount == 0 ||
currentIteration.StartOffset <= 0 ||
currentIteration.StartOffset == currentIteration.MinOffset
)
{
paramPath.Value = currentIteration.StartFileName;
paramNumberFiles.Value = 0;
paramInitialSequence.Value = 0;
}
logger.Debug($"paramPath : {paramPath.Value}");
logger.Debug($"paramNumberFiles : {paramNumberFiles.Value}");
logger.Debug($"paramInitialSequence: {paramInitialSequence.Value}");
SqlTransformer transformer = new SqlTransformer();
try
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
int skippedRows = 0;
while (reader.Read())
{
if (reader["EventSequence"] != DBNull.Value)
{
currentIteration.EndSequence = (long)reader["EventSequence"];
}
// read the event from the sqldatareader
var evt = parser.ParseEvent(reader);
// skip invalid events
if (evt.Type == WorkloadEvent.EventType.Unknown)
{
continue;
}
// skip to the correct event in case we're reading again
// from the same file and we have a reference sequence
if ((currentIteration.RowsRead == 0) && (currentIteration.StartSequence > 0))
{
// skip rows until we encounter the reference event_sequence
if (evt.EventSequence != currentIteration.StartSequence)
{
skippedRows++;
continue;
}
else
{
// skip one more row...
skippedRows++;
currentIteration.RowsRead++;
continue;
}
}
// this is only to print out a message, so consider
// getting rid of it
if (skippedRows > 0)
{
logger.Debug($"Skipped rows: {skippedRows}");
skippedRows = 0;
}
// now we have an event, no matter if good or bad => increment rows read
currentIteration.RowsRead++;
if (evt.EventSequence != null)
{
currentIteration.EndSequence = (long)evt.EventSequence;
}
if (evt.Type <= WorkloadEvent.EventType.BatchCompleted)
{
if (transformer.Skip(evt.Text))
{
continue;
}
if (!Filter.Evaluate(evt))
{
continue;
}
evt.Text = transformer.Transform(evt.Text);
}
// it's a "good" event: add it to the queue
Events.Enqueue(evt);
EventCount++;
}
logger.Debug($"currentIteration.EndSequence : {currentIteration.EndSequence}");
}
}
catch (Exception)
{
throw;
}
// Wait before querying the events file again
if (currentIteration.RowsRead < ReadIteration.DEFAULT_TRACE_ROWS_SLEEP_THRESHOLD &&
currentIteration.StartFileName == currentIteration.EndFileName)
{
Thread.Sleep(ReadIteration.DEFAULT_TRACE_INTERVAL_SECONDS * 1000);
}
}
}
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 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();
}
}
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 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;
}
}
}
}
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();
}
}
private void ReadXEData(SqlConnection conn, ReadIteration currentIteration)
{
string sqlXE = @"
SELECT event_data, file_name, file_offset
FROM sys.fn_xe_file_target_read_file(
@filename,
NULL,
@initial_file_name,
@initial_offset
)
";
logger.Debug("Reading XE data...");
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sqlXE;
var paramPath = cmd.Parameters.Add("@filename", System.Data.SqlDbType.NVarChar, 260);
if (ServerType != ExtendedEventsWorkloadListener.ServerType.AzureSqlDatabase)
{
paramPath.Value = currentIteration.GetXEFilePattern();
}
else
{
// Azure SqlDatabase does not support wildcards in file names
// Specify an exact file name
paramPath.Value = currentIteration.StartFileName;
}
var paramInitialFile = cmd.Parameters.Add("@initial_file_name", System.Data.SqlDbType.NVarChar, 260);
paramInitialFile.Value = currentIteration.StartFileName;
var paramInitialOffset = cmd.Parameters.Add("@initial_offset", System.Data.SqlDbType.BigInt);
paramInitialOffset.Value = currentIteration.GetInitialOffset();
// don't pass initial file name and offset
// read directly from the initial file
// until we have some rows read already
if (
EventCount == 0 ||
currentIteration.StartOffset <= 0 ||
currentIteration.StartOffset == currentIteration.MinOffset
)
{
if (ServerType != ExtendedEventsWorkloadListener.ServerType.LocalDB)
{
paramPath.Value = currentIteration.StartFileName;
}
paramInitialFile.Value = DBNull.Value;
paramInitialOffset.Value = DBNull.Value;
}
retryWithNULLS:
logger.Debug($"paramPath : {paramPath.Value}");
logger.Debug($"paramInitialFile : {paramInitialFile.Value}");
logger.Debug($"paramInitialOffset: {paramInitialOffset.Value}");
// in case we don't have any data in the xe file
// GetInitialOffset returns -1 and we need to wait a bit
// to let events flow to the file target
if (currentIteration.GetInitialOffset() > 0)
{
SqlTransformer transformer = new SqlTransformer();
using (var reader = cmd.ExecuteReader())
{
try
{
int skippedRows = 0;
while (reader.Read())
{
if (reader["file_name"] != DBNull.Value)
{
currentIteration.EndFileName = (string)reader["file_name"];
}
if (reader["file_offset"] != DBNull.Value)
{
currentIteration.EndOffset = (long)reader["file_offset"];
}
string xmldata = (string)reader["event_data"];
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmldata);
var evt = parseEvent(doc);
// skip to the correct event in case we're reading again
// from the same file and we have a reference sequence
if ((currentIteration.RowsRead == 0) && (currentIteration.StartSequence > 0))
{
// skip rows until we encounter the reference event_sequence
if (evt.EventSequence != currentIteration.StartSequence)
{
skippedRows++;
continue;
}
else
{
// skip one more row...
skippedRows++;
currentIteration.RowsRead++;
continue;
}
}
// this is only to print out a message, so consider
// getting rid of it
if (skippedRows > 0)
{
logger.Debug($"Skipped rows: {skippedRows}");
skippedRows = 0;
}
// now we have an event, no matter if good or bad => increment rows read
currentIteration.RowsRead++;
if (evt.EventSequence != null)
{
currentIteration.EndSequence = (long)evt.EventSequence;
}
if (evt.Type == WorkloadEvent.EventType.Unknown)
{
continue;
}
if (evt.Type <= WorkloadEvent.EventType.BatchCompleted)
{
if (transformer.Skip(evt.Text))
{
continue;
}
evt.Text = transformer.Transform(evt.Text);
}
// it's a "good" event: add it to the queue
Events.Enqueue(evt);
EventCount++;
}
logger.Debug($"currentIteration.EndSequence : {currentIteration.EndSequence}");
}
catch (Exception xx)
{
if (xx.Message.Contains("Specify an offset that exists in the log file"))
{
// retry the query without specifying the offset / file pair
paramInitialFile.Value = DBNull.Value;
paramInitialOffset.Value = DBNull.Value;
goto retryWithNULLS;
}
else
{
throw;
}
}
}
}
// Wait before querying the events file again
if (currentIteration.RowsRead < ReadIteration.DEFAULT_TRACE_ROWS_SLEEP_THRESHOLD)
{
Thread.Sleep(ReadIteration.DEFAULT_TRACE_INTERVAL_SECONDS * 1000);
}
}
}
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;
SqlTransformer transformer = new SqlTransformer();
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;
}
// preprocess and filter events
if (execEvent.Type <= WorkloadEvent.EventType.BatchCompleted)
{
if (transformer.Skip(execEvent.Text))
{
continue;
}
execEvent.Text = transformer.Transform(execEvent.Text);
}
}
// 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);
}