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)
{
TimeSpan span = ((DateTime)r["last_batch_end"]).Subtract((DateTime)r["last_batch_begin"]);
result = span > TimeSpan.FromSeconds(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;
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);
}
}
private void CheckPerformanceItem(ServerInfo Server, bool IsServer)
{
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];
var cpu_busy_current = Convert.ToInt64(row["cpu_busy_current"]);
var io_busy_current = Convert.ToInt64(row["io_busy_current"]);
var current_read = Convert.ToInt64(row["current_read"]);
var current_write = Convert.ToInt64(row["current_write"]);
var packets_received_current = Convert.ToInt64(row["packets_received_current"]);
var packets_sent_current = Convert.ToInt64(row["packets_sent_current"]);
var connections_current = Convert.ToInt64(row["connections_current"]);
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 (int 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"])
};
bool 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;
}
}