public static string GetSqlSelect(TraceColumns columns)
{
List <TraceColumns> list = ToArray(columns);
StringBuilder ret = new StringBuilder();
foreach (TraceColumns field in list)
{
TraceFieldInfo info = Get(field);
ret.Append(ret.Length > 0 ? ", " : "").Append(info.Select);
}
return(ret.ToString());
}
public TraceGroupsReport <TKey> ReadGroupsReport <TKey>(TraceColumns groupingField)
{
if ((_columns & groupingField) == 0)
{
throw new ArgumentException("Axis field " + groupingField + " is not included in trace", "groupingField");
}
TraceFieldInfo info = TraceFieldInfo.Get(groupingField);
string sql = string.Format(
SQL_SELECT_GROUPS,
info.GroupExpression);
using (SqlConnection con = new SqlConnection(_connectionString))
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.Parameters.Add("@file", SqlDbType.NVarChar).Value = _traceFile + ".trc";
using (SqlDataReader rdr = cmd.ExecuteReader())
{
TraceGroupsReport <TKey> ret = new TraceGroupsReport <TKey>();
while (rdr.Read())
{
object rawKey = rdr.IsDBNull(0) ? null : rdr.GetValue(0);
int count = rdr.GetInt32(1);
SqlCounters counters = ReadCounters(rdr, 2);
if (counters != null)
{
counters.Requests = count;
TKey key = (TKey)rawKey;
SqlGroupCounters <TKey> group =
new SqlGroupCounters <TKey>()
{
Group = key, Count = count, Counters = counters
};
ret.Add(key, group);
}
}
return(ret);
}
}
}
}
public void Start(string connectionString, string tracePath, TraceColumns columns, params TraceRowFilter[] rowFilters)
{
_connectionString = connectionString;
_traceFile = Path.Combine(tracePath, "trace_" + Guid.NewGuid().ToString("N"));
_columns = columns;
// For non-local sql server it doesn't work
if (_PrevCreatedDirectory != tracePath)
{
if (!Directory.Exists(tracePath))
{
Directory.CreateDirectory(Path.GetDirectoryName(_traceFile));
}
_PrevCreatedDirectory = tracePath;
}
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
StringBuilder sqlSetFields = new StringBuilder();
foreach (TraceColumns field in TraceFieldInfo.ToArray(columns))
{
TraceFieldInfo info = TraceFieldInfo.Get(field);
sqlSetFields.AppendFormat(SQL_SET_TRACE_COLUMN, info.SqlId, field);
}
List <SqlParameter> parameters = new List <SqlParameter>();
List <TraceColumns> rowFilterColumns = new List <TraceColumns>();
TraceRowFilter[] distinctRowFilter = TraceRowFilter.GetDistinct(rowFilters);
foreach (TraceRowFilter rowFilter in distinctRowFilter)
{
TraceFieldInfo info = TraceFieldInfo.Get(rowFilter.Column);
string pName = "@filter_" + info.GroupExpression + "_" + parameters.Count;
string comparisonOperator = rowFilter.StrictEquality ? "0" : "6";
object comparisonValue = rowFilter.Value;
if (!rowFilter.StrictEquality && comparisonValue != null)
{
comparisonValue = "%" + Convert.ToString(comparisonValue).Replace("'", "''") + "%";
}
sqlSetFields.AppendFormat("exec sp_trace_setfilter @TRACE, {0}, 0, {1}, {2} -- {3}",
info.SqlId,
comparisonOperator,
pName,
info.GroupExpression);
sqlSetFields.AppendLine();
SqlParameter p = new SqlParameter(pName, comparisonValue);
parameters.Add(p);
if (rowFilterColumns.Contains(rowFilter.Column))
{
throw new ArgumentException("Duplicate row filter column " + rowFilter.Column, "rowFilters");
}
rowFilterColumns.Add(rowFilter.Column);
}
string sqlCmd = SQL_START1_TRACE + sqlSetFields + SQL_START2_TRACE;
// Console.WriteLine($"TRACE ON {connectionString}");
using (SqlCommand cmd = new SqlCommand(sqlCmd, con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@file", SqlDbType.NVarChar).Value = _traceFile;
var maxFileSize = Math.Max(8, MaxFileSize);
cmd.Parameters.Add("@MaxFileSize", SqlDbType.BigInt).Value = maxFileSize;
cmd.Parameters.AddRange(parameters.ToArray());
_traceId = (int)cmd.ExecuteScalar();
// PInvoke.DeleteFileOnReboot(_traceFile + ".trc");
}
}
_NeedStop = true;
}
public TraceDetailsReport ReadDetailsReport()
{
using (SqlConnection con = new SqlConnection(_connectionString))
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
List <SqlStatementCounters> ret = new List <SqlStatementCounters>();
string sqlSelect = TraceFieldInfo.GetSqlSelect(_columns);
var sqlColumns = sqlSelect == "" ? SQL_SELECT_COUNTERS : sqlSelect + ", " + SQL_SELECT_COUNTERS;
var sqlErrorColumn = "Cast(CASE WHEN EventClass = 33 Then Error Else Null END As INT) Error, Cast(CASE WHEN EventClass = 33 Then TextData Else Null END As ntext) ErrorText, SPID SPID_For_Error";
// TODO: Always read SPID
sqlColumns = sqlErrorColumn + ", " + sqlColumns;
string sqlCmd = string.Format(SQL_SELECT_DETAILS, sqlColumns);
using (SqlCommand cmd = new SqlCommand(sqlCmd, con))
{
cmd.Parameters.Add("@file", SqlDbType.NVarChar).Value = _traceFile + ".trc";
using (SqlDataReader rdr = cmd.ExecuteReader())
{
ErrorsBySpid errors = new ErrorsBySpid();
while (rdr.Read())
{
int?spid = rdr.IsDBNull(2) ? (int?)null : rdr.GetInt32(2);
// if (spid == null) throw new InvalidOperationException("SPID column #1 cannot be null");
SqlStatementCounters item = new SqlStatementCounters();
// Error of Exception event follows sql statement or sp row
int? tempSqlErrorNumber = rdr.IsDBNull(0) ? (int?)null : rdr.GetInt32(0);
string tempErrorText = rdr.IsDBNull(1) ? (string)null : rdr.GetString(1);
if (tempSqlErrorNumber.GetValueOrDefault() != 0)
{
if (spid != null)
{
errors.SetErrorBySpid(spid.Value, tempSqlErrorNumber.Value, tempErrorText);
}
continue;
}
int colNum = 3;
if ((_columns & TraceColumns.Application) != 0)
{
item.Application = rdr.IsDBNull(colNum) ? null : rdr.GetString(colNum);
colNum++;
}
if ((_columns & TraceColumns.ClientHost) != 0)
{
item.ClientHost = rdr.IsDBNull(colNum) ? null : rdr.GetString(colNum);
colNum++;
}
if ((_columns & TraceColumns.ClientProcess) != 0)
{
item.ClientProcess = rdr.IsDBNull(colNum) ? 0 : rdr.GetInt32(colNum);
colNum++;
}
if ((_columns & TraceColumns.Database) != 0)
{
item.Database = rdr.IsDBNull(colNum) ? null : rdr.GetString(colNum);
colNum++;
}
if ((_columns & TraceColumns.Login) != 0)
{
item.Login = rdr.IsDBNull(colNum) ? null : rdr.GetString(colNum);
colNum++;
}
if ((_columns & TraceColumns.ServerProcess) != 0)
{
item.ServerProcess = rdr.IsDBNull(colNum) ? 0 : rdr.GetInt32(colNum);
colNum++;
}
if ((_columns & TraceColumns.Sql) != 0)
{
item.SpName = rdr.IsDBNull(colNum) ? null : rdr.GetString(colNum);
colNum++;
item.Sql = rdr.IsDBNull(colNum) ? null : rdr.GetString(colNum);
colNum++;
}
item.Counters = ReadCounters(rdr, colNum);
if (item.Counters != null)
{
var errorBySpid = spid.HasValue ? errors.GetErrorBySpid(spid.Value) : null;
int?error = errorBySpid == null ? (int?)null : errorBySpid.Error;
if (error.GetValueOrDefault() != 0)
{
item.SqlErrorCode = error;
item.SqlErrorText = errorBySpid?.ErrorText;
}
if (spid.HasValue)
{
errors.SetErrorBySpid(spid.Value, 0, null);
}
ret.Add(item);
}
}
}
}
return(new TraceDetailsReport(_columns, ret));
}
}
