CREATE TABLE dbo.performance_alerts (
id INT IDENTITY(1,1) PRIMARY KEY,
alert_time DATETIME,
alert_type VARCHAR(50),
alert_message NVARCHAR(MAX),
severity INT,
status VARCHAR(20) DEFAULT ‘New’
);
GO

— 步骤2：创建告警存储过程
CREATE PROCEDURE dbo.sp_check_performance_alerts
AS
BEGIN
SET NOCOUNT ON;

DECLARE @cpu_usage DECIMAL(5,2);
DECLARE @memory_usage DECIMAL(5,2);
DECLARE @io_stall_ms INT;
DECLARE @user_connections INT;
DECLARE @batch_requests_per_sec INT;
DECLARE @page_life_expectancy INT;

— 收集性能数据
SELECT @cpu_usage = 100.0 – (
SELECT TOP 1 CONVERT(DECIMAL(5,2), value)
FROM sys.dm_os_performance_counters
WHERE counter_name = ‘Idle Time’ AND instance_name = ‘_Total’
) / (
SELECT TOP 1 CONVERT(DECIMAL(5,2), value)
FROM sys.dm_os_performance_counters
WHERE counter_name = ‘Percent Processor Time’ AND instance_name = ‘_Total’
) * 100;

SELECT @memory_usage = (
SELECT physical_memory_in_use_kb / 1024.0
FROM sys.dm_os_process_memory
) / (
SELECT physical_memory_kb / 1024.0
FROM sys.dm_os_sys_info
) * 100;

SELECT @io_stall_ms = SUM(io_stall)
FROM sys.dm_io_virtual_file_stats(NULL, NULL);

SELECT @user_connections = COUNT(*)
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

SELECT @batch_requests_per_sec = CONVERT(INT, value)
FROM sys.dm_os_performance_counters
WHERE counter_name = ‘Batch Requests/sec’ AND instance_name = ”;

SELECT @page_life_expectancy = CONVERT(INT, value)
FROM sys.dm_os_performance_counters
WHERE counter_name = ‘Page Life Expectancy’ AND instance_name = ”;

— 检查CPU使用率
IF @cpu_usage > 80
BEGIN
INSERT INTO dbo.performance_alerts (
alert_time,
alert_type,
alert_message,
severity
) VALUES (
GETDATE(),
‘CPU’,
‘CPU使用率过高: ‘ + CAST(@cpu_usage AS VARCHAR(10)) + ‘%’,
1
);
END;

— 检查内存使用率
IF @memory_usage > 90
BEGIN
INSERT INTO dbo.performance_alerts (
alert_time,
alert_type,
alert_message,
severity
) VALUES (
GETDATE(),
‘Memory’,
‘内存使用率过高: ‘ + CAST(@memory_usage AS VARCHAR(10)) + ‘%’,
1
);
END;

— 检查IO等待时间
IF @io_stall_ms > 10000
BEGIN
INSERT INTO dbo.performance_alerts (
alert_time,
alert_type,
alert_message,
severity
) VALUES (
GETDATE(),
‘IO’,
‘IO等待时间过长: ‘ + CAST(@io_stall_ms AS VARCHAR(10)) + ‘ms’,
1
);
END;

— 检查用户连接数
IF @user_connections > 100
BEGIN
INSERT INTO dbo.performance_alerts (
alert_time,
alert_type,
alert_message,
severity
) VALUES (
GETDATE(),
‘Connections’,
‘用户连接数过多: ‘ + CAST(@user_connections AS VARCHAR(10)),
2
);
END;

— 检查页面生命周期
IF @page_life_expectancy < 300 BEGIN INSERT INTO dbo.performance_alerts ( alert_time, alert_type, alert_message, severity ) VALUES ( GETDATE(), 'Memory' , '页面生命周期过短: ' + CAST(@page_life_expectancy AS VARCHAR(10)) + '秒' , 2 ); END; -- 检查批处理请求数 IF @batch_requests_per_sec> 500
BEGIN
INSERT INTO dbo.performance_alerts (
alert_time,
alert_type,
alert_message,
severity
) VALUES (
GETDATE(),
‘Query’,
‘批处理请求数过高: ‘ + CAST(@batch_requests_per_sec AS VARCHAR(10)) + ‘/秒’,
2
);
END;
END;
GO

— 步骤3：创建告警作业
USE msdb;
GO

EXEC dbo.sp_add_job
@job_name = N’CheckPerformanceAlerts’,
@enabled = 1,
@description = N’检查SQLServer性能告警’;
GO

EXEC dbo.sp_add_jobstep
@job_name = N’CheckPerformanceAlerts’,
@step_name = N’Check Alerts’,
@subsystem = N’TSQL’,
@command = N’EXEC dbo.sp_check_performance_alerts;’,
@database_name = N’msdb’;
GO

EXEC dbo.sp_add_jobschedule
@job_name = N’CheckPerformanceAlerts’,
@name = N’Every 5 Minutes’,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 5,
@active_start_time = 000000,
@active_end_time = 235959;
GO

— 步骤4：创建告警通知
— 1. 配置数据库邮件
EXEC msdb.dbo.sysmail_add_account_sp
@account_name = ‘SQLServerAlert’,
@email_address = ‘alert@fgedu.net.cn’,
@display_name = ‘SQLServer Alert’,
@mailserver_name = ‘smtp.fgedu.net.cn’,
@username = ‘alert@fgedu.net.cn’,
@password = ‘Password123!’;
GO

EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘SQLServerAlertProfile’,
@description = ‘SQLServer Alert Profile’;
GO

EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘SQLServerAlertProfile’,
@account_name = ‘SQLServerAlert’,
@sequence_number = 1;
GO

— 2. 创建告警通知存储过程
CREATE PROCEDURE dbo.sp_send_performance_alerts
AS
BEGIN
SET NOCOUNT ON;

DECLARE @alert_id INT;
DECLARE @alert_type VARCHAR(50);
DECLARE @alert_message NVARCHAR(MAX);
DECLARE @severity INT;
DECLARE @email_body NVARCHAR(MAX);

— 选择未处理的告警
DECLARE alert_cursor CURSOR FOR
SELECT id, alert_type, alert_message, severity
FROM dbo.performance_alerts
WHERE status = ‘New’;

OPEN alert_cursor;
FETCH NEXT FROM alert_cursor INTO @alert_id, @alert_type, @alert_message, @severity;

WHILE @@FETCH_STATUS = 0
BEGIN
— 构建邮件内容
SET @email_body = ‘SQLServer性能告警:\n\n’;
SET @email_body = @email_body + ‘告警类型: ‘ + @alert_type + ‘\n’;
SET @email_body = @email_body + ‘告警消息: ‘ + @alert_message + ‘\n’;
SET @email_body = @email_body + ‘严重程度: ‘ + CASE WHEN @severity = 1 THEN ‘高’ ELSE ‘中’ END + ‘\n’;
SET @email_body = @email_body + ‘告警时间: ‘ + CONVERT(VARCHAR(20), GETDATE(), 120) + ‘\n’;

— 发送邮件
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘SQLServerAlertProfile’,
@recipients = ‘dba@fgedu.net.cn’,
@subject = ‘SQLServer性能告警’,
@body = @email_body;

— 更新告警状态
UPDATE dbo.performance_alerts
SET status = ‘Sent’
WHERE id = @alert_id;

FETCH NEXT FROM alert_cursor INTO @alert_id, @alert_type, @alert_message, @severity;
END;

CLOSE alert_cursor;
DEALLOCATE alert_cursor;
END;
GO

— 3. 创建告警通知作业
USE msdb;
GO

EXEC dbo.sp_add_job
@job_name = N’SendPerformanceAlerts’,
@enabled = 1,
@description = N’发送SQLServer性能告警’;
GO

EXEC dbo.sp_add_jobstep
@job_name = N’SendPerformanceAlerts’,
@step_name = N’Send Alerts’,
@subsystem = N’TSQL’,
@command = N’EXEC dbo.sp_send_performance_alerts;’,
@database_name = N’msdb’;
GO

EXEC dbo.sp_add_jobschedule
@job_name = N’SendPerformanceAlerts’,
@name = N’Every 10 Minutes’,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 10,
@active_start_time = 000000,
@active_end_time = 235959;
GO

执行结果：