# 1. 服务器硬件选择
# 推荐配置
– CPU：Intel Xeon Gold 6348 28核
– 内存：128GB DDR4 ECC
– 存储：8TB NVMe SSD (RAID 10)
– 网络：双10GbE网卡

# 2. 存储配置
# 配置RAID 10
# 检查存储配置
Get-PhysicalDisk | Select-Object FriendlyName, MediaType, Size, OperationalStatus
GO

FriendlyName MediaType Size OperationalStatus
———— ——— —- —————–
NVMe SSD 1 SSD 4294967296000 OK
NVMe SSD 2 SSD 4294967296000 OK
NVMe SSD 3 SSD 4294967296000 OK
NVMe SSD 4 SSD 4294967296000 OK

# 3. 网络配置
# 配置网络绑定
New-NetLbfoTeam -Name “Team1” -TeamMembers “Ethernet0”, “Ethernet1” -TeamingMode SwitchIndependent -LoadBalancingAlgorithm Dynamic

# 检查网络配置
Get-NetAdapter | Select-Object Name, Status, LinkSpeed
GO

Name Status LinkSpeed
—- —— ———
Team1 Up 20 Gbps
Ethernet0 Up 10 Gbps
Ethernet1 Up 10 Gbps

# 4. 服务器电源配置
# 设置电源计划为高性能
powercfg /setactive 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c

# 检查电源计划
powercfg /list
GO

Existing Power Schemes (* Active)
———————————–
Power Scheme GUID: 381b4222-f694-41f0-9685-ff5bb260df2e (Balanced)
Power Scheme GUID: 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c (High performance) *
Power Scheme GUID: a1841308-3541-4fab-bc81-f71556f20b4a (Power saver)

# 5. 硬件监控配置
# 安装硬件监控工具
# 配置硬件告警

# 1. 操作系统配置
# 安装Windows Server 2022
# 配置操作系统参数

# 禁用自动关闭硬盘
powercfg /change -disk-timeout-ac 0
powercfg /change -disk-timeout-dc 0

# 禁用休眠
powercfg /hibernate off

# 配置虚拟内存
wmic pagefile set InitialSize=32768,MaximumSize=65536

# 2. SQLServer安装
# 下载SQLServer 2019 Enterprise Edition
# 安装SQLServer

# 3. SQLServer配置
# 配置最大内存
EXEC sp_configure ‘max server memory (MB)’, 1048576;
RECONFIGURE;

# 配置并行度
EXEC sp_configure ‘max degree of parallelism’, 8;
RECONFIGURE;

# 配置并行查询阈值
EXEC sp_configure ‘cost threshold for parallelism’, 50;
RECONFIGURE;

# 配置TempDB
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 1024MB, FILEGROWTH = 512MB);
GO

— 添加多个TempDB文件
ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = ‘D:\\SQLServer\\Data\\tempdb2.mdf’, SIZE = 1024MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb ADD FILE (NAME = tempdev3, FILENAME = ‘D:\\SQLServer\\Data\\tempdb3.mdf’, SIZE = 1024MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb ADD FILE (NAME = tempdev4, FILENAME = ‘D:\\SQLServer\\Data\\tempdb4.mdf’, SIZE = 1024MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb ADD FILE (NAME = tempdev5, FILENAME = ‘D:\\SQLServer\\Data\\tempdb5.mdf’, SIZE = 1024MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb ADD FILE (NAME = tempdev6, FILENAME = ‘D:\\SQLServer\\Data\\tempdb6.mdf’, SIZE = 1024MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb ADD FILE (NAME = tempdev7, FILENAME = ‘D:\\SQLServer\\Data\\tempdb7.mdf’, SIZE = 1024MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb ADD FILE (NAME = tempdev8, FILENAME = ‘D:\\SQLServer\\Data\\tempdb8.mdf’, SIZE = 1024MB, FILEGROWTH = 512MB);
GO

# 4. 高可用配置
# 配置Always On可用性组
# 启用Always On功能
EXEC sp_configure ‘show advanced options’, 1;
RECONFIGURE;
EXEC sp_configure ‘hadr enabled’, 1;
RECONFIGURE;

# 重启SQLServer
Restart-Service -Name “MSSQLSERVER”

# 5. 备份配置
# 创建备份目录
New-Item -Path “D:\\SQLServer\\Backup” -ItemType Directory

# 配置备份作业
USE [msdb]
GO
EXEC dbo.sp_add_job
@job_name = N’Full Backup’,
@enabled = 1,
@description = N’Daily full backup’
GO

EXEC dbo.sp_add_jobstep
@job_name = N’Full Backup’,
@step_name = N’Backup Database’,
@subsystem = N’TSQL’,
@command = N’BACKUP DATABASE fgedudb TO DISK = ”D:\\SQLServer\\Backup\\fgedudb_full.bak” WITH COMPRESSION’,
@database_name = N’master’
GO

EXEC dbo.sp_add_jobschedule
@job_name = N’Full Backup’,
@name = N’Daily Schedule’,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_start_time = 20000
GO

# 1. 测试环境准备
# 创建测试数据库
CREATE DATABASE fgedu_test;
GO

# 创建测试表
USE fgedu_test;
GO

CREATE TABLE dbo.fgedu_users (
id INT PRIMARY KEY IDENTITY,
username NVARCHAR(50) NOT NULL,
email NVARCHAR(100) NOT NULL,
password NVARCHAR(100) NOT NULL,
create_time DATETIME DEFAULT GETDATE()
);
GO

CREATE TABLE dbo.fgedu_orders (
id INT PRIMARY KEY IDENTITY,
user_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL,
order_time DATETIME DEFAULT GETDATE(),
status INT NOT NULL
);
GO

# 创建索引
CREATE INDEX IX_fgedu_orders_user ON dbo.fgedu_orders(user_id);
GO

# 插入测试数据
— 插入100万用户数据
DECLARE @i INT = 1;
BEGIN TRAN
WHILE @i <= 1000000 BEGIN INSERT INTO dbo.fgedu_users (username, email, password) VALUES ('user' + CAST(@i AS NVARCHAR), 'user' + CAST(@i AS NVARCHAR) + '@example.com', 'password' + CAST(@i AS NVARCHAR)); SET @i = @i + 1; IF @i % 1000 = 0 BEGIN COMMIT TRAN; BEGIN TRAN; END; END; COMMIT TRAN; GO -- 插入500万订单数据 DECLARE @i INT = 1; BEGIN TRAN WHILE @i <= 5000000 BEGIN INSERT INTO dbo.fgedu_orders (user_id, product_id, quantity, total_amount, status) VALUES (ABS(CHECKSUM(NEWID())) % 1000000 + 1, ABS(CHECKSUM(NEWID())) % 100000 + 1, ABS(CHECKSUM(NEWID())) % 10 + 1, (ABS(CHECKSUM(NEWID())) % 1000 + 1) * (ABS(CHECKSUM(NEWID())) % 10 + 1), ABS(CHECKSUM(NEWID())) % 5); SET @i = @i + 1; IF @i % 1000 = 0 BEGIN COMMIT TRAN; BEGIN TRAN; END; END; COMMIT TRAN; GO # 2. 性能测试 # 使用SQLServer Profiler进行测试 # 测试查询性能 DECLARE @start_time DATETIME = GETDATE(); SELECT u.id, u.username, u.email, COUNT(o.id) AS order_count, SUM(o.total_amount) AS total_spent FROM dbo.fgedu_users u LEFT JOIN dbo.fgedu_orders o ON u.id = o.user_id WHERE u.id BETWEEN 1 AND 10000 GROUP BY u.id, u.username, u.email ORDER BY total_spent DESC; DECLARE @end_time DATETIME = GETDATE(); SELECT DATEDIFF(MS, @start_time, @end_time) AS execution_time_ms; GO execution_time_ms ---------------- 500 # 测试并发性能 # 使用ostress工具进行并发测试 # 安装RML Utilities # 运行并发测试 ostress.exe -S localhost -U sa -P "Fgedu@2026#SQL" -d fgedu_test -q "SELECT * FROM dbo.fgedu_users WHERE id = ?" -n 100 -r 1000 # 3. 测试结果分析 # 分析CPU使用情况 SELECT counter_name, cntr_value FROM sys.dm_os_performance_counters WHERE object_name LIKE '%Processor%' AND counter_name LIKE '%% Processor Time%'; GO counter_name cntr_value -------------------------- ---------- % Processor Time 25 # 分析内存使用情况 SELECT counter_name, cntr_value FROM sys.dm_os_performance_counters WHERE object_name LIKE '%Memory%'; GO counter_name cntr_value -------------------------- ---------- Available MBytes 32768 # 分析IO使用情况 SELECT counter_name, cntr_value FROM sys.dm_os_performance_counters WHERE object_name LIKE '%Disk%' AND instance_name = 'D:'; GO counter_name cntr_value -------------------------- ---------- Disk Reads/sec 1000 Disk Writes/sec 500

# 1. 测试工具准备
# 下载并安装SQLServer Distributed Replay

# 2. 捕获工作负载
# 启动分布式重放管理工具
# 配置捕获设置
# 开始捕获

# 3. 重放工作负载
# 配置重放设置
# 设置并发用户数：1000
# 设置持续时间：1小时
# 开始重放

# 4. 监控系统状态
# 监控CPU使用率
Get-Counter -Counter “\Processor(_Total)\% Processor Time” -SampleInterval 5 -MaxSamples 12

Timestamp CounterSamples
——— ————–
2026-04-08 10:00:00 \SERVER\Processor(_Total)\% Processor Time : 30
2026-04-08 10:00:05 \SERVER\Processor(_Total)\% Processor Time : 35
2026-04-08 10:00:10 \SERVER\Processor(_Total)\% Processor Time : 40
2026-04-08 10:00:15 \SERVER\Processor(_Total)\% Processor Time : 38
2026-04-08 10:00:20 \SERVER\Processor(_Total)\% Processor Time : 32

# 监控内存使用
Get-Counter -Counter “\Memory\Available MBytes” -SampleInterval 5 -MaxSamples 12

Timestamp CounterSamples
——— ————–
2026-04-08 10:00:00 \SERVER\Memory\Available MBytes : 30720
2026-04-08 10:00:05 \SERVER\Memory\Available MBytes : 29696
2026-04-08 10:00:10 \SERVER\Memory\Available MBytes : 28672
2026-04-08 10:00:15 \SERVER\Memory\Available MBytes : 27648
2026-04-08 10:00:20 \SERVER\Memory\Available MBytes : 26624

# 监控磁盘IO
Get-Counter -Counter “\PhysicalDisk(_Total)\Disk Reads/sec”, “\PhysicalDisk(_Total)\Disk Writes/sec” -SampleInterval 5 -MaxSamples 12

Timestamp CounterSamples
——— ————–
2026-04-08 10:00:00 \SERVER\PhysicalDisk(_Total)\Disk Reads/sec : 1200
\SERVER\PhysicalDisk(_Total)\Disk Writes/sec : 600
2026-04-08 10:00:05 \SERVER\PhysicalDisk(_Total)\Disk Reads/sec : 1300
\SERVER\PhysicalDisk(_Total)\Disk Writes/sec : 650
2026-04-08 10:00:10 \SERVER\PhysicalDisk(_Total)\Disk Reads/sec : 1250
\SERVER\PhysicalDisk(_Total)\Disk Writes/sec : 620

# 5. 测试结果分析
# 分析响应时间
SELECT
AVG(execution_time_ms) AS avg_response_time,
MAX(execution_time_ms) AS max_response_time,
MIN(execution_time_ms) AS min_response_time
FROM test_results;

# 分析系统稳定性
SELECT
counter_name,
MIN(cntr_value) AS min_value,
MAX(cntr_value) AS max_value,
AVG(cntr_value) AS avg_value
FROM performance_counters
GROUP BY counter_name;

# 6. 优化建议
# 根据测试结果调整配置
# 优化查询和索引
# 调整硬件配置

# 1. 每日维护任务
# 检查数据库状态
SELECT
name,
state_desc,
recovery_model_desc,
log_reuse_wait_desc
FROM sys.databases
WHERE name NOT IN (‘master’, ‘model’, ‘msdb’, ‘tempdb’);
GO

# 检查备份状态
SELECT
database_name,
type,
backup_finish_date,
backup_size
FROM msdb.dbo.backupset
WHERE database_name = ‘fgedudb’
ORDER BY backup_finish_date DESC
TOP 10;
GO

# 检查作业状态
SELECT
name,
last_run_date,
last_run_time,
last_run_outcome
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
WHERE h.step_id = 0
ORDER BY h.instance_id DESC;
GO

# 2. 每周维护任务
# 重建索引
USE fgedudb;
GO

DECLARE @table_name NVARCHAR(255);
DECLARE @sql NVARCHAR(MAX);

DECLARE table_cursor CURSOR FOR
SELECT name FROM sys.tables WHERE schema_id = SCHEMA_ID(‘dbo’);

OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @table_name;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘ALTER INDEX ALL ON dbo.’ + QUOTENAME(@table_name) + ‘ REBUILD WITH (FILLFACTOR = 90);’;
EXEC sp_executesql @sql;
FETCH NEXT FROM table_cursor INTO @table_name;
END;

CLOSE table_cursor;
DEALLOCATE table_cursor;
GO

# 更新统计信息
USE fgedudb;
GO

EXEC sp_updatestats;
GO

# 检查数据库完整性
DBCC CHECKDB(‘fgedudb’) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO

# 3. 每月维护任务
# 检查磁盘空间
EXEC xp_fixeddrives;
GO

# 检查SQLServer错误日志
EXEC xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, N’DESC’;
GO

# 检查系统事件日志
Get-EventLog -LogName System -EntryType Error -Newest 20

# 4. 季度维护任务
# 应用SQLServer补丁
# 检查服务器硬件状态
# 备份系统配置
# 进行灾难恢复测试

# 5. 维护计划自动化
# 创建维护计划
USE [msdb]
GO
EXEC dbo.sp_add_job
@job_name = N’Daily Maintenance’,
@enabled = 1,
@description = N’Daily maintenance tasks’
GO

EXEC dbo.sp_add_jobstep
@job_name = N’Daily Maintenance’,
@step_name = N’Check Database Status’,
@subsystem = N’TSQL’,
@command = N’EXEC dbo.CheckDatabaseStatus’,
@database_name = N’master’
GO

EXEC dbo.sp_add_jobstep
@job_name = N’Daily Maintenance’,
@step_name = N’Backup Database’,
@subsystem = N’TSQL’,
@command = N’BACKUP DATABASE fgedudb TO DISK = ”D:\\SQLServer\\Backup\\fgedudb_full.bak” WITH COMPRESSION’,
@database_name = N’master’
GO

EXEC dbo.sp_add_jobschedule
@job_name = N’Daily Maintenance’,
@name = N’Daily Schedule’,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_start_time = 20000
GO