高级SQL Server监视器，包含性能图表、分析和版本控制
监视SQL Server进程和作业，分析性能，对象版本控制，查看正在执行的SQL查询，终止进程/作业，对象浏览器，数据库收缩/日志截断/备份/分离/附加等
引言
这是SQL Monitor的第二篇文章，第一篇在这里：sqlmon.aspx
有关背景信息，请参考上述文章。
SQL Monitor可以监视SQL Server进程和作业，分析性能，对象版本控制，查看正在执行的SQL查询，终止进程/作业，对象浏览器，数据库收缩/日志截断/备份/分离/附加等。
好的，为了更好地理解，首先让我们来看下面的图片
嗯，我们可以看到它显示了SQL Server的实时IO/CPU/网络信息。事实上，它还能做很多事情。关于对象浏览器、活动和版本控制的信息，请参考上面提到的第一篇文章。
背景
在上一篇文章中，我们讨论了如何实现对象浏览器、活动和版本控制，在本文中，我将介绍新引入的性能图表和分析功能。
我们一直在思考“我的SQL Server到底怎么了？”，“它的运行状况如何？”，“瓶颈在哪里？”，“我能让它运行得更快吗？”等等。好了，是时候得到答案了，嗯，在某种程度上，以某种方式，到目前为止；）
性能图表
好吧，这可能不是你希望在圣诞节到来之际看到的最激动人心的功能了 :-D。
通过性能图表，您可以持续跟踪SQL Server的运行状况，它显示了以下实时信息
- 自上次服务器启动以来的总IO忙碌时间
- 自上次服务器启动以来的总CPU忙碌时间
- 自上次服务器启动以来的总IO读取（字节）
- 最近n秒的IO读取（字节）
- 自上次服务器启动以来的总IO写入（字节）
- 最近n秒的IO写入（字节）
- 自上次服务器启动以来的总数据包读取（字节）
- 最近n秒的数据包读取（字节）
- 自上次服务器启动以来的总数据包写入（字节）
- 最近n秒的数据包写入（字节）
- 自上次服务器启动以来的总连接数
- 最近n秒的连接数
n秒是监视器的刷新间隔，您可以在选项中设置。
信息从哪里来？
好吧，因为我是一个非常无聊的人，所以我真的很想找点事情来打发时间，时不时地，我会挖掘各种各样的资源，比如整个SQL Server数据库，包括所有函数、存储过程、视图，有一天我发现了一个名为sp_monitor的系统存储过程。嗯......这意味着一切，对吧？非常感谢SQL Server团队；）。当你深入挖掘时，你会发现它使用了以下系统变量
@@cpu_busy
@@io_busy
@@idle
@@pack_received
@@pack_sent
@@connections
@@packet_errors
@@total_read
@@total_write
@@total_errors我修改了存储过程以满足我的需求declare @now datetime
declare @cpu_busy int
declare @io_busy int
declare @idle int
declare @pack_received int
declare @pack_sent int
declare @pack_errors int
declare @connections int
declare @total_read int
declare @total_write int
declare @total_errors int
declare @oldcpu_busy int /* used to see if DataServer has been rebooted */
declare @interval int
declare @mspertick int /* 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(int, ((@cpu_busy * @mspertick) / 1000)),
cpu_busy_current = convert(int, (((@cpu_busy - cpu_busy)
* @mspertick) / 1000)),
cpu_busy_percentage = convert(int, ((((@cpu_busy - cpu_busy)
* @mspertick) / 1000) * 100) / @interval),
io_busy_total = convert(int, ((@io_busy * @mspertick) / 1000)),
io_busy_current = convert(int, (((@io_busy - io_busy)
* @mspertick) / 1000)),
io_busy_percentage = convert(int, ((((@io_busy - io_busy)
* @mspertick) / 1000) * 100) / @interval),
idle_total = convert(int, ((convert(bigint,@idle) * @mspertick) / 1000)),
idle_current = convert(int, (((@idle - idle)
* @mspertick) / 1000)),
idle_percentage = convert(int, ((((@idle - idle)
* @mspertick) / 1000) * 100) / @interval),
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
您可以看到它使用了一个名为spt_monitor的表来保存变量的最后快照，当下次来临时，用当前变量减去之前的变量。
您可以在这里找到存储过程的详细信息：http://technet.microsoft.com/en-gb/library/ms188912.aspx
如何显示信息？
我使用了.NET 4.0内置的Chart控件，位于System.Windows.Forms.DataVisualization。您可以在这里找到示例：http://archive.msdn.microsoft.com/mschart/Release/ProjectReleases.aspx?ReleaseId=4418
分析
目前，SQL Monitor可以分析磁盘/数据库/表/索引空间关系并给出可能的建议。
您是怎么做到的？
首先，它查找所有数据库，对于每个数据库，获取所有文件（数据、日志）的大小，然后使用master.sys.xp_fixeddrives查找每个磁盘的可用空间。然后它将获取所有数据库文件的总大小，并按文件所在的磁盘进行分组。根据选项中定义的比例，SQL Monitor将决定是否需要收缩或截断某个数据库。
//database & disk free space
var databases = GetDatabasesInfo();
var files = new List<tuple<bool, />>();
databases.AsEnumerable().ForEach(d =>
{
var database = GetDatabaseInfo(d["name"].ToString());
database.AsEnumerable().ForEach(f =>
{
files.Add(new Tuple<bool, />(Convert.ToInt32(f["type"]) == 1, f["physical_name"].ToString(), Convert.ToInt64(Convert.ToDecimal(f["Size"]) / Size1K)));
}
);
});
var spaces = new Dictionary<string, />>();
//MB free
var driveSpaces = Query("EXEC master.sys.xp_fixeddrives");
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, />(Convert.ToInt64(s[1]), 0));
});
files.ForEach(f =>
{
//maybe some access issues
try
{
var drive = f.Item2.Substring(0, 1);
if (spaces.ContainsKey(drive))
{
spaces[drive].Value += f.Item3;
}
}
catch (Exception)
{
//mmmm.....what can we do, mate?
}
});
spaces.ForEach(s =>
{
if (s.Value.Key < s.Value.Value / 100 * Settings.Instance.DatabaseDiskFreeSpaceRatio)
{
analysisResult.Add(new AnalysisResult { ResultType = AnalysisResultTypes.DiskFreeSpace, ObjectName = s.Key, ReferenceValue = s.Value.Key, CurrentValue = s.Value.Value, Factor = Settings.Instance.DatabaseDiskFreeSpaceRatio + SizePercentage });
}
});
//database data file & log file space
databases.AsEnumerable().ForEach(d =>
{
var name = d["name"].ToString();
if (!systemDatabases.Contains(name))
{
var database = GetDatabaseInfo(name);
var databaseSpace = new Dictionary<databasefiletypes, /> { { DatabaseFileTypes.Data, 0 }, { DatabaseFileTypes.Log, 0 } };
database.AsEnumerable().ForEach(f =>
{
var key = (DatabaseFileTypes)Convert.ToInt32(f["type"]);
databaseSpace[key] += Convert.ToInt64(Convert.ToDecimal(f["Size"]) / Size1K);
}
);
bool? shrink = null;
if (databaseSpace[DatabaseFileTypes.Log] > databaseSpace[DatabaseFileTypes.Data] / 100 * Settings.Instance.DatabaseDataLogSpaceRatio)
shrink = false;
else
{
var logSpaces = SQLHelper.Query("DBCC SQLPERF(LOGSPACE)", GetServerInfo(name));
var logSpace = logSpaces.Select(string.Format("[Database Name] = '{0}'", name));
if (logSpace.Length > 0)
{
var logSpacedUsed = Convert.ToDouble(logSpace[0]["Log Space Used (%)"]);
if (logSpacedUsed < Settings.Instance.DatabaseDataLogSpaceRatio)
shrink = true;
}
}
if (shrink != null)
analysisResult.Add(new AnalysisResult { ResultType = AnalysisResultTypes.DatabaseLogSpace, ObjectName = name, ReferenceValue = databaseSpace[DatabaseFileTypes.Log], CurrentValue = databaseSpace[DatabaseFileTypes.Data], Factor = Settings.Instance.DatabaseDataLogSpaceRatio + SizePercentage, Key = (bool)shrink ? 1 : 0 });
}
});
对于表空间，系统存储过程sp_spaceused用于获取表的使用空间
var tables = GetObjects(KeyTables);
tables.AsEnumerable().ForEach(t =>
{
var name = t[KeyName].ToString();
var space = Query(string.Format("EXEC sp_spaceused '{0}'", name), CurrentServerInfo);
if (space.Rows.Count > 0)
{
var row = space.Rows[0];
var dataSize = ToKB(row["data"]) / Size1K;
var indexSize = ToKB(row["index_size"]) / Size1K;
if (indexSize > dataSize / 100 * Settings.Instance.TableDataIndexSpaceRatio)
analysisResult.Add(new AnalysisResult { ResultType = AnalysisResultTypes.TableIndexSpace, ObjectName = name, ReferenceValue = dataSize, CurrentValue = indexSize, Factor = Settings.Instance.DatabaseDataLogSpaceRatio + SizePercentage, Key = (int)TableIndexSpaceRules.DataIndexSpaceRatio });
}
});
关注点
好吧，在深入挖掘SQL Server的过程中，我真的学到了很多（听起来像个建筑工人:D），我挖掘得越深，就越意识到SQL Server非常复杂且强大：它可以轻松处理一个表中超过100亿条记录。然而，在我日常的数据库开发中，我发现SQL Server（即使是SQL Server 2008 R2）存在不少问题，尤其是在它试图将资源让给其他工作进程时，它会卡在那里什么也不做。这就是我开发SQL Monitor的原因，试图弄清楚SQL Server内部到底发生了什么。
请继续关注，下一版本将有更激动人心、更具野心、更硬核、更全面的功能。
最后，新年快乐：)