利用MSSQL监控工具保障系统运行顺利
在当今信息化时代，对于企业来说，信息系统已经成为了重要的基础设施之一，而对于这些系统的正常运行，数据库则是其中的重要组成部分。MSSQL作为微软的数据库产品，在企业业务中运用越来越广泛。不过，由于数据库的庞大和复杂性，操作和维护也变得越来越困难，例如如何在保证MSSQL的高可用性的同时确保系统的高效稳定运行。这时候就需要用到MSSQL的监控工具来协助我们解决问题。
一、SQL Server Profiler
SQL Server Profiler是MSSQL自带的一个强大的工具，它可以跟踪和记录数据库中的活动和事件。使用它可以监控用户在数据库中执行的各种操作，如查询数据、修改数据、删除数据等，还可以检测到出现的错误和锁定情况，这些信息有助于我们定位和解决问题。
1.1 使用SQL Server Profiler监控系统性能
使用SQL Server Profiler可以监控数据库的性能，并帮助我们找出影响数据库性能的瓶颈，比如高负载时CPU或磁盘IO使用率高等。我们可以设置一个跟踪事件，从而收集数据库性能数据。例如，我们可以编辑一个模板，选择需要跟踪的事件，然后指定需要监控的指标，例如CPU使用率、每秒事务数、平均响应时间等。具体的操作可以参考以下代码示例。
SELECT * FROM sys.traces
WHERE is_default = 1;
这条SQL语句可以查询到MSSQL默认跟踪模板的ID，然后我们可以使用以下代码创建一个跟踪模板，并设置需要监控的指标值。
USE master;
GO
EXEC sp_trace_create @traceid OUTPUT,
@options = 2,
@tracefile = N'D:\SQLTrace.trc';
/* 选择需要监控的事件 */
DECLARE @on BIT = 1;
EXEC sp_trace_setevent @traceid, 10, 1, @on;
EXEC sp_trace_setevent @traceid, 10, 9, @on;
EXEC sp_trace_setevent @traceid, 10, 6, @on;
EXEC sp_trace_setevent @traceid, 10, 10, @on;
EXEC sp_trace_setevent @traceid, 10, 14, @on;
EXEC sp_trace_setevent @traceid, 10, 11, @on;
EXEC sp_trace_setevent @traceid, 10, 12, @on;
/* 设置需要监控的指标值 */
EXEC sp_trace_setstatus @traceid, 1;
使用以上代码，我们可以创建一个跟踪事件，并指定需要监控的指标值。
1.2 使用SQL Server Profiler监控数据库安全性
我们可以使用SQL Server Profiler监控数据库的安全性，这可以帮助我们检测恶意攻击和审计数据库访问。使用Profiler可以捕获数据库中的所有访问请求，包括访问数据库对象、执行Transact-SQL语句和进行身份验证。我们可以使用如下代码创建一个捕获登录和登出请求的跟踪事件。
DECLARE @TraceID INT;
DECLARE @maxfilesize BIGINT = 5;
DECLARE @filepath NVARCHAR(200)
SET @filepath = N'C:\TraceDirectory\TraceOutput';
EXEC sp_trace_create @TraceID OUTPUT, 2, @filepath, @maxfilesize, NULL ;
EXEC sp_trace_setevent @TraceID, 14, 1, 1;
EXEC sp_trace_setevent @TraceID, 14, 22, 1;
EXEC sp_trace_setevent @TraceID, 14, 31, 1;
EXEC sp_trace_setevent @TraceID, 14, 34, 1;
EXEC sp_trace_setfilter @TraceID, 8, 0, 7, N'TestUser';
EXEC sp_trace_setstatus @TraceID, 1;
在以上代码中，使用sp_trace_create存储过程创建一个跟踪事件，并设置要跟踪的事件和属性。使用sp_trace_setfilter存储过程可以过滤要捕获的事件，例如设置只捕获TestUser帐户的请求。使用sp_trace_setstatus存储过程可以启动跟踪事件。
二、SQL Server Management Studio
SQL Server Management Studio（SSMS）是一个强大的管理和开发工具，用于管理MSSQL的配置、维护、调试和监控。它还提供了访问其他工具的链接和集成，例如SQL Server Profiler、Database Engine Tuning Advisor和SQL Server Agent。
2.1 使用SQL Server Management Studio监控系统性能
使用SQL Server Management Studio可以监控系统性能，方法是使用Activity Monitor。在Management Studio的对象资源管理器中，右键单击一个实例，然后单击“活动监视器”，打开此监视器。活动监视器包括几个不同的标签页，可以提供关于响应时间、CPU和磁盘IO使用率、单个线程的等待信息等的数据。我们可以根据需要选择指标，并选择排序方式进行查看数据。例如，我们可以选择对象，然后选择“cpu使用率”进行排序，以便识别最耗费CPU资源的查询。
2.2 使用SQL Server Management Studio监控数据库的空间和性能
我们可以使用Management Studio的“SQL Server轨迹”标签页监控数据库的性能，该标签页允许我们捕获有关SQL Server数据库性能的详细信息。详细信息包括：locks的使用情况、prcoesses详细信息、查询统计信息以及各种事件信息等。捕获事件的过程与使用Profiler相似，只是操作界面略有不同。与Profiler相比，SOSM的资源占用较小，适用于部署在远程服务器上进行远程监控，同时提供了详细的指标值，相较于类似Task Manager的性能查看工具，更为全面和明细。
三、SQL Server每日日志监控
如果您是一位系统管理员，那么肯定要定期监视MSSQL的日志。每日审计日志是MSSQL Server的关键组件之一，其中包含有关可能指示系统的任何进行或错误的详细信息。
3.1 在MSSQL Server Configuration Manager中开启MSSQL日志
在日志监控之前，我们首先需要确定我们已经正确地配置了日志选项。在MSSQL中，我们需要在SQL SERVER Configuration Manager中开启日志选项。以下是一些配置建议的示例代码：
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC xp_cmdshell 'mkdir X:\SQLServerLogs\';
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
EXEC sp_configure 'default trace enabled', 1;
RECONFIGURE;
配置按上述代码执行后，我们的MSSQL服务器就会开启默认的每日日志功能。然后，我们就可以检查日志，找到或记录我们需要监控的几个主服务器活动。
3.2 使用SQL储存过程规范日志的自动清理
由于日志文件大小随着时间的推移会变得庞大，因此进行自动清理已经成为了必要活动。我们可以通过使用MSSQL内置的删除过程，编写自动清理脚本，使得清理日志文件的过程高效自动化。
以下SQL代码展示了如何使用stored procedures来自动清理日志：
USE AdventureWorks2012;
GO
-- 日志文件清理
EXEC sp_cycle_errorlog;
GO
--备份操作日志并清理
DECLARE @path varchar(256);
SELECT @path = path
FROM sys.traces
WHERE id = 1;
DECLARE @dt NVARCHAR(8);
DECLARE @sb NVARCHAR(8);
SELECT @dt= CONVERT(NVARCHAR(8), GETDATE(), 112);
SELECT @sb= CONVERT(NVARCHAR(6), GETDATE(), 112);
SET @path = REPLACE(@path, '.trc',N'' + @sb + '.trc');
EXEC sp_trace_create 1, 0, @path, 1024, NULL ;
-- Trace the backup and restore event.Type为115表示备份过程，116表示还原过程
EXEC sp_trace_setevent 1, 115, 1, 1;
EXEC sp_trace_setevent 1, 115, 9, 1;
EXEC sp_trace_setevent 1, 116, 1, 1;
EXEC sp_trace_setevent 1, 116, 9, 1;
--清除备份日志
EXEC sp_trace_setstatus 1, 0;
EXEC sp_trace_setstatus 1, 2;
EXEC sp_trace_setstatus 1, 0;
DROP TABLE #LogInfo;
GO
有了以上步骤，我们就可以自动化地清除我们的日志。
总结
MSSQL Server作为企业级数据库的一个重要应用，它的操作和维护都涉及到运维的各个层面。在保证MSSQL的高可用性的同时，确保系统的高效稳定运行，监控工具的重要性也就凸显出来。从SQL Server Profiler到SQL Server Management Studio再到日志监控，每个工具都有自己独特的优势，但他们的共同目标都是监控和保护数据库的性能和安全。