public UserQuery(string query, ServerInfo server)
: this()
{
_server = server.Clone();
rtbSQL.Font = Monitor.Instance.SetFont();
Utils.SetTextBoxStyle(rtbSQL);
rtbSQL.Text = query;
}
public void Init(ObjectModes objectMode, ServerInfo server)
{
_isLoading = true;
_objectMode = objectMode;
_server = server;
if (_server != null)
chkAutoPerformance.Checked = Settings.Instance.PerformanceItems.Exists(p => p.Server == _server.Server
&& p.Database == _server.Database && p.IsServer == IsServer);
_isLoading = false;
StartEngine();
}
public ConnectionDialog(ServerInfo info)
: this()
{
if (info != null)
{
AuthType = info.AuthType;
Server = info.Server;
UserName = info.User;
Password = info.Password;
AuthType = info.AuthType;
}
}
internal static DataTable GetSpScripts(ServerInfo server)
{
var data = SqlHelper.Query(SqlObjectScripts + " WHERE s.type = 'P' ORDER BY s.name", server);
var result = data.Clone();
data.Rows.Cast<DataRow>().GroupBy(r => r.Field<string>("name")).ForEach(g =>
{
var text = new StringBuilder();
var row = result.NewRow();
var first = g.First();
row.ItemArray = first.ItemArray;
row["text"] = g.Aggregate(new StringBuilder(), (a, b) => a.Append("\r\n" + b.Field<string>("text")), (a) => a.Remove(0, 2).ToString());
result.Rows.Add(row);
});
return result;
}
internal void RemovePerformanceItem(ServerInfo server, bool isServer)
{
var item = FindPerformanceServer(server, isServer);
if (item != null)
PerformanceItems.Remove(item);
}
internal static ServerInfo GetServerInfo(ServerInfo server, string catalog)
{
return new ServerInfo { IsAzure = server.IsAzure, AuthType = server.AuthType, Server = server.Server, Database = catalog, User = server.User, Password = server.Password };
}
internal static DataTable GetSessions(ServerInfo server)
{
return SqlHelper.Query(SqlSessions, server);
}
internal static DataTable GetLockedObjects(short sessionId, ServerInfo server)
{
var sql = SqlLockedObjects + " AND l.request_session_id = " + sessionId;
return SqlHelper.Query(sql, server);
}
internal static void GetMemoryInfo(ServerInfo server, out long physicalMemory, out long availableMemory)
{
var version = GetServerVersion(server);
if (version > 9)
{
var info = SqlHelper.Query("SELECT * FROM sys.dm_os_sys_memory WITH (NOLOCK)", server);
var row = info.Rows[0];
physicalMemory = GetSizeLong(info, row, new Dictionary<string, int> { { "total_physical_memory_kb", 1 }, { "total_physical_memory_in_bytes", Utils.Size1K } });
physicalMemory = physicalMemory /= Utils.Size1K;
availableMemory = GetSizeLong(info, row, new Dictionary<string, int> { { "available_physical_memory_kb", 1 }, { "available_physical_memory_in_bytes", Utils.Size1K } });
availableMemory = availableMemory /= Utils.Size1K;
}
else
{
var info = SqlHelper.Query("SELECT * FROM sys.dm_os_sys_info WITH (NOLOCK)", server);
var row = info.Rows[0];
physicalMemory = GetSizeLong(info, row, new Dictionary<string, int> { { "physical_memory_kb", 1 }, { "physical_memory_in_bytes", Utils.Size1K } });
physicalMemory = physicalMemory /= Utils.Size1K;
availableMemory = GetSizeLong(info, row, new Dictionary<string, int> { { "committed_target_kb", 1 }, { "committed_target_in_bytes", Utils.Size1K } });
availableMemory = availableMemory /= Utils.Size1K;
}
//var virtualMemory = 0L;
//if (info.Columns.Contains("virtual_memory_kb"))
// virtualMemory = Convert.ToInt64(row["virtual_memory_kb"]);
//else if (info.Columns.Contains("virtual_memory_in_bytes"))
// virtualMemory = Convert.ToInt64(row["virtual_memory_in_bytes"]) / Utils.Size1K;
//virtualMemory /= Utils.Size1K;
}
internal static DataTable GetDatabaseIoInfo(ServerInfo server)
{
string sql;
if (server.IsAzure)
{
sql = "SELECT DB_ID() AS dbid, file_id AS fileid, physical_name AS filename FROM sys.database_files";
}
else
{
sql = string.Format(@"SELECT sys.databases.database_id AS dbid, sys.master_files.file_id AS fileid, sys.master_files.physical_name AS filename
FROM sys.master_files INNER JOIN sys.databases
ON sys.master_files.database_id = sys.databases.database_id
WHERE sys.databases.name = '{0}'", server.Database);
}
var dataFiles = SqlHelper.Query(sql, server);
var data = new DataTable();
data.Columns.Add("StartDate", typeof(DateTime));
data.Columns.Add("IsStall", typeof(double));
data.Columns.Add("IsReadStall", typeof(double));
data.Columns.Add("IsWriteStall", typeof(double));
data.Columns.Add("NumberReads", typeof(long));
data.Columns.Add("BytesRead", typeof(long));
data.Columns.Add("NumberWrites", typeof(long));
data.Columns.Add("BytesWritten", typeof(long));
data.Columns.Add("CurrentNumberReads", typeof(long));
data.Columns.Add("CurrentNumberWrites", typeof(long));
data.Columns.Add("IsLog", typeof(bool));
data.Columns.Add("FileCount", typeof(long));
data.Columns.Add("IoStallReadMS", typeof(long));
data.Columns.Add("IoStallWriteMS", typeof(long));
for (var i = 0; i < 2; i++)
{
var row = data.NewRow();
row.ItemArray = new object[] { DateTime.Now, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
row["IsLog"] = Convert.ToBoolean(i);
data.Rows.Add(row);
}
dataFiles.AsEnumerable().ForEach(d =>
{
var dbId = d["dbid"];
var fileId = d["fileid"];
var fileName = d["filename"].ToString();
var index = Path.GetExtension(fileName).ToLower() == ".ldf" ? 1 : 0;
//sys.dm_io_virtual_file_stats()
var fileStats = SqlHelper.Query(string.Format("SELECT DATEADD(ss, -1 * Timestamp/1000 , getdate()) AS StartDate, IoStallReadMS, IoStallWriteMS, NumberReads, BytesRead, NumberWrites, BytesWritten FROM ::fn_virtualfilestats({0}, {1})", dbId, fileId), server);
fileStats.AsEnumerable().ForEach(f =>
{
data.Rows[index]["StartDate"] = f["StartDate"];
data.Rows[index]["NumberReads"] = Convert.ToInt64(data.Rows[index]["NumberReads"]) + Convert.ToInt64(f["NumberReads"]);
if (Convert.ToInt64(data.Rows[index]["NumberReads"]) == 0)
{
data.Rows[index]["NumberReads"] = 1;
}
data.Rows[index]["BytesRead"] = Convert.ToInt64(data.Rows[index]["BytesRead"]) + Convert.ToInt64(f["BytesRead"]);
data.Rows[index]["NumberWrites"] = Convert.ToInt64(data.Rows[index]["NumberWrites"]) + Convert.ToInt64(f["NumberWrites"]);
if (Convert.ToDouble(data.Rows[index]["NumberWrites"]) == 0)
{
data.Rows[index]["NumberWrites"] = 1;
}
data.Rows[index]["BytesWritten"] = Convert.ToInt64(data.Rows[index]["BytesWritten"]) + Convert.ToInt64(f["BytesWritten"]);
data.Rows[index]["FileCount"] = Convert.ToInt64(data.Rows[index]["FileCount"]) + 1;
data.Rows[index]["IoStallReadMS"] = Convert.ToInt64(data.Rows[index]["IoStallReadMS"]) + Convert.ToInt64(f["IoStallReadMS"]);
data.Rows[index]["IoStallWriteMS"] = Convert.ToInt64(data.Rows[index]["IoStallWriteMS"]) + Convert.ToInt64(f["IoStallWriteMS"]);
data.Rows[index]["IsReadStall"] = Convert.ToDouble(data.Rows[index]["IoStallReadMS"]) / Convert.ToInt64(data.Rows[index]["NumberReads"]);
data.Rows[index]["IsWriteStall"] = Convert.ToDouble(data.Rows[index]["IoStallWriteMS"]) / Convert.ToInt64(data.Rows[index]["NumberWrites"]);
data.Rows[index]["IsStall"] = (Convert.ToDouble(data.Rows[index]["IoStallReadMS"]) + Convert.ToDouble(data.Rows[index]["IoStallWriteMS"])) / (Convert.ToInt64(data.Rows[index]["NumberReads"]) + Convert.ToInt64(data.Rows[index]["NumberWrites"]));
});
});
return(data);
}
internal static Dictionary<string, Tuple<long, long, bool>> GetDbLogSpace(ServerInfo server)
{
var result = new Dictionary<string, Tuple<long, long, bool>>();
var databases = GetDatabasesInfo(server);
//database data file & log file space
databases.AsEnumerable().ForEach(d =>
{
if (Convert.ToInt32(d["state"]) == 0)
{
var name = d["name"].ToString();
if (!SystemDatabases.Contains(name))
{
var database = GetDatabaseInfo(server, name);
var databaseSpace = new Dictionary<DatabaseFileTypes, long> { { DatabaseFileTypes.Data, 0 }, { DatabaseFileTypes.Log, 0 } };
database.AsEnumerable().ForEach(f =>
{
var key = (DatabaseFileTypes)Convert.ToInt32(f["type"]);
if (databaseSpace.ContainsKey(key))
databaseSpace[key] += Convert.ToInt64(Convert.ToDecimal(f["Size"]) / Utils.Size1K);
}
);
bool? shrink = null;
if (databaseSpace[DatabaseFileTypes.Log] > databaseSpace[DatabaseFileTypes.Data] / 100.0 * Settings.Instance.DatabaseDataLogSpaceRatio)
shrink = false;
else
{
var logSpaces = SqlHelper.Query("DBCC SQLPERF(LOGSPACE)", GetServerInfo(server, name));
var columnName = "DB Name";
if (!logSpaces.Columns.Contains(columnName))
columnName = "Database Name";
var logSpace = logSpaces.Select(string.Format("[{0}] = '{1}'", columnName, name));
if (logSpace.Length > 0)
{
var logSpacedUsed = Convert.ToDouble(logSpace[0]["Log Space Used (%)"]);
if (logSpacedUsed > Settings.Instance.DatabaseDataLogSpaceRatio)
shrink = true;
}
}
if (shrink != null)
result.Add(name, new Tuple<long, long, bool>(databaseSpace[DatabaseFileTypes.Log], databaseSpace[DatabaseFileTypes.Data], (bool)shrink));
}
}
});
return result;
}
internal static DataTable GetSessions(ServerInfo server)
{
return(SqlHelper.Query(SqlSessions, server));
}
internal static DataTable GetLockedObjects(short sessionId, ServerInfo server)
{
var sql = SqlLockedObjects + " AND l.request_session_id = " + sessionId;
return(SqlHelper.Query(sql, server));
}
internal static DataTable GetLockedProcesses(ServerInfo server)
{
return(SqlHelper.Query(SqlLockedProcesses, server));
}
internal static ServerInfo GetServerInfo(ServerInfo server, string catalog)
{
return(new ServerInfo {
IsAzure = server.IsAzure, AuthType = server.AuthType, Server = server.Server, Database = catalog, User = server.User, Password = server.Password
});
}
internal static DataTable GetDatabasesInfo(ServerInfo server)
{
return(SqlHelper.Query("SELECT * FROM sys.databases WITH (NOLOCK)", server));
}
internal static DataTable GetDatabaseInfo(ServerInfo server, string database)
{
return SqlHelper.Query("SELECT DB_NAME(database_id) AS DatabaseName, Name AS Logical_Name, Physical_Name, CAST(size AS decimal(30,0))*8 AS Size, state, type FROM sys.master_files WITH (NOLOCK) WHERE DB_NAME(database_id) = '" + database + "'", server);
}
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];
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 static List<DatabaseStall> GetDatabaseStall(ServerInfo server)
{
var result = new List<DatabaseStall>();
var databaseList = GetDatabasesInfo(server);
databaseList.AsEnumerable().ForEach(db =>
{
if (Convert.ToInt32(db["state"]) == 0)
{
var name = db["name"].ToString();
if (!SystemDatabases.Contains(name))
{
var databaseInfo = GetDatabaseIoInfo(GetServerInfo(server, name));
var row = databaseInfo.Rows[0];
var dbIsStall = Convert.ToInt64(row["IsStall"]);
var dbIsReadStall = Convert.ToInt64(row["IsReadStall"]);
var dbIsWriteStall = Convert.ToInt64(row["IsWriteStall"]);
var hasLog = databaseInfo.Rows.Count > 1;
long logIsStall = 0;
long logIsReadStall = 0;
long logIsWriteStall = 0;
if (hasLog)
{
var log = databaseInfo.Rows[1];
logIsStall = Convert.ToInt64(log["IsStall"]);
logIsReadStall = Convert.ToInt64(log["IsReadStall"]);
logIsWriteStall = Convert.ToInt64(log["IsWriteStall"]);
}
var stalls = new long[] { dbIsReadStall, dbIsWriteStall, logIsReadStall, logIsWriteStall };
var max = stalls.Max();
var isExceeded = max >= DbStallThreshold;
result.Add(new DatabaseStall { Database = name, DbReadStall = dbIsReadStall, DbWriteStall = dbIsWriteStall, LogReadStall = logIsReadStall, LogwriteStall = logIsWriteStall, Max = max, IsExceeded = isExceeded });
}
}
});
return result;
}
public ProcessVisualizer(ServerInfo server)
: this()
{
if (server != null)
_serverInfo = server.Clone();
}
internal static Dictionary<string, KeyValue<long, long>> GetDiskSpace(ServerInfo server)
{
var databases = GetDatabasesInfo(server);
var files = new List<Tuple<bool, string, long>>();
databases.AsEnumerable().ForEach(d =>
{
if (Convert.ToInt32(d["state"]) == 0)
{
var database = GetDatabaseInfo(server, d["name"].ToString());
database.AsEnumerable().ForEach(f =>
{
files.Add(new Tuple<bool, string, long>(Convert.ToInt32(f["type"]) == 1, f["physical_name"].ToString(), Convert.ToInt64(Convert.ToDecimal(f["Size"]) / Utils.Size1K)));
}
);
}
});
var spaces = new Dictionary<string, KeyValue<long, long>>();
if (!server.IsAzure)
{
//MB free
var driveSpaces = SqlHelper.Query("EXEC master.sys.xp_fixeddrives", server);
driveSpaces.AsEnumerable().ForEach(s =>
{
//could not use name but rather index, because the column name will change according to locale
spaces.Add(s[0].ToString(), new KeyValue<long, long>(Convert.ToInt64(s[1]), 0));
});
}
files.ForEach(f =>
{
var drive = f.Item2.Substring(0, 1);
if (spaces.ContainsKey(drive))
{
spaces[drive].Value += f.Item3;
}
});
return spaces;
}
internal static string GetKey(ServerInfo server, bool isServer)
{
return (server.Server + (isServer ? string.Empty : "." + server.Database)).ToLower();
}
internal static DataTable GetLockedProcesses(ServerInfo server)
{
return SqlHelper.Query(SqlLockedProcesses, server);
}
internal static List<HistoryRecord> GetRecords(ServerInfo server, bool isServer, DateTypes dateType, DateTime startDate)
{
var records = new List<HistoryRecord>();
var key = GetKey(server, isServer);
using (var dateIndexStream = new FileStream(GetFile(false, true), FileMode.OpenOrCreate))
{
using (var dateContentStream = new FileStream(GetFile(false, false), FileMode.OpenOrCreate))
{
var dateFormatter = new CustomBinaryFormatter(dateIndexStream, dateContentStream);
dateFormatter.Register<HistoryDate>(1);
var endDate = DateTime.Now.Date;
var samplingSpan = 1;
switch (dateType)
{
case DateTypes.Hour:
endDate = startDate.AddDays(1);
samplingSpan = 1;
break;
case DateTypes.Day:
endDate = startDate.AddDays(1);
samplingSpan = 24;
break;
case DateTypes.Week:
endDate = startDate.AddDays(7);
samplingSpan = 7 * 24;
break;
case DateTypes.Month:
endDate = startDate.AddMonths(1);
samplingSpan = 31 * 24;
break;
case DateTypes.Year:
endDate = startDate.AddYears(1);
samplingSpan = 365 * 24;
break;
default:
break;
}
var count = dateFormatter.Count;
Debug.WriteLine("all date count:" + count);
Debug.WriteLine("start date:" + startDate);
var dates = new List<HistoryDate>();
for (long i = 0; i < count; i++)
{
var date = dateFormatter.Deserialize<HistoryDate>(false);
var dateTime = DateTime.Parse(date.Date);
Debug.WriteLine("current date:" + dateTime);
if (startDate.Date <= dateTime && dateTime <= endDate)
dates.Add(date);
}
Debug.WriteLine("valid date count:" + dates.Count);
if (dates.Count > 0)
{
var start = dates.Aggregate((d1, d2) => DateTime.Parse(d1.Date) < DateTime.Parse(d2.Date) ? d1 : d2);
var end = dates.Aggregate((d1, d2) => DateTime.Parse(d1.Date) > DateTime.Parse(d2.Date) ? d1 : d2);
using (var dataIndexStream = new FileStream(GetFile(true, true), FileMode.OpenOrCreate))
{
using (var dataContentStream = new FileStream(GetFile(true, false), FileMode.OpenOrCreate))
{
var historyFormatter = new CustomBinaryFormatter(dataIndexStream, dataContentStream);
historyFormatter.Register<HistoryRecord>(1);
for (var i = start.Index; i < end.Index; i += samplingSpan)
{
historyFormatter.MoveTo(i);
var record = historyFormatter.Deserialize<HistoryRecord>(false);
if (record.Key == key)
records.Add(record);
}
historyFormatter.Close();
dataContentStream.Close();
}
dataIndexStream.Close();
}
}
dateFormatter.Close();
dateContentStream.Close();
}
dateIndexStream.Close();
}
return records;
}
internal static void GetOsInfo(ServerInfo server, out DateTime serverStartTime)
{
//what's wrong with the SQL Server team??? they just keep changing the column name in different versions
//sqlserver_start_time
var info = SqlHelper.Query("SELECT * FROM sys.dm_os_sys_info WITH (NOLOCK)", server);
var row = info.Rows[0];
if (info.Columns.Contains("sqlserver_start_time"))
serverStartTime = Convert.ToDateTime(row["sqlserver_start_time"]);
else if (info.Columns.Contains("ms_ticks"))
{
var startTime = row["ms_ticks"].ToString();
var ticks = Convert.ToInt64(startTime);
serverStartTime = DateTime.Now.AddMilliseconds(-ticks);
}
else
serverStartTime = DateTime.Now;
}
internal static void GetCpuInfo(ServerInfo server, out int sqlProcess, out int systemIdle, out int otherProcesses)
{
var sql = @"select TOP (1) SQLProcessUtilization,
SystemIdle,
100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
from (
select
record.value('(./Record/@id)[1]', 'int') as record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,
timestamp
from (
select timestamp, convert(xml, record) as record
from sys.dm_os_ring_buffers
where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
and record like '%<SystemHealth>%') as x
) as y
order by record_id desc";
var data = SqlHelper.Query(sql, server);
if (data.Rows.Count > 0)
{
var row = data.Rows[0];
sqlProcess = row.Field<int>("SQLProcessUtilization");
systemIdle = row.Field<int>("SystemIdle");
otherProcesses = row.Field<int>("OtherProcessUtilization");
}
else
{
sqlProcess = 0;
systemIdle = 0;
otherProcesses = 0;
}
}
internal static int GetServerVersion(ServerInfo server)
{
var version = SqlHelper.ExecuteScalar("SELECT SERVERPROPERTY('ProductVersion')", server);
var value = version.ToString();
var major = value.Split('.')[0];
return Convert.ToInt32(major);
}
internal static DataTable GetDatabaseInfo(ServerInfo server, string database)
{
if (server.IsAzure)
{
server.Database = database;
return SqlHelper.Query(string.Format("SELECT '{0}' AS DatabaseName, Name AS Logical_Name, Physical_Name, CAST(size AS decimal(30,0))*8 AS Size, state, type FROM sys.database_files", database), server);
}
else
return SqlHelper.Query("SELECT DB_NAME(database_id) AS DatabaseName, Name AS Logical_Name, Physical_Name, CAST(size AS decimal(30,0))*8 AS Size, state, type FROM sys.master_files WITH (NOLOCK) WHERE DB_NAME(database_id) = '" + database + "'", server);
}
internal static string GetSessionSql(string sessionId, ServerInfo server)
{
var data = SqlHelper.Query("dbcc INPUTBUFFER(" + sessionId + ")", server);
var sql = data != null && data.Rows.Count > 0 ? (data.Rows[0][2] as string) : string.Empty;
sql = !string.IsNullOrEmpty(sql) ? sql.Replace("\0", string.Empty) : string.Empty;
if (!server.IsAzure)
{
data = SqlHelper.Query(@"declare @s nvarchar(max)
declare @handle binary(20)
declare @start int
declare @end int
select @handle = sql_handle,@start = stmt_start, @end = stmt_end from sys.sysprocesses where spid=" + sessionId + @"
select @s = text FROM sys.dm_exec_sql_text( @handle )
select @s as FullStatement, SUBSTRING(@s, (@start/2)+1, ((CASE @end WHEN -1 THEN DATALENGTH(@s) ELSE @end END - @start)/2)+1) as CurrentStatement", server);
if (data.Rows.Count > 0)
{
sql = sql.Trim();
var full = data.Rows[0]["FullStatement"] as string;
if (!string.IsNullOrEmpty(full) && !string.IsNullOrEmpty(full.Trim()))
{
full = full.Trim();
var statement = data.Rows[0]["CurrentStatement"] as string;
if (!string.IsNullOrEmpty(statement) && !string.IsNullOrEmpty(statement.Trim()))
{
statement = statement.Trim();
if (statement != sql)
{
var finalSql = "--actual command\r\n" + sql + "\r\n\r\n--current statement\r\n" + statement;
if (full != sql)
finalSql += "\r\n\r\n--full sql\r\n" + full;
sql = finalSql;
}
}
}
}
}
return sql;
}
internal void AddPerformanceItem(ServerInfo server, bool isServer)
{
if (FindPerformanceServer(server, isServer) == null)
PerformanceItems.Add(new ServerInfoEx { Database = server.Database, Server = server.Server, IsServer = isServer });
}
internal static DataRow GetTableInfo(ServerInfo server, string tableName)
{
var data = SqlHelper.Query(string.Format(SqlTableInfo, tableName), server);
if (data != null && data.Rows.Count > 0)
return data.Rows[0];
else
return null;
}
internal static DataTable GetDatabasesInfo(ServerInfo server)
{
return SqlHelper.Query("SELECT * FROM sys.databases WITH (NOLOCK)", server);
}
internal static DataTable GetDatabaseIoInfo(ServerInfo server)
{
string sql;
if (server.IsAzure)
sql = "SELECT DB_ID() AS dbid, file_id AS fileid, physical_name AS filename FROM sys.database_files";
else
sql = string.Format(@"SELECT sys.databases.database_id AS dbid, sys.master_files.file_id AS fileid, sys.master_files.physical_name AS filename
FROM sys.master_files INNER JOIN sys.databases
ON sys.master_files.database_id = sys.databases.database_id
WHERE sys.databases.name = '{0}'", server.Database);
var dataFiles = SqlHelper.Query(sql, server);
var data = new DataTable();
data.Columns.Add("StartDate", typeof(DateTime));
data.Columns.Add("IsStall", typeof(double));
data.Columns.Add("IsReadStall", typeof(double));
data.Columns.Add("IsWriteStall", typeof(double));
data.Columns.Add("NumberReads", typeof(long));
data.Columns.Add("BytesRead", typeof(long));
data.Columns.Add("NumberWrites", typeof(long));
data.Columns.Add("BytesWritten", typeof(long));
data.Columns.Add("CurrentNumberReads", typeof(long));
data.Columns.Add("CurrentNumberWrites", typeof(long));
data.Columns.Add("IsLog", typeof(bool));
data.Columns.Add("FileCount", typeof(long));
data.Columns.Add("IoStallReadMS", typeof(long));
data.Columns.Add("IoStallWriteMS", typeof(long));
for (var i = 0; i < 2; i++)
{
var row = data.NewRow();
row.ItemArray = new object[] { DateTime.Now, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
row["IsLog"] = Convert.ToBoolean(i);
data.Rows.Add(row);
}
dataFiles.AsEnumerable().ForEach(d =>
{
var dbId = d["dbid"];
var fileId = d["fileid"];
var fileName = d["filename"].ToString();
var index = Path.GetExtension(fileName).ToLower() == ".ldf" ? 1 : 0;
//sys.dm_io_virtual_file_stats()
var fileStats = SqlHelper.Query(string.Format("SELECT DATEADD(ss, -1 * Timestamp/1000 , getdate()) AS StartDate, IoStallReadMS, IoStallWriteMS, NumberReads, BytesRead, NumberWrites, BytesWritten FROM ::fn_virtualfilestats({0}, {1})", dbId, fileId), server);
fileStats.AsEnumerable().ForEach(f =>
{
data.Rows[index]["StartDate"] = f["StartDate"];
data.Rows[index]["NumberReads"] = Convert.ToInt64(data.Rows[index]["NumberReads"]) + Convert.ToInt64(f["NumberReads"]);
if (Convert.ToInt64(data.Rows[index]["NumberReads"]) == 0)
data.Rows[index]["NumberReads"] = 1;
data.Rows[index]["BytesRead"] = Convert.ToInt64(data.Rows[index]["BytesRead"]) + Convert.ToInt64(f["BytesRead"]);
data.Rows[index]["NumberWrites"] = Convert.ToInt64(data.Rows[index]["NumberWrites"]) + Convert.ToInt64(f["NumberWrites"]);
if (Convert.ToDouble(data.Rows[index]["NumberWrites"]) == 0)
data.Rows[index]["NumberWrites"] = 1;
data.Rows[index]["BytesWritten"] = Convert.ToInt64(data.Rows[index]["BytesWritten"]) + Convert.ToInt64(f["BytesWritten"]);
data.Rows[index]["FileCount"] = Convert.ToInt64(data.Rows[index]["FileCount"]) + 1;
data.Rows[index]["IoStallReadMS"] = Convert.ToInt64(data.Rows[index]["IoStallReadMS"]) + Convert.ToInt64(f["IoStallReadMS"]);
data.Rows[index]["IoStallWriteMS"] = Convert.ToInt64(data.Rows[index]["IoStallWriteMS"]) + Convert.ToInt64(f["IoStallWriteMS"]);
data.Rows[index]["IsReadStall"] = Convert.ToDouble(data.Rows[index]["IoStallReadMS"]) / Convert.ToInt64(data.Rows[index]["NumberReads"]);
data.Rows[index]["IsWriteStall"] = Convert.ToDouble(data.Rows[index]["IoStallWriteMS"]) / Convert.ToInt64(data.Rows[index]["NumberWrites"]);
data.Rows[index]["IsStall"] = (Convert.ToDouble(data.Rows[index]["IoStallReadMS"]) + Convert.ToDouble(data.Rows[index]["IoStallWriteMS"])) / (Convert.ToInt64(data.Rows[index]["NumberReads"]) + Convert.ToInt64(data.Rows[index]["NumberWrites"]));
});
});
return data;
}
private ServerInfoEx FindPerformanceServer(ServerInfo server, bool isServer)
{
return PerformanceItems.FirstOrDefault(s => s.Server == server.Server
&& s.Database == server.Database
&& s.IsServer == isServer);
}