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