# 实施步骤：

# 1. 评审准备
# 收集架构文档
– 数据库设计文档
– 系统架构文档
– 数据模型文档
– 业务需求文档

# 组建评审团队
– DBA专家：张工
– 系统架构师：李工
– 开发人员：王工
– 业务分析师：赵工

# 2. 架构评审执行
# 审查数据库设计
SELECT
t.name AS table_name,
c.name AS column_name,
ty.name AS data_type,
c.max_length,
c.is_nullable
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.types ty ON c.system_type_id = ty.system_type_id
WHERE t.name LIKE ‘fgedu%’
ORDER BY t.name, c.column_id;
GO

table_name column_name data_type max_length is_nullable
————– ————– ———— ———- ———–
fgedu_users id int 4 0
fgedu_users username nvarchar 50 0
fgedu_users email nvarchar 100 0
fgedu_users password nvarchar 100 0
fgedu_users create_time datetime 8 0
fgedu_orders id int 4 0
fgedu_orders user_id int 4 0
fgedu_orders product_id int 4 0
fgedu_orders quantity int 4 0
fgedu_orders total_amount decimal 17 0
fgedu_orders order_time datetime 8 0
fgedu_orders status int 4 0
fgedu_products id int 4 0
fgedu_products name nvarchar 100 0
fgedu_products price decimal 17 0
fgedu_products description nvarchar 1000 1
fgedu_products stock int 4 0

# 审查索引设计
SELECT
t.name AS table_name,
i.name AS index_name,
i.type_desc AS index_type,
COL_NAME(i.object_id, ic.column_id) AS column_name
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE t.name LIKE ‘fgedu%’
ORDER BY t.name, i.name, ic.key_ordinal;
GO

table_name index_name index_type column_name
————– ——————– ————- ————
fgedu_users PK_fgedu_users_id CLUSTERED id
fgedu_orders PK_fgedu_orders_id CLUSTERED id
fgedu_orders IX_fgedu_orders_user NONCLUSTERED user_id
fgedu_products PK_fgedu_products_id CLUSTERED id

# 审查存储过程
SELECT
name,
type_desc,
create_date
FROM sys.objects
WHERE type = ‘P’
AND name LIKE ‘fgedu%’
ORDER BY name;
GO

name type_desc create_date
——————— ———— —————————
fgedu_create_order PROCEDURE 2026-04-01 10:00:00.000
fgedu_get_order_info PROCEDURE 2026-04-01 10:00:00.000
fgedu_get_product_info PROCEDURE 2026-04-01 10:00:00.000
fgedu_get_user_info PROCEDURE 2026-04-01 10:00:00.000
fgedu_update_product PROCEDURE 2026-04-01 10:00:00.000

# 3. 性能分析
# 分析查询性能
EXEC dbo.fgedu_get_order_info @order_id = 1;
GO

order_id user_id product_id quantity total_amount order_time status
———– ———– ———– ———– ———— ———————– ———–
1 1 1 2 200.00 2026-04-01 10:00:00.000 1

# 分析执行计划
SET SHOWPLAN_XML ON;
GO
EXEC dbo.fgedu_get_order_info @order_id = 1;
GO
SET SHOWPLAN_XML OFF;
GO

# 4. 安全审查
# 审查用户权限
SELECT
p.name AS principal_name,
r.name AS role_name
FROM sys.database_principals p
LEFT JOIN sys.database_role_members rm ON p.principal_id = rm.member_principal_id
LEFT JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
WHERE p.type IN (‘S’, ‘U’)
ORDER BY p.name;
GO

principal_name role_name
————– ——————–
fgedu db_owner
fgedu_app db_datareader
fgedu_app db_datawriter

# 5. 评审发现
## 问题1：缺少必要的索引
– 影响：fgedu_orders表按product_id查询时性能差
– 建议：创建product_id的非聚集索引

## 问题2：存储过程使用SELECT *
– 影响：fgedu_get_product_info存储过程使用SELECT *，影响性能
– 建议：只选择必要的列

## 问题3：权限管理不规范
– 影响：fgedu用户拥有db_owner权限，权限过大
– 建议：调整权限，遵循最小权限原则

# 6. 改进建议
## 短期改进
1. 创建fgedu_orders表的product_id索引
2. 优化存储过程，避免使用SELECT *
3. 调整用户权限

## 中期改进
1. 配置完整的备份策略
2. 加强监控体系
3. 优化查询性能

# 7. 验证改进效果
# 创建索引
CREATE INDEX IX_fgedu_orders_product ON dbo.fgedu_orders(product_id);
GO

# 优化存储过程
ALTER PROCEDURE dbo.fgedu_get_product_info
@product_id INT
AS
BEGIN
SET NOCOUNT ON;

SELECT
id,
name,
price,
stock
FROM dbo.fgedu_products
WHERE id = @product_id;
END;
GO

# 调整权限
REVOKE CONTROL ON DATABASE::fgedudb FROM fgedu;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.fgedu_users TO fgedu;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.fgedu_orders TO fgedu;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.fgedu_products TO fgedu;
GRANT EXECUTE ON dbo.fgedu_create_order TO fgedu;
GRANT EXECUTE ON dbo.fgedu_get_order_info TO fgedu;
GRANT EXECUTE ON dbo.fgedu_get_product_info TO fgedu;
GRANT EXECUTE ON dbo.fgedu_get_user_info TO fgedu;
GRANT EXECUTE ON dbo.fgedu_update_product TO fgedu;
GO

# 实施步骤：

# 1. 评审准备
# 收集架构文档
– 数据库设计文档
– 系统架构文档
– 性能测试报告
– 故障记录

# 组建评审团队
– DBA专家：张工
– 系统架构师：李工
– 开发人员：王工
– 业务分析师：赵工

# 2. 架构评审执行
# 审查系统架构
SELECT
@@SERVERNAME AS server_name,
SERVERPROPERTY(‘ProductVersion’) AS version,
SERVERPROPERTY(‘ProductLevel’) AS service_pack,
SERVERPROPERTY(‘Edition’) AS edition;
GO

server_name version service_pack edition
—————– ———- ———— ——————————
prod-fgedu-sql01 15.0.4198.2 RTM Enterprise Edition

# 审查数据库状态
SELECT
name,
state_desc,
recovery_model_desc,
log_reuse_wait_desc
FROM sys.databases
WHERE name NOT IN (‘master’, ‘model’, ‘msdb’, ‘tempdb’);
GO

name state_desc recovery_model_desc log_reuse_wait_desc
———- ———- —————– ——————-
fgedudb ONLINE FULL NOTHING

# 审查存储使用情况
SELECT
DB_NAME(database_id) AS database_name,
type_desc,
SUM(size) * 8 / 1024 AS size_mb
FROM sys.master_files
WHERE database_id = DB_ID(‘fgedudb’)
GROUP BY database_id, type_desc;
GO

database_name type_desc size_mb
————- ———— ——–
fgedudb ROWS 10240
fgedudb LOG 2048

# 3. 性能分析
# 分析CPU使用情况
SELECT
TOP 10
total_worker_time / execution_count AS avg_cpu_time,
execution_count,
SUBSTRING(text, 1, 200) AS query_text
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY avg_cpu_time DESC;
GO

avg_cpu_time execution_count query_text
———— ————— ——————————————————-
5000 1000 SELECT * FROM fgedu_transactions WHERE account_id = @account_id
2000 5000 SELECT * FROM fgedu_accounts WHERE customer_id = @customer_id
1000 10000 SELECT * FROM fgedu_customers WHERE id = @id

# 分析内存使用情况
SELECT
physical_memory_in_use_kb / 1024 AS physical_memory_mb,
locked_page_allocations_kb / 1024 AS locked_memory_mb,
total_server_memory_kb / 1024 AS total_server_memory_mb,
max_server_memory_kb / 1024 AS max_server_memory_mb
FROM sys.dm_os_process_memory;
GO

physical_memory_mb locked_memory_mb total_server_memory_mb max_server_memory_mb
—————— —————- ——————— ——————-
16384 0 16384 32768

# 分析IO使用情况
SELECT
database_id,
DB_NAME(database_id) AS database_name,
num_of_reads,
num_of_writes,
num_of_bytes_read / 1024 / 1024 AS read_mb,
num_of_bytes_written / 1024 / 1024 AS written_mb
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
WHERE database_id = DB_ID(‘fgedudb’)
ORDER BY num_of_reads + num_of_writes DESC;
GO

database_id database_name num_of_reads num_of_writes read_mb written_mb
———– ————- ———— ————- ———- ———–
fgedudb 5 100000 50000 5000 2500

# 4. 高可用审查
# 审查Always On配置
SELECT
ag.name AS availability_group,
ar.replica_server_name,
ar.availability_mode_desc,
ar.failover_mode_desc,
hars.role_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states hars ON ar.replica_id = hars.replica_id;
GO

availability_group replica_server_name availability_mode_desc failover_mode_desc role_desc
—————— ——————- ——————— —————– ———
fgedu-ag prod-fgedu-sql01 SYNCHRONOUS_COMMIT AUTOMATIC PRIMARY
fgedu-ag prod-fgedu-sql02 SYNCHRONOUS_COMMIT AUTOMATIC SECONDARY

# 5. 评审发现
## 问题1：查询性能问题
– 影响：fgedu_transactions表的查询平均CPU时间为5000ms，性能差
– 建议：优化查询，添加适当的索引

## 问题2：内存配置问题
– 影响：max_server_memory设置为32GB，但物理内存只有16GB，配置不合理
– 建议：调整max_server_memory设置，避免内存不足

## 问题3：高可用架构问题
– 影响：只有2个副本，没有见证服务器，自动故障转移可能失败
– 建议：添加见证服务器，确保自动故障转移的可靠性

# 6. 改进建议
## 短期改进
1. 优化fgedu_transactions表的查询性能，添加account_id的非聚集索引
2. 调整max_server_memory设置为12GB，留4GB给操作系统
3. 加强监控体系，配置必要的监控和告警

## 中期改进
1. 完善高可用架构，添加见证服务器
2. 优化存储架构，提高IO性能
3. 实施分区表，提高大表的查询性能

# 7. 验证改进效果
# 创建索引
CREATE INDEX IX_fgedu_transactions_account ON dbo.fgedu_transactions(account_id);
GO

# 调整内存配置
EXEC sp_configure ‘max server memory (MB)’, 12288;
RECONFIGURE;
GO

# 验证改进效果
SELECT
total_worker_time / execution_count AS avg_cpu_time,
execution_count
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE text LIKE ‘%fgedu_transactions%’
AND text LIKE ‘%account_id%’;
GO

avg_cpu_time execution_count
———— —————
500 100

# 实施步骤：

# 1. 评审准备
# 收集性能数据
– 慢查询日志
– 性能计数器数据
– 执行计划
– 系统资源使用情况

# 组建评审团队
– DBA专家：张工
– 系统架构师：李工
– 开发人员：王工
– 性能专家：刘工

# 2. 架构评审执行
# 分析慢查询
SELECT
TOP 10
qs.total_worker_time / qs.execution_count AS avg_cpu_time,
qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
qs.execution_count,
SUBSTRING(qt.text, qs.statement_start_offset/2 + 1,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END – qs.statement_start_offset)/2) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_elapsed_time DESC;
GO

avg_cpu_time avg_elapsed_time execution_count query_text
————- —————- ————— ——————————————————-
10000 15000 500 SELECT * FROM fgedu_orders WHERE order_date BETWEEN @start_date AND @end_date
5000 8000 1000 SELECT * FROM fgedu_products WHERE category_id = @category_id
2000 3000 2000 SELECT * FROM fgedu_users WHERE created_date > @date

# 分析索引使用情况
SELECT
OBJECT_NAME(i.object_id) AS table_name,
i.name AS index_name,
i.type_desc AS index_type,
s.user_seeks,
s.user_scans,
s.user_lookups,
s.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE i.object_id IN (OBJECT_ID(‘fgedu_orders’), OBJECT_ID(‘fgedu_products’), OBJECT_ID(‘fgedu_users’))
ORDER BY OBJECT_NAME(i.object_id), i.name;
GO

table_name index_name index_type user_seeks user_scans user_lookups user_updates
————– ——————– ————- ———- ———- ———— ————
fgedu_orders PK_fgedu_orders_id CLUSTERED 1000 500 0 2000
fgedu_products PK_fgedu_products_id CLUSTERED 2000 1000 0 1000
fgedu_users PK_fgedu_users_id CLUSTERED 3000 500 0 500

# 分析表结构
SELECT
t.name AS table_name,
p.rows AS row_count,
SUM(a.total_pages) * 8 / 1024 AS total_space_mb,
SUM(a.used_pages) * 8 / 1024 AS used_space_mb,
(SUM(a.total_pages) – SUM(a.used_pages)) * 8 / 1024 AS unused_space_mb
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.name IN (‘fgedu_orders’, ‘fgedu_products’, ‘fgedu_users’)
GROUP BY t.name, p.rows
ORDER BY total_space_mb DESC;
GO

table_name row_count total_space_mb used_space_mb unused_space_mb
————– ———- ————– ————- —————
fgedu_orders 1000000 5000 4500 500
fgedu_products 500000 2000 1800 200
fgedu_users 100000 500 450 50

# 3. 性能瓶颈分析
## 瓶颈1：缺少必要的索引
– 影响：fgedu_orders表按order_date查询时需要全表扫描
– 建议：创建order_date的非聚集索引

## 瓶颈2：表结构不合理
– 影响：fgedu_orders表有100万行数据，查询性能差
– 建议：实施表分区，按order_date分区

## 瓶颈3：存储过程优化不足
– 影响：存储过程使用SELECT *，查询效率低
– 建议：优化存储过程，只选择必要的列

# 4. 改进建议
## 短期改进
1. 创建fgedu_orders表的order_date索引
2. 创建fgedu_products表的category_id索引
3. 创建fgedu_users表的created_date索引
4. 优化存储过程，避免使用SELECT *

## 中期改进
1. 实施表分区，按order_date分区fgedu_orders表
2. 优化存储架构，提高IO性能
3. 配置查询计划缓存，提高查询效率

## 长期改进
1. 考虑使用列存储索引，提高分析查询性能
2. 实施读写分离，提高系统并发能力
3. 优化应用程序设计，减少数据库负载

# 5. 验证改进效果
# 创建索引
CREATE INDEX IX_fgedu_orders_date ON dbo.fgedu_orders(order_date);
CREATE INDEX IX_fgedu_products_category ON dbo.fgedu_products(category_id);
CREATE INDEX IX_fgedu_users_created ON dbo.fgedu_users(created_date);
GO

# 优化存储过程
ALTER PROCEDURE dbo.fgedu_get_orders_by_date
@start_date DATETIME,
@end_date DATETIME
AS
BEGIN
SET NOCOUNT ON;

SELECT
id,
user_id,
total_amount,
order_date,
status
FROM dbo.fgedu_orders
WHERE order_date BETWEEN @start_date AND @end_date;
END;
GO

# 验证改进效果
EXEC dbo.fgedu_get_orders_by_date @start_date = ‘2026-01-01’, @end_date = ‘2026-01-31’;
GO

— 执行时间：之前15秒，现在1秒

# 实施表分区
— 创建分区函数
CREATE PARTITION FUNCTION pf_fgedu_orders_date (DATETIME)
AS RANGE RIGHT FOR VALUES
(‘2026-01-01’, ‘2026-02-01’, ‘2026-03-01’, ‘2026-04-01’,
‘2026-05-01’, ‘2026-06-01’, ‘2026-07-01’, ‘2026-08-01’,
‘2026-09-01’, ‘2026-10-01’, ‘2026-11-01’, ‘2026-12-01’);
GO

— 创建分区方案
CREATE PARTITION SCHEME ps_fgedu_orders_date
AS PARTITION pf_fgedu_orders_date
ALL TO ([PRIMARY]);
GO

— 重建索引，使用分区方案
CREATE CLUSTERED INDEX PK_fgedu_orders_id
ON dbo.fgedu_orders(id)
WITH (DROP_EXISTING = ON)
ON ps_fgedu_orders_date(order_date);
GO

# 验证分区效果
SELECT
partition_number,
rows
FROM sys.partitions
WHERE object_id = OBJECT_ID(‘fgedu_orders’);
GO

partition_number rows
—————- —–
1 80000
2 85000
3 90000
4 95000
5 100000
6 105000
7 110000
8 115000
9 120000
10 80000
11 75000
12 40000