private void SetPerformanceInfo(PerformanceRecord record)
{
_isLoading = true;
if (_objectMode == ObjectModes.Server)
{
var cpuBusyCurrent = record.Value1;
var ioBusyCurrent = record.Value2;
var currentRead = record.Value3;
var currentWrite = record.Value4;
var connectionsCurrent = record.Value7;
var ioBusyTotal = record.Value8;
txtPerformanceIO.Text = string.Format("{0} / {1}", ioBusyTotal, ioBusyCurrent);
var cpuBusyTotal = record.Value9;
txtPerformanceCPU.Text = string.Format("{0} / {1}", cpuBusyTotal, cpuBusyCurrent);
var totalRead = record.Value10;
txtPerformanceRead.Text = string.Format("{0} / {1}", totalRead, currentRead);
var totalWrite = record.Value11;
txtPerformanceWrite.Text = string.Format("{0} / {1}", totalWrite, currentWrite);
var packetsReceivedTotal = record.Value12;
var packetsSentTotal = record.Value13;
txtPerformancePackets.Text = string.Format("{0} / {1}", packetsReceivedTotal, packetsSentTotal);
var connectionsTotal = record.Value14;
txtPerformanceConnections.Text = string.Format("{0} / {1}", connectionsTotal, connectionsCurrent);
}
else
{
var dbIsStall = record.Value13;
var dbNumberReads = record.Value1;
var dbBytesRead = record.Value2;
var dbNumberWrites = record.Value3;
var dbBytesWritten = record.Value4;
var dbStartDate = record.Value16;
var dbFileCount = record.Value15;
var logIsStall = record.Value14;
var logNumberReads = record.Value7;
var logBytesRead = record.Value8;
var logNumberWrites = record.Value9;
var logBytesWritten = record.Value10;
txtPerformanceCPU.Text = dbStartDate.ToString();
txtPerformanceIO.Text = string.Format("{0} / {1}", dbIsStall, logIsStall);
ResetPerformanceIo();
if (dbIsStall >= QueryEngine.DbStallThreshold
|| logIsStall >= QueryEngine.DbStallThreshold)
{
txtPerformanceIO.BackColor = Color.Red;
ttInfo.SetToolTip(txtPerformanceIO, "Potential performance bottleneck due to hard disk IO delay, check using database analysis.");
llPerformanceIO.Enabled = true;
}
txtPerformanceRead.Text = string.Format("{0} / {1}", dbNumberReads, Utils.FormatSize(dbBytesRead));
txtPerformanceWrite.Text = string.Format("{0} / {1}", dbNumberWrites, Utils.FormatSize(dbBytesWritten));
txtPerformancePackets.Text = string.Format("{0} / {1}", logNumberReads, Utils.FormatSize(logBytesRead));
txtPerformanceConnections.Text = string.Format("{0} / {1}", logNumberWrites, Utils.FormatSize(logBytesWritten));
}
if (record.Value16 > DateTime.MinValue)
dtpPerformanceStartDate.Value = record.Value16;
_isLoading = false;
}
private void AddPerformanceHistoryRecord(PerformanceRecord record, DateTime date, DateTime min, DateTime max)
{
if (_objectMode == ObjectModes.Server)
{
var cpuBusyCurrent = record.Value1;
var ioBusyCurrent = record.Value2;
var currentRead = record.Value3;
var currentWrite = record.Value4;
var packetsReceivedCurrent = record.Value5;
var packetsSentCurrent = record.Value6;
var connectionsCurrent = record.Value7;
Series cpu;
Series io;
Series read;
Series write;
Series packetsSent;
Series packetsReceived;
Series connections;
if (chPerformance.Series.Count == 0)
{
chPerformance.ChartAreas[0].AxisX.Minimum = min.ToOADate();
chPerformance.ChartAreas[0].AxisX.Maximum = max.ToOADate();
cpu = AddPerformanceSerie("CPU", Color.Green);
io = AddPerformanceSerie("IO", Color.Blue);
read = AddPerformanceSerie("Read", Color.Red);
write = AddPerformanceSerie("Write", Color.Salmon);
packetsReceived = AddPerformanceSerie("Packet Received", Color.Tan);
packetsSent = AddPerformanceSerie("Packet Sent", Color.Pink);
connections = AddPerformanceSerie("Connections", Color.Yellow);
}
else
{
cpu = chPerformance.Series[0];
io = chPerformance.Series[1];
read = chPerformance.Series[2];
write = chPerformance.Series[3];
packetsReceived = chPerformance.Series[4];
packetsSent = chPerformance.Series[5];
connections = chPerformance.Series[6];
}
AddPerformanceValue(date, cpu, cpuBusyCurrent);
AddPerformanceValue(date, io, ioBusyCurrent);
AddPerformanceValue(date, read, currentRead);
AddPerformanceValue(date, write, currentWrite);
AddPerformanceValue(date, packetsReceived, packetsReceivedCurrent);
AddPerformanceValue(date, packetsSent, packetsSentCurrent);
AddPerformanceValue(date, connections, connectionsCurrent);
}
else
{
var dbCurrentNumberReads = record.Value5;
var dbCurrentNumberWrites = record.Value6;
var logCurrentNumberReads = record.Value11;
var logCurrentNumberWrites = record.Value12;
Series dbReads;
Series dbWrites;
Series logReads;
Series logWrites;
if (chPerformance.Series.Count == 0)
{
chPerformance.ChartAreas[0].AxisX.Minimum = min.ToOADate();
chPerformance.ChartAreas[0].AxisX.Maximum = max.ToOADate();
dbReads = AddPerformanceSerie("DB Read", Color.Red);
dbWrites = AddPerformanceSerie("DB Write", Color.Salmon);
logReads = AddPerformanceSerie("Log Read", Color.Green);
logWrites = AddPerformanceSerie("Log Write", Color.Blue);
}
else
{
dbReads = chPerformance.Series[0];
dbWrites = chPerformance.Series[1];
logReads = chPerformance.Series[2];
logWrites = chPerformance.Series[3];
}
AddPerformanceValue(date, dbReads, dbCurrentNumberReads);
AddPerformanceValue(date, dbWrites, dbCurrentNumberWrites);
AddPerformanceValue(date, logReads, logCurrentNumberReads);
AddPerformanceValue(date, logWrites, logCurrentNumberWrites);
}
}
private void OnUpdateServerInfo(PerformanceRecord Record, ServerInfo server, bool isServer)
{
UpdateServerInfo(this, new PerformanceRecordEventArgs() { Data = Record, Server = server });
if (Settings.Instance.LogHistory)
{
var record = new HistoryRecord(Record)
{
Date = DateTime.Now.ToString(),
Key = History.GetKey(server, isServer)
};
History.AddRecords(new List<HistoryRecord> { record });
}
}
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;
}
}
internal HistoryRecord(PerformanceRecord record)
{
//damn, it's crazy! I wanted to use reflection...
Value1 = record.Value1;
Value2 = record.Value2;
Value3 = record.Value3;
Value4 = record.Value4;
Value5 = record.Value5;
Value6 = record.Value6;
Value7 = record.Value7;
Value8 = record.Value8;
Value9 = record.Value9;
Value10 = record.Value10;
Value11 = record.Value11;
Value12 = record.Value12;
Value13 = record.Value13;
Value14 = record.Value14;
}
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;
}
}
