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)); } }