# 启用扩展事件会话
CREATE EVENT SESSION [fgedu_performance_monitor] ON SERVER
ADD EVENT sqlserver.sql_batch_completed(
ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.nt_username, sqlserver.session_id, sqlserver.sql_text)
WHERE ([duration] > 1000000) — 超过1秒的查询
),
ADD EVENT sqlserver.rpc_completed(
ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.nt_username, sqlserver.session_id, sqlserver.sql_text)
WHERE ([duration] > 1000000) — 超过1秒的查询
)
ADD TARGET package0.event_file(
SET filename=N’\fgedu1\monitoring\fgedu_performance_monitor.xel’,
max_file_size=(100),
max_rollover_files=(5)
)
WITH (
MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=ON,
STARTUP_STATE=ON
);
GO

# 启动扩展事件会话
ALTER EVENT SESSION [fgedu_performance_monitor] ON SERVER STATE = START;
GO

# 查看扩展事件会话状态
SELECT
name,
status,
start_time,
event_session_address
FROM sys.dm_xe_sessions
WHERE name = ‘fgedu_performance_monitor’;
GO

name status start_time event_session_address
——————— —— ———————– ———————
fgedu_performance_monitor 1 2023-12-01 10:00:00.000 0x0000000000000000

# 使用动态管理视图监控性能
— 监控CPU使用情况
SELECT
session_id,
logical_reads,
physical_reads,
cpu_time,
total_elapsed_time,
status,
command,
text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE session_id > 50
ORDER BY cpu_time DESC;
GO

— 监控等待统计信息
SELECT
wait_type,
wait_time_ms,
signal_wait_time_ms,
wait_time_ms – signal_wait_time_ms AS resource_wait_time_ms,
waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0
ORDER BY wait_time_ms DESC
TOP 10;
GO

— 监控内存使用情况
SELECT
physical_memory_in_use_kb,
large_page_allocations_kb,
locked_page_allocations_kb,
total_virtual_address_space_kb,
virtual_address_space_committed_kb,
memory_utilization_percentage,
process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory;
GO

# 创建监控数据库
CREATE DATABASE fgedu_monitoring
ON PRIMARY (
NAME = ‘fgedu_monitoring_data’,
FILENAME = ‘/sqlserver/fgdata/fgedu_monitoring_data.mdf’,
SIZE = 20GB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 2GB
)
LOG ON (
NAME = ‘fgedu_monitoring_log’,
FILENAME = ‘/sqlserver/fgdata/fgedu_monitoring_log.ldf’,
SIZE = 5GB,
MAXSIZE = 50GB,
FILEGROWTH = 1GB
);
GO

# 创建监控表
USE fgedu_monitoring;
GO

CREATE TABLE dbo.fgedu_performance_metrics (
metric_id INT PRIMARY KEY IDENTITY,
server_name NVARCHAR(100) NOT NULL,
metric_name NVARCHAR(100) NOT NULL,
metric_value FLOAT NOT NULL,
collection_time DATETIME NOT NULL DEFAULT GETDATE()
);
GO

CREATE INDEX IX_fgedu_performance_metrics_collection_time ON dbo.fgedu_performance_metrics(collection_time);
GO

CREATE TABLE dbo.fgedu_alert_log (
alert_id INT PRIMARY KEY IDENTITY,
server_name NVARCHAR(100) NOT NULL,
alert_type NVARCHAR(100) NOT NULL,
alert_message NVARCHAR(1000) NOT NULL,
alert_time DATETIME NOT NULL DEFAULT GETDATE(),
alert_status NVARCHAR(50) NOT NULL DEFAULT ‘New’
);
GO

CREATE INDEX IX_fgedu_alert_log_alert_time ON dbo.fgedu_alert_log(alert_time);
GO

# 创建监控存储过程
CREATE PROCEDURE dbo.usp_collect_performance_metrics
AS
BEGIN
INSERT INTO dbo.fgedu_performance_metrics (server_name, metric_name, metric_value)
SELECT
@@SERVERNAME,
‘CPU Usage’,
(SELECT TOP 1 cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = ‘% Processor Time’ AND instance_name = ‘_Total’)
UNION ALL
SELECT
@@SERVERNAME,
‘Memory Usage (MB)’,
(SELECT physical_memory_in_use_kb / 1024 FROM sys.dm_os_process_memory)
UNION ALL
SELECT
@@SERVERNAME,
‘Disk Read IOPS’,
(SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = ‘Reads/sec’ AND instance_name = ‘C:’)
UNION ALL
SELECT
@@SERVERNAME,
‘Disk Write IOPS’,
(SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = ‘Writes/sec’ AND instance_name = ‘C:’)
UNION ALL
SELECT
@@SERVERNAME,
‘Active Connections’,
(SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE status = ‘running’);
END;
GO

# 创建SQL Server Agent作业定期收集监控数据
USE msdb;
GO

EXEC dbo.sp_add_job
@job_name = N’Collect Performance Metrics’,
@enabled = 1,
@description = N’Collect performance metrics every 5 minutes’;
GO

EXEC dbo.sp_add_jobstep
@job_name = N’Collect Performance Metrics’,
@step_name = N’Collect Metrics’,
@subsystem = N’TSQL’,
@command = N’EXEC fgedu_monitoring.dbo.usp_collect_performance_metrics;’,
@database_name = N’fgedu_monitoring’;
GO

EXEC dbo.sp_add_schedule
@schedule_name = N’Every 5 Minutes’,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 5;
GO

EXEC dbo.sp_attach_schedule
@job_name = N’Collect Performance Metrics’,
@schedule_name = N’Every 5 Minutes’;
GO

EXEC dbo.sp_add_jobserver
@job_name = N’Collect Performance Metrics’;
GO

# 1. 部署监控服务器
# 配置监控服务器硬件：8核CPU, 32GB内存, 500GB SSD

# 2. 安装SQL Server Management Studio
# 下载并安装最新版本的SSMS

# 3. 配置扩展事件
# 创建系统健康会话
ALTER EVENT SESSION [system_health] ON SERVER
ADD TARGET package0.event_file(
SET filename=N’\fgedu1\monitoring\system_health.xel’,
max_file_size=(100),
max_rollover_files=(5)
);
GO

# 4. 配置SQL Server Agent告警
USE msdb;
GO

# 创建告警运算符
EXEC dbo.sp_add_operator
@name = N’DBA Team’,
@enabled = 1,
@email_address = N’dba@fgedu.net.cn’,
@pager_address = N’13800138000′,
@weekday_pager_start_time = 090000,
@weekday_pager_end_time = 180000,
@saturday_pager_start_time = 090000,
@saturday_pager_end_time = 120000,
@sunday_pager_start_time = 090000,
@sunday_pager_end_time = 120000,
@pager_days = 127;
GO

# 创建性能条件告警
EXEC dbo.sp_add_alert
@name = N’High CPU Usage’,
@performance_condition = N’Processor(*)\% Processor Time > 80 for 5 minutes’,
@enabled = 1,
@delay_between_responses = 300,
@include_event_description_in = 1,
@notification_message = N’CPU usage is high. Please check the system performance.’,
@job_id = NULL;
GO

# 添加告警通知
EXEC dbo.sp_add_notification
@alert_name = N’High CPU Usage’,
@operator_name = N’DBA Team’,
@notification_method = 1; — 1=Email, 2=Pager, 4=NetSend
GO