public MemoryClerks(HttpContext context, SQLServerInfo sqlServerInfo)
{
this.SQLServerInfo = sqlServerInfo;
logger = context.RequestServices.GetRequiredService <ILogger <MemoryClerks> >();
TSQLQuery = TSQLStore.ProbeTSQL("memory_clerks", this.SQLServerInfo);
}
public WaitStats(HttpContext context, SQLServerInfo sqlServerInfo)
{
this.SQLServerInfo = sqlServerInfo;
logger = context.RequestServices.GetRequiredService <ILogger <WaitStats> >();
// in theory we should guard this code against concurrent access. It is possibile that
// two or more threads will work on the same HashSet concurrently because the all
// tested Waits == null to be true. In practice this will never happen as long as it's only
// one Prometheus calling our method. For now we will avoid the cost of a mutex, if bugs
// arise it will be included here.
if (Waits == null)
{
Waits = new HashSet <string>();
foreach (var file in Program.CommandLineOptions.WaitStats.TemplateFiles)
{
System.IO.FileInfo fi = new System.IO.FileInfo(file);
logger.LogInformation($"Loading wait stats template to include from {fi.FullName}...");
using (System.IO.StreamReader sr = new System.IO.StreamReader(new System.IO.FileStream(fi.FullName,
System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.Read)))
{
string str;
while ((str = sr.ReadLine()) != null)
{
if (str.StartsWith("#"))
{
continue;
}
Waits.Add(str);
}
}
}
string tsql = TSQLStore.ProbeTSQL("wait_stats", this.SQLServerInfo);
logger.LogDebug($"Probing wait statistics for {this.SQLServerInfo.Name}, version {this.SQLServerInfo.Version} returned {tsql}");
System.Text.StringBuilder sb = new System.Text.StringBuilder();
bool fFirst = true;
foreach (string str in Waits)
{
if (!fFirst)
{
sb.Append(",\n");
}
else
{
fFirst = false;
}
sb.Append($" N'{str}'");
}
TSQLQuery = tsql.Replace("%%WAITS%%", sb.ToString());
}
}
public async Task <string> QueryAndSerializeData()
{
using (SqlConnection conn = new SqlConnection(this.SQLServerInfo.ConnectionString))
{
logger.LogDebug($"About to open connection to {this.SQLServerInfo.Name}");
await conn.OpenAsync();
System.Text.StringBuilder sb = new System.Text.StringBuilder();
string tsql = TSQLStore.ProbeTSQL("worker_threads", this.SQLServerInfo);
logger.LogDebug($"Probing worker_threads for {this.SQLServerInfo.Name}, version {this.SQLServerInfo.Version} returned {tsql}");
using (SqlCommand cmd = new SqlCommand(tsql, conn))
{
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
int parent_node_id = reader.GetInt32(0);
int scheduler_id = reader.GetInt32(1);
int cpu_id = reader.GetInt32(2);
for (int i = 3; i < reader.FieldCount; i++)
{
sb.Append($"sql_os_schedulers_{reader.GetName(i)}{{instance=\"{this.SQLServerInfo.Name}\", parent_node_id=\"{parent_node_id}\", scheduler_id=\"{scheduler_id}\", cpu_id=\"{cpu_id}\"}} ");
if (reader.GetFieldType(i) == typeof(bool))
{
sb.Append($"{(reader.GetBoolean(i) == true ? "1" : "0")}\n");
}
else if (reader.GetFieldType(i) == typeof(Int32))
{
sb.Append($"{reader.GetInt32(i)}\n");
}
else
{
sb.Append($"{reader.GetInt64(i)}\n");
}
}
}
}
}
return(sb.ToString());
}
}
public string QueryAndSerializeData()
{
if (EnabledCounters == null)
{
throw new Exception("EnabledCounters must not be null at this phase.");
}
else
{
using (SqlConnection conn = new SqlConnection(this.SQLServerInfo.ConnectionString))
{
logger.LogDebug($"About to open connection to {this.SQLServerInfo.Name}");
conn.Open();
System.Text.StringBuilder sb = new System.Text.StringBuilder();
string tsql = TSQLStore.ProbeTSQL("performance_counters", this.SQLServerInfo);
logger.LogDebug($"Probing performance counters for {this.SQLServerInfo.Name}, version {this.SQLServerInfo.Version} returned {tsql}");
using (SqlCommand cmd = new SqlCommand(tsql, conn))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string objectName = reader.GetString(0).Trim();
// this will strip the prefix because is dependent on the instance
// name (like MSSQL$SQL17:Locks, MSSQL$SQL17:Databases etc...)
// and we will store the info in the instance attribute instead
int idx = objectName.IndexOf(":");
if (idx != -1)
{
objectName = objectName.Substring(idx + 1);
}
string counterName = reader.GetString(1).Trim();
string?instanceName = reader.IsDBNull(2) ? null : reader.GetString(2).Trim();
long cntr_value = reader.GetInt64(3);
string key = KeyFromObjectNameAndCounterName(objectName, counterName);
GrafanaPerformanceCounter gpc;
if (_dGraf.TryGetValue(key, out gpc))
{
// skip this is it's not in the enabled performance counters
// as specified by the template files configured.
if (!EnabledCounters.Contains(gpc.name))
{
continue;
}
string gpcName = $"sql_pc_{gpc.name}";
//sb.Append($"# TYPE {gpcName} {gpc.type}\n");
string completeName = $"{gpcName}{{instance=\"{this.SQLServerInfo.Name}\"";
if (!string.IsNullOrEmpty(instanceName))
{
completeName += $", counter_instance=\"{instanceName}\"";
}
completeName += "}";
sb.Append($"{completeName} {cntr_value.ToString()}\n");
}
else
{
logger.LogWarning($"entry {key} not mapped in the mapping file! Ignored in the output");
}
}
}
}
return(sb.ToString());
}
}
}
