private void CheckPerformanceItem(ServerInfo server, bool isServer)
{
if (server.IsAzure)
{
return;
}
PerformanceRecord record;
if (isServer)
{
var sql = @"declare @now datetime
declare @cpu_busy bigint
declare @io_busy bigint
declare @idle bigint
declare @pack_received bigint
declare @pack_sent bigint
declare @pack_errors bigint
declare @connections bigint
declare @total_read bigint
declare @total_write bigint
declare @total_errors bigint
declare @oldcpu_busy bigint /* used to see if DataServer has been rebooted */
declare @interval bigint
declare @mspertick bigint /* milliseconds per tick */
/*
** Set @mspertick. This is just used to make the numbers easier to handle
** and avoid overflow.
*/
select @mspertick = convert(int, @@timeticks / 1000.0)
/*
** Get current monitor values.
*/
select
@now = getdate(),
@cpu_busy = @@cpu_busy,
@io_busy = @@io_busy,
@idle = @@idle,
@pack_received = @@pack_received,
@pack_sent = @@pack_sent,
@connections = @@connections,
@pack_errors = @@packet_errors,
@total_read = @@total_read,
@total_write = @@total_write,
@total_errors = @@total_errors
/*
** Check to see if DataServer has been rebooted. If it has then the
** value of @@cpu_busy will be less than the value of spt_monitor.cpu_busy.
** If it has update spt_monitor.
*/
select @oldcpu_busy = cpu_busy
from master.dbo.spt_monitor
if @oldcpu_busy > @cpu_busy
begin
update master.dbo.spt_monitor
set
lastrun = @now,
cpu_busy = @cpu_busy,
io_busy = @io_busy,
idle = @idle,
pack_received = @pack_received,
pack_sent = @pack_sent,
connections = @connections,
pack_errors = @pack_errors,
total_read = @total_read,
total_write = @total_write,
total_errors = @total_errors
end
/*
** Now print out old and new monitor values.
*/
set nocount on
select @interval = datediff(ss, lastrun, @now)
from master.dbo.spt_monitor
/* To prevent a divide by zero error when run for the first
** time after boot up
*/
if @interval = 0
select @interval = 1
select last_run = lastrun, current_run = @now, seconds = @interval,
cpu_busy_total = convert(bigint, (@cpu_busy / 1000.0 * @mspertick)),
cpu_busy_current = convert(bigint, ((@cpu_busy - cpu_busy)
/ 1000.0 * @mspertick)),
cpu_busy_percentage = convert(bigint, (((@cpu_busy - cpu_busy)
/ 1000.0 * @mspertick) / @interval * 100.0)),
io_busy_total = convert(bigint, (@io_busy / 1000 * @mspertick)),
io_busy_current = convert(bigint, ((@io_busy - io_busy)
/ 1000.0 * @mspertick)),
io_busy_percentage = convert(bigint, (((@io_busy - io_busy)
/ 1000.0 * @mspertick) / @interval * 100.0)),
idle_total = convert(bigint, (convert(bigint,@idle) / 1000.0 * @mspertick)),
idle_current = convert(bigint, ((@idle - idle)
/ 1000.0 * @mspertick)),
idle_percentage = convert(bigint, (((@idle - idle)
/ 1000.0 * @mspertick) / @interval * 100.0)),
packets_received_total = @pack_received,
packets_received_current = @pack_received - pack_received,
packets_sent_total = @pack_sent,
packets_sent_current = @pack_sent - pack_sent,
packet_errors_total = @pack_errors,
packet_errors_current = @pack_errors - pack_errors,
total_read = @total_read,
current_read = @total_read - total_read,
total_write = @total_write,
current_write = @total_write - total_write,
total_errors = @total_errors,
current_errors = @total_errors - total_errors,
connections_total = @connections,
connections_current = @connections - connections
from master.dbo.spt_monitor
/*
** Now update spt_monitor
*/
update master.dbo.spt_monitor
set
lastrun = @now,
cpu_busy = @cpu_busy,
io_busy = @io_busy,
idle = @idle,
pack_received = @pack_received,
pack_sent = @pack_sent,
connections = @connections,
pack_errors = @pack_errors,
total_read = @total_read,
total_write = @total_write,
total_errors = @total_errors" ;
var serverInfo = SqlHelper.Query(sql, server);
var row = serverInfo.Rows[0];
record = new PerformanceRecord
{
Value1 = Convert.ToInt64(row["cpu_busy_current"]),
Value2 = Convert.ToInt64(row["io_busy_current"]),
Value3 = Convert.ToInt64(row["current_read"]),
Value4 = Convert.ToInt64(row["current_write"]),
Value5 = Convert.ToInt64(row["packets_received_current"]),
Value6 = Convert.ToInt64(row["packets_sent_current"]),
Value7 = Convert.ToInt64(row["connections_current"]),
Value8 = Convert.ToInt64(row["io_busy_total"]),
Value9 = Convert.ToInt64(row["cpu_busy_total"]),
Value10 = Convert.ToInt64(row["total_read"]),
Value11 = Convert.ToInt64(row["total_write"]),
Value12 = Convert.ToInt64(row["packets_received_total"]),
Value13 = Convert.ToInt64(row["packets_sent_total"]),
Value14 = Convert.ToInt64(row["connections_total"]),
};
OnUpdateServerInfo(record, server, isServer);
}
else
{
var data = QueryEngine.GetDatabaseIoInfo(server);
if (_lastPerformanceData != null)
{
for (var i = 0; i < 2; i++)
{
var row = data.Rows[i];
var last = _lastPerformanceData.Rows[i];
row["CurrentNumberReads"] = Convert.ToInt64(row["NumberReads"]) - Convert.ToInt64(last["NumberReads"]);
row["CurrentNumberWrites"] = Convert.ToInt64(row["NumberWrites"]) - Convert.ToInt64(last["NumberWrites"]);
}
}
var db = data.Rows[0];
record = new PerformanceRecord
{
Value1 = Convert.ToInt64(db["NumberReads"]),
Value2 = Convert.ToInt64(db["BytesRead"]),
Value3 = Convert.ToInt64(db["NumberWrites"]),
Value4 = Convert.ToInt64(db["BytesWritten"]),
Value5 = Convert.ToInt64(db["CurrentNumberReads"]),
Value6 = Convert.ToInt64(db["CurrentNumberWrites"]),
Value13 = Convert.ToInt64(db["IsStall"]),
Value16 = Convert.ToDateTime(db["StartDate"]),
Value15 = Convert.ToInt64(db["FileCount"])
};
var hasLog = data.Rows.Count > 1;
if (hasLog)
{
var log = data.Rows[1];
record.Value7 = Convert.ToInt64(log["NumberReads"]);
record.Value8 = Convert.ToInt64(log["BytesRead"]);
record.Value9 = Convert.ToInt64(log["NumberWrites"]);
record.Value10 = Convert.ToInt64(log["BytesWritten"]);
record.Value11 = Convert.ToInt64(log["CurrentNumberReads"]);
record.Value12 = Convert.ToInt64(log["CurrentNumberWrites"]);
record.Value14 = Convert.ToInt64(log["IsStall"]);
record.Value15 += Convert.ToInt64(log["FileCount"]);
}
OnUpdateServerInfo(record, server, isServer);
_lastPerformanceData = data;
}
}
private void CheckMonitorItems()
{
try
{
Settings.Instance.MonitorItems.Where(i => i.IsEnabled).ForEach(item =>
{
var server = Settings.Instance.FindServer(item.Server);
if (server != null)
{
server.Database = "master";
switch (item.AlertType)
{
case AlertTypes.Sql:
switch (item.CondictionType)
{
case 0:
case 1:
var sessions = SqlHelper.Query(QueryEngine.SqlProcesses, server);
sessions.Rows.Cast <DataRow>().ForEach(r =>
{
var id = r["spid"].ToString();
var sql = QueryEngine.GetSessionSql(id, server);
if (IsTextMatch(sql, item.Target))
{
bool result;
if (item.CondictionType == 1)
{
var span = ((DateTime)r["last_batch_end"]).Subtract((DateTime)r["last_batch_begin"]);
result = span > TimeSpan.FromSeconds(Convert.ToDouble(item.CondictionValue));
sql = span.ToString() + ", " + sql;
}
else
{
result = true;
}
if (result)
{
var key = id + "|" + r["hostname"].ToString() + "|" + r["hostprocess"].ToString();
ShowAlert(item, key, sql);
}
}
});
break;
case 2:
var tasks = SqlHelper.Query(QueryEngine.SqlWaitingTasks, server);
tasks.Rows.Cast <DataRow>().ForEach(r =>
{
var id = r["[Blocking Session Id]"].ToString();
if (!string.IsNullOrEmpty(id))
{
var sql = QueryEngine.GetSessionSql(id, server);
if (string.IsNullOrEmpty(sql))
{
sql = "session id = " + id;
}
ShowAlert(item, sql, sql);
}
});
break;
case 3:
var tableServer = server.Clone();
tableServer.Database = item.Target;
var count = QueryEngine.GetTableInfo(tableServer, "AND so.name='" + item.CondictionValue + "'");
if (count != null && Convert.ToInt64(count["RowCount"]) == 0)
{
ShowAlert(item, item.Target + QueryEngine.Dot + item.CondictionValue, item.Target + QueryEngine.Dot + item.CondictionValue);
}
break;
default:
break;
}
break;
case AlertTypes.Server:
switch (item.CondictionType)
{
case 0:
try
{
SqlHelper.ExecuteScalar("SELECT @@version", server);
}
catch (Exception ex)
{
ShowAlert(item, server.Server, ex.Message);
}
break;
default:
break;
}
break;
//case AlertTypes.CPU:
// var cpu = CurrentCpuUsage;
// if (cpu > item.CondictionValue)
// ShowAlert(item, cpu.ToString());
// break;
//case AlertTypes.Memory:
// var mem = AvailableRAM;
// if (mem > item.CondictionValue)
// ShowAlert(item, mem.ToString());
// break;
//case AlertTypes.Diskspace:
// break;
default:
break;
}
}
});
}
catch (Exception ex)
{
OnError(ex);
}
}
public void CheckServerHealth()
{
if (RequestHealthServer != null && Health != null)
{
var e = new ServerInfoEventArgs();
RequestHealthServer(this, e);
var healthItems = new List <HealthItem>();
if (!e.Cancel && e.Server != null && !string.IsNullOrEmpty(e.Server.Server))
{
var isValid = true;
try
{
var version = QueryEngine.GetServerVersion(e.Server);
}
catch (Exception)
{
isValid = false;
}
if (isValid)
{
var isAlert = false;
//memory
long physicalMemory;
long availableMemory;
var serverState = e.Server as ServerState;
if (!serverState.IsAzure)
{
QueryEngine.GetMemoryInfo(e.Server, out physicalMemory, out availableMemory);
}
else
{
physicalMemory = 0;
availableMemory = 0;
}
var memoryMb = SqlHelper.ExecuteScalar("SELECT (cntr_value/1024.0) FROM sys.dm_os_performance_counters WHERE counter_name = 'Total Server Memory (KB)'", e.Server);
if (memoryMb != null)
{
var memory = Convert.ToInt32(memoryMb);
isAlert = availableMemory * 1.0 / physicalMemory < Settings.Instance.FreeMemoryRatio / 100.0;
healthItems.Add(new HealthItem {
Category = HealthCategoryServer, HealthType = HealthTypes.ServerMemory, CurrentValue = availableMemory.ToString() + " " + Utils.SizeMb, ReferenceValue = memory + " " + Utils.SizeMb, ItemName = "Server Memory", Description = "Free/DB Used", IsAlert = isAlert
});
}
//cpu
int cpuSqlProcess;
int cpuSystemIdle;
int cpuOtherProcesses;
QueryEngine.GetCpuInfo(e.Server, out cpuSqlProcess, out cpuSystemIdle, out cpuOtherProcesses);
isAlert = cpuSystemIdle < Settings.Instance.FreeCpuRatio;
healthItems.Add(new HealthItem {
Category = HealthCategoryServer, HealthType = HealthTypes.ServerCpu, CurrentValue = cpuSystemIdle.ToString() + " %", ReferenceValue = cpuSqlProcess + " %", ItemName = "Server CPU", Description = "Free/DB Used", IsAlert = isAlert
});
//disk space
var diskSpaces = QueryEngine.GetDiskSpace(e.Server);
diskSpaces.Where(s => s.Value.Value > 0).ForEach(s =>
{
isAlert = s.Value.Key < s.Value.Value / 100 * Settings.Instance.DatabaseDiskFreeSpaceRatio;
healthItems.Add(new HealthItem {
Category = HealthCategoryServer, HealthType = HealthTypes.ServerSpace, CurrentValue = s.Value.Key.ToString() + " " + Utils.SizeMb, ReferenceValue = s.Value.Value + " " + Utils.SizeMb, ItemName = "Disk Space (" + s.Key + ")", Description = "Free/DB Used", IsAlert = isAlert
});
});
if (!e.Server.IsAzure)
{
//locked objects
var lockedObjects = SqlHelper.Query(QueryEngine.SqlLockedObjects, e.Server);
lockedObjects.Rows.Cast <DataRow>().ForEach(r =>
{
isAlert = false;
healthItems.Add(new HealthItem {
Category = HealthCategoryProcess, HealthType = HealthTypes.LockedObjects, CurrentValue = r.Field <string>("SchemaName") + "." + r.Field <string>("ObjectName"), ReferenceValue = r.Field <string>("DatabaseName"), ItemName = "Locked Object", Description = r.Field <string>("ProgramName"), IsAlert = isAlert
});
});
}
//blocked processes
var blockedProcesses = SqlHelper.Query(QueryEngine.SqlWaitingTasks + " WHERE blocking_session_id IS NOT NULL", e.Server);
blockedProcesses.Rows.Cast <DataRow>().ForEach(r =>
{
isAlert = true;
healthItems.Add(new HealthItem {
Category = HealthCategoryProcess, HealthType = HealthTypes.BlockedProcess, CurrentValue = r["Session Id"].ToString(), ReferenceValue = r["Blocking Session Id"].ToString(), ItemName = "Blocked Process", Description = "Blocked/Blocking", IsAlert = isAlert
});
});
//db performance
var databaseStalls = QueryEngine.GetDatabaseStall(e.Server);
databaseStalls.ForEach(db =>
{
isAlert = db.IsExceeded;
healthItems.Add(new HealthItem {
Category = HealthCategoryDatabase, HealthType = HealthTypes.DatabaseStall, CurrentValue = db.Max + " " + Utils.TimeMs, ReferenceValue = QueryEngine.DbStallThreshold + " " + Utils.TimeMs, ItemName = "DB Perf (" + db.Database + ")", Description = "Higher = worse", IsAlert = isAlert
});
});
//db/log space
var dbLogSpaces = QueryEngine.GetDbLogSpace(e.Server);
dbLogSpaces.Where(s => !s.Value.Item3).ForEach(s =>
{
healthItems.Add(new HealthItem {
Category = HealthCategoryDatabase, HealthType = HealthTypes.DatabaseLogSpace, CurrentValue = s.Value.Item1.ToString() + " " + Utils.SizeMb, ReferenceValue = s.Value.Item2 + " " + Utils.SizeMb, ItemName = "DB/Log Space (" + s.Key + ")", Description = "Log/DB", IsAlert = true
});
});
}
}
Health(this, new HealthEventArgs {
Result = healthItems
});
}
}