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();
}
}
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) { }
}
static void Main(string[] args)
{
init();
// Reading events
while (reader.Read())
{
try
{
recordHeartBeat();
if (mySPID != (Convert.ToInt32(readerParam("SPID"))))
{
sqlSaveStatementHistory();
Console.WriteLine("mySPID: " + mySPID.ToString());
Console.WriteLine("Event : " + reader["EventClass"]);
Console.WriteLine("SPID : " + reader["SPID"]);
Console.WriteLine("Login : "******"NTUserName"]);
Console.WriteLine("Object: " + reader["LoginName"]);
Console.WriteLine("Text : " + reader["TextData"]);
Console.WriteLine();
}
Console.Write("Skipping " + mySPID.ToString());
Application.DoEvents();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
} // main
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);
}
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();
}
