private static void Main()
{
var connectionInfo = new SqlConnectionInfo("(localdb)\\MSSQLLocalDb")
{
UseIntegratedSecurity = true
};
var trace = new TraceServer();
trace.InitializeAsReader(connectionInfo, "trace.trc");
while (trace.Read())
{
if (trace["TextData"] == null)
{
continue;
}
if (RecordDoesNotContainNotSupportedEvent(trace["TextData"].ToString()) == false)
{
continue;
}
Console.WriteLine("Event : " + trace["EventClass"]);
Console.WriteLine("SPID : " + trace["SPID"]);
Console.WriteLine("Timestamp: " + DateTime.Now);
Console.WriteLine("Text : " + trace["TextData"]);
AnalyzeQuery(trace["TextData"].ToString());
Console.WriteLine();
}
}
/// <summary>
/// Does the simulation.
/// </summary>
/// <param name="state">The state.</param>
private void DoSimulation(object state)
{
try
{
lock (m_lock)
{
foreach (var blockAddress in m_blocks)
{
for (int ii = 0; ii < blockAddress.Value.Properties.Count - 1; ii++)
{
string firstName = blockAddress.Value.Properties[ii].Name;
string secondName = blockAddress.Value.Properties[ii + 1].Name;
if (!secondName.StartsWith(firstName) || !secondName.EndsWith("SetPoint"))
{
continue;
}
int valueOffset = blockAddress.Value.Properties[ii].Offset;
int setpointOffset = blockAddress.Value.Properties[ii + 1].Offset;
double value = (double)Read(blockAddress.Key, valueOffset);
double setpoint = (double)Read(blockAddress.Key, setpointOffset);
Write(blockAddress.Key, valueOffset, Adjust(value, setpoint));
}
}
}
}
catch (Exception e)
{
TraceServer.Error(e, "Failed run simulation.");
}
}
private void Trace()
{
try
{
SqlConnectionInfo connInfo = new SqlConnectionInfo();
if (_dbConnection.IntegratedSecurity)
{
connInfo.UseIntegratedSecurity = true;
}
else
{
connInfo.ServerName = _dbConnection.Server;
connInfo.UserName = _dbConnection.UserID;
connInfo.Password = _dbConnection.Password;
}
TraceServer trace = new TraceServer();
trace.InitializeAsReader(connInfo, @"trace.tdf");
while (trace.Read())
{
if (!_stopTrace)
{
var record = new Trace(trace);
this.dgvRecord.Invoke(new ShowRecordDelegate(AddTrace), record);
}
}
}
catch (ThreadAbortException e) { }
}
/// <summary>
/// Configure the trace to run against the local SQL Server.
/// </summary>
private static void SQLTraceLiveReader()
{
TraceServer traceServerReader;
SqlConnectionInfo sci;
string traceConfigFileName;
string programFilesPath;
// Setup connection to the SQL Server
traceServerReader = new TraceServer();
// Use the local SQL Server
sci = new SqlConnectionInfo();
sci.UseIntegratedSecurity = true;
// Test for SQL Express
Server srvr = new Server(sci.ServerName);
if (srvr.Information.Edition != @"Express Edition")
{
// Configure the reader
// Use the Standard profiler configuration
traceConfigFileName = Properties.Settings.Default.TraceConfigFile;
programFilesPath = Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles);
traceServerReader.InitializeAsReader(sci, programFilesPath + traceConfigFileName);
// Start reading the trace information
SQLTraceReader(traceServerReader);
}
else
{
Console.WriteLine("SQL Express is not supported for tracing");
}
}
/// <summary>
/// Configure the trace to run against the local SQL Server.
/// </summary>
private static void SQLTraceLiveReader()
{
TraceServer traceServerReader;
SqlConnectionInfo sci;
string traceConfigFileName;
string programFilesPath;
// Setup connection to the SQL Server
traceServerReader = new TraceServer();
// Use the local SQL Server
sci = new SqlConnectionInfo();
sci.UseIntegratedSecurity = true;
// Test for SQL Express
Server srvr = new Server(sci.ServerName);
if (srvr.Information.Edition != @"Express Edition")
{
// Configure the reader
// Use the Standard profiler configuration
traceConfigFileName = Properties.Settings.Default.TraceConfigFile;
programFilesPath = Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles);
traceServerReader.InitializeAsReader(sci, programFilesPath + traceConfigFileName);
// Start reading the trace information
SQLTraceReader(traceServerReader);
}
else
{
Console.WriteLine("SQL Express is not supported for tracing");
}
}
static void Main(string[] args)
{
// 1. Run trace server
var connectionInfo = new SqlConnectionInfo(args[0])
{
DatabaseName = args[1],
UseIntegratedSecurity = true
};
var trace = new TraceServer();
trace.InitializeAsReader(connectionInfo, args[2]);
// 2. Continuously read trace and send them to event hubs
var tokenSource = new CancellationTokenSource();
var readerTask = Task.Factory.StartNew(() => ReadTrace(trace, tokenSource.Token), tokenSource.Token);
var senderTask = Task.Factory.StartNew(() => SendToEventHubs(tokenSource.Token), tokenSource.Token);
// 3. Stop the trace
Console.WriteLine("Press any key to stop...");
Console.ReadKey();
Console.WriteLine("Stopping....");
tokenSource.Cancel();
Task.WaitAll(readerTask, senderTask);
Console.WriteLine("Stopped");
}
public static TraceServer BeginTrace(SqlConnectionInfo sci)
{
TraceServer traceServerReader = new TraceServer();
traceServerReader.InitializeAsReader(sci, GetTraceTemplateName());
return(traceServerReader);
}
public static PerformanceData EndTrace(string uri, TraceServer traceServerReader)
{
PerformanceData perf = new PerformanceData(uri);
traceServerReader.Stop();
while (traceServerReader.Read())
{
string ApplicationName = traceServerReader.GetString(2);
if (ApplicationName == null)
{
ApplicationName = string.Empty;
}
if (ApplicationName == "SQL Management")
{
continue;
}
if (ApplicationName == "Microsoft SQL Server Management Studio")
{
continue;
}
if (ApplicationName.StartsWith("SQL Server Profiler"))
{
continue;
}
//0: EventClass
//1: TextData
//2: ApplicationName
//3: NTUserName
//4: LoginName
//5: CPU
//6: Reads
//7: Writes
//8: Duration
//9: ClientProcessID
//10: SPID
//11: StartTime
//12: EndTime
//13: BinaryData
//string query = traceServerReader.GetString(1);
//if (string.IsNullOrEmpty(query)) query = string.Empty;
//if (query.StartsWith("exec sp_executesql N'")) query = query.Remove(0, 21);
// if (query.Length > 64) query = query.Substring(0, 64);
//Console.WriteLine("{0}: {1} -> {2} | CPU={3} | Duration={4}", nEventNum,
// query,
// traceServerReader.GetString(2),
// traceServerReader.GetOrdinal("CPU"),
// traceServerReader.GetOrdinal("Duration"));
perf.Queries++;
object duration = traceServerReader.GetValue(8);
perf.TotalDuration += (duration == null ? 0 : (long)duration);
}
return(perf);
}
private void Connector_Closed(object sender, EventArgs e)
{
TraceServer.Trace(new TraceClass()
{
Message = string.Format("Connection closed"),
Action = "Connector_Closed"
});
}
public void Start()
{
running = true;
reader = new TraceServer();
System.IO.FileInfo fi = new System.IO.FileInfo(@".\Sql\SqlTraceTemplate.tdf");
reader.InitializeAsReader(ci, fi.FullName);
Thread t = new Thread(new ThreadStart(DoTrace));
t.Start();
}
public Trace(TraceServer ts)
{
this.EventClass = ts["EventClass"] as String;
this.Duration = ts["Duration"] as String;
this.LoginName = ts["SessionLoginName"] as String;
this.TextData = (ts["TextData"] as String ?? "").Trim();
this.ObjectName = ts["ObjectName"] as String;
this.ObjectType = ts["ObjectType"] as String;
this.ClientProcessID = ts["SPID"] as String;
this.StartTime = DateTime.Now.ToString("yyyy-MM-dd HH:MM:ss");
}
public void StopServer()
{
if (traceServer != null)
{
using (new ItemTracer("Terminator", "Terminator"))
{
traceServer.StopServer = true;
}
traceServer = null;
}
}
private PerformanceData GetPagePerformance(Uri root, Uri uri)
{
SqlConnectionInfo sci = SqlTrace.GetTraceSqlConnectionInfo();
Server srvr = new Server(sci.ServerName);
Assert.AreNotEqual("SqlServer Express not supported.", srvr.Information.Edition, "Express Edition");
TraceServer traceServerReader = SqlTrace.BeginTrace(sci);
TestPage(root, uri);
return(SqlTrace.EndTrace(uri.ToString(), traceServerReader));
}
public void Trace(string Message)
{
TraceClass traceClass = new TraceClass()
{
Message = Message,
Action = string.Format("Trace from application assembly: {0} ", Assembly.GetExecutingAssembly().FullName)
};
_traceList.Add(traceClass);
TraceServer.Trace(traceClass);
}
bool ConnectOlap(out TraceServer traceServer, string SSASserver)
{
OlapConnectionInfo ci = new OlapConnectionInfo();
traceServer = new TraceServer();
StringBuilder messageText = new StringBuilder();
try
{
ci.UseIntegratedSecurity = true;
ci.ServerName = SSASserver;
string tracetemplate = localPath + "\\" + Properties.Settings.Default.TraceDefinition;
traceServer.InitializeAsReader(ci, tracetemplate);
lock (traceServers)
{
traceServers.Add(traceServer);
}
messageText.Append(
DateTime.Now.ToString()
+ ": Created trace for Analysis Server : '"
+ SSASserver
+ "'");
WriteLog(messageText.ToString());
EventLog.WriteEntry(this.ServiceName, messageText.ToString(), EventLogEntryType.Information);
return(true);
}
catch (Exception e)
{
messageText.Append(DateTime.Now.ToString() + ": Cannot start Analysis Server trace: ").AppendLine();
messageText.Append(DateTime.Now.ToString() + ": Analysis Server name: '" + SSASserver + "'").AppendLine();
messageText.Append(DateTime.Now.ToString() + ": Trace definition : '" + localPath + "\\" + Properties.Settings.Default.TraceDefinition + "'").AppendLine();
messageText.Append(DateTime.Now.ToString() + ": Error: " + e.Message).AppendLine();
messageText.Append(DateTime.Now.ToString() + ": Stack Trace: " + e.StackTrace).AppendLine();
while (e.InnerException != null)
{
messageText.Append("INNER EXCEPTION: ");
messageText.Append(e.InnerException.Message).AppendLine();
messageText.Append(e.InnerException.StackTrace).AppendLine();
e = e.InnerException;
}
WriteLog(messageText.ToString());
EventLog.WriteEntry(this.ServiceName, messageText.ToString(), EventLogEntryType.Error);
return(false);
}
}
public MainProfiler()
{
InitializeComponent();
this.InitializeBindings();
traceServer = null;
var repository = new MemoryRepository();
this.traceServices = new TraceServices(repository);
this.repository = repository;
commandParametersBindingSource.DataSource = parameter;
parameter.ServerStatusChanged += new Action <bool>(parameter_ServerStatusChanged);
}
private void StartTheServer()
{
if (traceServer == null)
{
traceServer = new TraceServer();
traceServer.Received += new Action <LogTracer.TraceItem>((arr) =>
{
this.repository.Add(arr);
});
ThreadPool.QueueUserWorkItem(new WaitCallback((o) =>
{
traceServer.Listen();
}));
}
}
public void StartServer()
{
if (traceServer == null)
{
traceServer = new TraceServer();
traceServer.Received += new Action <TraceItem>((arr) =>
{
repo.Add(arr);
});
ThreadPool.QueueUserWorkItem(new WaitCallback((o) =>
{
traceServer.Listen();
}));
}
}
public static PerformanceData EndTrace(string uri, TraceServer traceServerReader)
{
PerformanceData perf = new PerformanceData(uri);
traceServerReader.Stop();
while (traceServerReader.Read())
{
string ApplicationName = traceServerReader.GetString(2);
if (ApplicationName == null) ApplicationName = string.Empty;
if (ApplicationName == "SQL Management") continue;
if (ApplicationName == "Microsoft SQL Server Management Studio") continue;
if (ApplicationName.StartsWith("SQL Server Profiler")) continue;
//0: EventClass
//1: TextData
//2: ApplicationName
//3: NTUserName
//4: LoginName
//5: CPU
//6: Reads
//7: Writes
//8: Duration
//9: ClientProcessID
//10: SPID
//11: StartTime
//12: EndTime
//13: BinaryData
//string query = traceServerReader.GetString(1);
//if (string.IsNullOrEmpty(query)) query = string.Empty;
//if (query.StartsWith("exec sp_executesql N'")) query = query.Remove(0, 21);
// if (query.Length > 64) query = query.Substring(0, 64);
//Console.WriteLine("{0}: {1} -> {2} | CPU={3} | Duration={4}", nEventNum,
// query,
// traceServerReader.GetString(2),
// traceServerReader.GetOrdinal("CPU"),
// traceServerReader.GetOrdinal("Duration"));
perf.Queries++;
object duration = traceServerReader.GetValue(8);
perf.TotalDuration += (duration == null ? 0 : (long)duration);
}
return perf;
}
private static void ReadTrace(TraceServer trace, CancellationToken token)
{
while (trace.Read() && !token.IsCancellationRequested)
{
var eventClass = trace["EventClass"].ToString();
if (string.Compare(eventClass, "RPC:Completed") == 0)
{
var textData = trace["TextData"].ToString();
if (!textData.Contains("sp_reset_connection") &&
!textData.Contains("sp_trace") &&
!textData.Contains("sqlagent"))
{
eventQueue.Enqueue(textData);
}
}
}
trace.Stop();
trace.Close();
}
public IObservable<IEventBase> GetEvents()
{
if (null != connectionInfo)
{
return GetEvents(() =>
{
var trace = new TraceServer();
trace.InitializeAsReader(this.connectionInfo, this.filename);
return trace;
});
}
else
{
return GetEvents(() =>
{
var trace = new TraceFile();
trace.InitializeAsReader(filename);
return trace;
});
}
}
bool ConnectSQL(ref TraceServer traceServer, out TraceTable tableWriter, string SSASserver)
{
SqlConnectionInfo connInfo = new SqlConnectionInfo(Properties.Settings.Default.SQLServer);
StringBuilder messageText = new StringBuilder();
string _AppendInst = "_" + SSASserver;
string _SQLTable;
string _AppendDate;
//Maintains legacy logic where by a server with single instance does not have any inst names appended.
if (_NumInstance == 1)
{
_AppendInst = "";
}
//Append data to end of SQL table. Useful as an alternative to preserver SQL but data only survives 1 restart a day and you need cleanup logic in SQL Server
if (Properties.Settings.Default.AppendDateToSQLTable)
{
_AppendDate = "_" + DateTime.Now.ToString("yyyyMMdd");
}
else
{
_AppendDate = "";
}
_SQLTable =
Properties.Settings.Default.TraceTableName
+ _AppendInst
+ _AppendDate;
if (Properties.Settings.Default.PreserveHistory)
{
PreserveSQLHistory(ref _SQLTable);
}
tableWriter = new TraceTable();
try
{
connInfo.DatabaseName = Properties.Settings.Default.SQLServerDatabase;
tableWriter.InitializeAsWriter(traceServer, connInfo, _SQLTable);
messageText.Append(DateTime.Now.ToString() + ": Created Analysis Server trace table: '" + _SQLTable + "' on SQL Server: '" + Properties.Settings.Default.SQLServer
+ "' in database: " + Properties.Settings.Default.SQLServerDatabase + "'");
WriteLog(messageText.ToString());
EventLog.WriteEntry(this.ServiceName, messageText.ToString(), EventLogEntryType.Information);
return(true);
}
catch (Exception e)
{
messageText.Append(DateTime.Now.ToString() + ": Cannot create Analysis Server trace table: '" + SSASserver + "'").AppendLine();
messageText.Append(DateTime.Now.ToString() + ": SQL Server Name: '" + Properties.Settings.Default.SQLServer + "'").AppendLine();
messageText.Append(DateTime.Now.ToString() + ": SQL Server Database : '" + Properties.Settings.Default.SQLServerDatabase + "'").AppendLine();
messageText.Append(DateTime.Now.ToString() + ": SQL Server Table : '" + _SQLTable + "'").AppendLine();
messageText.Append(DateTime.Now.ToString() + ": Error: " + e.Message).AppendLine();
while (e.InnerException != null)
{
messageText.Append("INNER EXCEPTION: ");
messageText.Append(e.InnerException.Message).AppendLine();
messageText.Append(e.InnerException.StackTrace.ToString()).AppendLine();
e = e.InnerException;
}
WriteLog(messageText.ToString());
EventLog.WriteEntry(this.ServiceName, messageText.ToString(), EventLogEntryType.Error);
return(false);
}
}
public StopAndCloseTraceOnExit(TraceServer trace)
{
this.trace = trace;
SetConsoleCtrlHandler(new HandlerRoutine(ConsoleCtrlCheck), true);
}
public void TestDeep(Cookie cookie, SqlConnectionInfo sci)
{
Uri root = new Uri("http://localhost/DBlog/Default.aspx");
List <Uri> queue = new List <Uri>(2500);
List <Uri> visited = new List <Uri>(5000);
PerformanceDataCollection perfdata = (sci == null ? null : new PerformanceDataCollection());
double totaltime = 0;
int totalcount = 0;
queue.Add(root);
Dictionary <Uri, Uri> references = new Dictionary <Uri, Uri>();
references.Add(root, new Uri("http://localhost/DBlog/"));
while (queue.Count > 0)
{
Uri topofqueue = queue[0];
List <HtmlUri> links;
double ts = 0;
PerformanceData perf = (perfdata != null ? new PerformanceData(topofqueue.ToString()) : null);
try
{
TraceServer traceServerReader = (sci == null ? null : SqlTrace.BeginTrace(sci));
TestPage(references[topofqueue], topofqueue, cookie, out links, out ts);
if (perfdata != null)
{
perfdata.Add(SqlTrace.EndTrace(topofqueue.ToString(), traceServerReader));
}
}
catch (Exception ex)
{
Console.WriteLine();
Console.WriteLine("{0}: {1}", topofqueue, ex.Message);
throw ex;
}
finally
{
totalcount++;
totaltime += ts;
// add to visited links
visited.Add(topofqueue);
// remove from queue
queue.RemoveAt(0);
}
if (perfdata != null)
{
perfdata.Add(perf);
}
// -----------------
Console.Write("{0}/{1} [avg: {2}]", totalcount, queue.Count, (totaltime / totalcount).ToString("0.00"));
if (perf != null)
{
Console.Write("[SQL: {0} in {1}] ", perf.Queries, perf.TotalDuration);
}
Console.Write(" => ");
int count = 0;
foreach (HtmlUri uri in links)
{
Uri fulluri = uri.Uri;
if (!root.IsBaseOf(fulluri))
{
// Console.WriteLine("\tSkipping {0}.", uri);
continue;
}
if (references.ContainsKey(fulluri) || queue.Contains(fulluri) || visited.Contains(fulluri))
{
continue;
}
Assert.IsFalse(fulluri.ToString().Contains("\0"),
string.Format("Uri {0} in {1} contains non-ASCII character.", fulluri, topofqueue));
Assert.IsFalse(fulluri.ToString().Contains("<%"),
string.Format("Uri {0} in {1} contains non-executed ASP.NET code.", fulluri, topofqueue));
Assert.IsFalse(fulluri.ToString().Contains("id=0"),
string.Format("Uri {0} in {1} contains a link to a zero id.", fulluri, topofqueue));
references.Add(fulluri, topofqueue);
// Console.WriteLine("\tAdding {0}.", fulluri.OriginalString);
queue.Add(fulluri);
count++;
}
if ((perfdata != null) && (((totalcount > 0) && ((totalcount % 100) == 0)) || (queue.Count == 0)))
{
perfdata.DumpPigs();
}
}
}
public DefaultServer()
{
InitializeComponent();
TraceServer.Register(new MemoryTraceListener());
}
protected void StartTrace()
{
try
{
trace = new TraceServer();
trace.InitializeAsReader(connectionInfo, traceDefinitionFile);
object[] userState = new object[2];
userState[0] = this.trace;
this.State = TraceState.Started;
DataTable schema=null;
while (this.trace.Read())
{
try
{
if (schema == null) schema = FromTraceReaderSchema(this.trace.GetSchemaTable());
schema = (DataTable)schema.Clone();
//schmea.Clear();
//schema.AcceptChanges();
//schema = FromTraceReaderSchema(this.trace.GetSchemaTable());
userState[0] = ReaderToDataRow(this.trace, schema);
userState[1] = this.State;
this.backWorker.ReportProgress(0, userState);
}
catch (Exception ex)//System.AccessViolationException in Microsoft.SqlServer.ConnectionInfoExtended.dll
{//Microsoft.SqlServer.Management.Trace.SqlTraceException
System.Diagnostics.Trace.WriteLine(ex.StackTrace);
System.Diagnostics.Trace.WriteLine(ex.Message);
}
}
this.State = TraceState.Stopped;
userState[1] = this.State;
}
catch (Exception ex)
{
trace = null;
this.State = TraceState.NotInitiated;
throw ex;
}
}
public void Trace(TraceClass msg)
{
TraceServer.Trace(msg);
}
public static TraceServer BeginTrace(SqlConnectionInfo sci)
{
TraceServer traceServerReader = new TraceServer();
traceServerReader.InitializeAsReader(sci, GetTraceTemplateName());
return traceServerReader;
}
private void Init()
{
_traceServer = new TraceServer();
}
void DoWork(object SSAS)
{
string SSASserver = (string)SSAS;
TraceTable _SQLDestTableWriter = null;
TraceServer _SSASSourceTraceServer = null;
int _RetryCounter = 0;
bool bFirstLoop = true;
while (bFirstLoop || _RetryCounter < Properties.Settings.Default.RestartRetries)
{
bFirstLoop = false;
try
{
//Grab connection to SSAS
bool bSuccess = ConnectOlap(out _SSASSourceTraceServer, SSASserver);
if (bSuccess)
{
//Grab connection to SQL and connect it with the SSAS trace
bSuccess = ConnectSQL(ref _SSASSourceTraceServer, out _SQLDestTableWriter, SSASserver);
if (bSuccess)
{
_RetryCounter = 0;
while (_SQLDestTableWriter.Write())
{
if (_SQLDestTableWriter.IsClosed)
{
throw new Exception("SQL connection closed unexpectedly.");
}
if (_SSASSourceTraceServer.IsClosed)
{
throw new Exception("SSAS connection closed unexpectedly.");
}
}
}
}
}
catch (Exception ex)
{
StringBuilder messageText = new StringBuilder();
messageText.Append(DateTime.Now.ToString() + ": Error reading trace: " + ex.Message).AppendLine();
messageText.Append(ex.StackTrace).AppendLine();
while (ex.InnerException != null)
{
messageText.Append("INNER EXCEPTION: ");
messageText.Append(ex.InnerException.Message).AppendLine();
messageText.Append(ex.InnerException.StackTrace).AppendLine();
ex = ex.InnerException;
}
WriteLog(messageText.ToString());
EventLog.WriteEntry(this.ServiceName, messageText.ToString(), EventLogEntryType.Warning);
}
try
{
_SSASSourceTraceServer.Stop();
_SSASSourceTraceServer.Close();
}
catch { }
try
{
_SQLDestTableWriter.Close();
}
catch { }
_RetryCounter++;
if (_RetryCounter < Properties.Settings.Default.RestartRetries)
{
StringBuilder messageText2 = new StringBuilder();
messageText2.Append(DateTime.Now.ToString() + ": Exception caught tracing server: " + SSASserver + ", retry " + _RetryCounter + " of " + Properties.Settings.Default.RestartRetries
+ ". Pausing for " + Properties.Settings.Default.RestartDelayMinutes + " minute(s) then restarting automatically"
).AppendLine();
WriteLog(messageText2.ToString());
EventLog.WriteEntry(this.ServiceName, messageText2.ToString(), EventLogEntryType.Warning);
System.Threading.Thread.Sleep(new TimeSpan(0, Properties.Settings.Default.RestartDelayMinutes, 0));
}
else
{
WriteLog(DateTime.Now.ToString() + ": Exceeded the number of allowed retries for server: " + SSASserver);
}
}
//if this one trace exceeded the number of retries so stop the service and stop all traces
Stop();
}