# 1. 使用DMV识别慢查询
— 查找执行时间最长的查询
SELECT
TOP 10
qs.total_elapsed_time / 1000000.0 AS total_elapsed_time_sec,
qs.execution_count,
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS avg_elapsed_time_sec,
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 qs.total_elapsed_time DESC;
GO

— 查找逻辑读取最多的查询
SELECT
TOP 10
qs.total_logical_reads,
qs.execution_count,
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
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 qs.total_logical_reads DESC;
GO

— 查找物理读取最多的查询
SELECT
TOP 10
qs.total_physical_reads,
qs.execution_count,
qs.total_physical_reads / qs.execution_count AS avg_physical_reads,
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 qs.total_physical_reads DESC;
GO

# 2. 使用SQL Server Profiler识别慢查询
— 启动SQL Server Profiler
— 创建跟踪，选择事件：RPC:Completed, SQL:BatchCompleted
— 设置筛选器：Duration > 1000ms
— 运行跟踪，收集慢查询

# 3. 使用Extended Events识别慢查询
— 创建Extended Events会话
CREATE EVENT SESSION [SlowQueries] ON SERVER
ADD EVENT sqlserver.rpc_completed(
ACTION(sqlserver.database_name,sqlserver.sql_text,sqlserver.username)
WHERE ([duration]>(1000000))),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(sqlserver.database_name,sqlserver.sql_text,sqlserver.username)
WHERE ([duration]>(1000000)))
ADD TARGET package0.event_file(
SET filename=N’/sqlserver/fgdata/SlowQueries.xel’,max_file_size=(50),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=OFF,STARTUP_STATE=OFF);
GO

— 启动会话
ALTER EVENT SESSION [SlowQueries] ON SERVER STATE = START;
GO

— 查看收集的慢查询
SELECT
event_data = CONVERT(xml, event_data)
FROM sys.fn_xe_file_target_read_file(‘/sqlserver/fgdata/SlowQueries*.xel’, NULL, NULL, NULL);
GO

# 1. 查看估计执行计划
— 在SSMS中，选中查询，按Ctrl+L查看估计执行计划
— 或使用SET SHOWPLAN_XML ON
SET SHOWPLAN_XML ON;
GO

SELECT
*
FROM dbo.fgedu_users
WHERE username = ‘user1’;
GO

SET SHOWPLAN_XML OFF;
GO

# 2. 查看实际执行计划
— 在SSMS中，选中查询，按Ctrl+M启用实际执行计划
— 或使用SET STATISTICS XML ON
SET STATISTICS XML ON;
GO

SELECT
*
FROM dbo.fgedu_users
WHERE username = ‘user1’;
GO

SET STATISTICS XML OFF;
GO

# 3. 分析执行计划运算符
— 常见运算符及其含义：
— Clustered Index Scan: 聚集索引扫描（全表扫描）
— Nonclustered Index Scan: 非聚集索引扫描
— Clustered Index Seek: 聚集索引查找
— Nonclustered Index Seek: 非聚集索引查找
— Key Lookup: 键查找（书签查找）
— RID Lookup: RID查找（堆表的书签查找）
— Hash Match: 哈希匹配（用于JOIN）
— Nested Loops: 嵌套循环（用于JOIN）
— Merge Join: 合并连接（用于JOIN）
— Sort: 排序
— Filter: 过滤
— Compute Scalar: 计算标量

# 4. 使用DMV查看缓存的执行计划
SELECT
TOP 10
usecounts,
cacheobjtype,
objtype,
TEXT
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE TEXT LIKE ‘%fgedu_users%’
ORDER BY usecounts DESC;
GO

# 5. 分析执行计划性能指标
— 查看执行计划的逻辑读取、物理读取、CPU时间等
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

SELECT
*
FROM dbo.fgedu_users
WHERE username = ‘user1’;
GO

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

# 6. 识别性能瓶颈
— 查找执行计划中的昂贵运算符
— 关注高成本的扫描操作
— 分析JOIN操作的成本
— 检查排序和哈希操作的成本

# 1. 避免使用SELECT *
— 不好的写法
SELECT * FROM dbo.fgedu_users;

— 好的写法
SELECT id, username, email FROM dbo.fgedu_users;

# 2. 使用参数化查询
— 不好的写法
DECLARE @username NVARCHAR(50) = ‘user1’;
EXEC(‘SELECT * FROM dbo.fgedu_users WHERE username = ”’ + @username + ””);

— 好的写法
DECLARE @username NVARCHAR(50) = ‘user1’;
SELECT * FROM dbo.fgedu_users WHERE username = @username;

# 3. 优化WHERE子句
— 不好的写法（使用函数导致索引失效）
SELECT * FROM dbo.fgedu_users WHERE YEAR(create_time) = 2023;

— 好的写法
SELECT * FROM dbo.fgedu_users WHERE create_time BETWEEN ‘2023-01-01’ AND ‘2023-12-31’;

# 4. 优化JOIN操作
— 不好的写法（笛卡尔积）
SELECT * FROM dbo.fgedu_users, dbo.fgedu_orders;

— 好的写法（使用INNER JOIN）
SELECT * FROM dbo.fgedu_users u INNER JOIN dbo.fgedu_orders o ON u.id = o.user_id;

# 5. 避免子查询
— 不好的写法（相关子查询）
SELECT
id,
username,
(SELECT COUNT(*) FROM dbo.fgedu_orders WHERE user_id = u.id) AS order_count
FROM dbo.fgedu_users u;

— 好的写法（使用JOIN）
SELECT
u.id,
u.username,
COUNT(o.id) AS order_count
FROM dbo.fgedu_users u
LEFT JOIN dbo.fgedu_orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

# 6. 使用TOP限制结果集
— 不好的写法（返回所有行）
SELECT * FROM dbo.fgedu_users ORDER BY create_time DESC;

— 好的写法（限制返回行数）
SELECT TOP 10 * FROM dbo.fgedu_users ORDER BY create_time DESC;

# 7. 优化ORDER BY和GROUP BY
— 确保ORDER BY和GROUP BY的列有索引
— 避免在ORDER BY中使用函数
— 考虑使用覆盖索引

# 8. 使用UNION ALL替代UNION
— 不好的写法（UNION会去重，性能较差）
SELECT id, username FROM dbo.fgedu_users WHERE status = ‘Active’
UNION
SELECT id, username FROM dbo.fgedu_users WHERE status = ‘Inactive’;

— 好的写法（UNION ALL不会去重，性能较好）
SELECT id, username FROM dbo.fgedu_users WHERE status = ‘Active’
UNION ALL
SELECT id, username FROM dbo.fgedu_users WHERE status = ‘Inactive’;

# 1. 识别缺失索引
SELECT
TOP 10
dm_mid.database_id,
dm_mid.physical_operator_name,
dm_mid.est_cost AS estimated_cost,
dm_migs.avg_total_user_cost * dm_migs.avg_user_impact * (dm_migs.user_seeks + dm_migs.user_scans) AS improvement_measure,
‘CREATE INDEX [IX_’ + OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) + ‘_’
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns, ”), ‘, ‘, ‘_’), ‘[‘, ”), ‘]’, ”)
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ‘_’
ELSE ”
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns, ”), ‘, ‘, ‘_’), ‘[‘, ”), ‘]’, ”)
+ ‘]’
+ ‘ ON ‘ + dm_migs.statement
+ ‘ (‘ + ISNULL(dm_mid.equality_columns, ”)
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ‘, ‘ ELSE ” END
+ ISNULL(dm_mid.inequality_columns, ”)
+ ‘)’
+ ISNULL(‘ INCLUDE (‘ + dm_mid.included_columns + ‘)’, ”) AS create_index_statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs ON dm_mig.index_group_handle = dm_migs.group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid ON dm_mig.index_handle = dm_mid.index_handle
ORDER BY dm_migs.avg_total_user_cost * dm_migs.avg_user_impact * (dm_migs.user_seeks + dm_migs.user_scans) DESC;
GO

# 2. 检查索引碎片
SELECT
DB_NAME(database_id) AS database_name,
OBJECT_NAME(object_id) AS table_name,
index_id,
name AS index_name,
avg_fragmentation_in_percent,
fragment_count,
page_count
FROM sys.dm_db_index_physical_stats(
DB_ID(),
NULL,
NULL,
NULL,
‘DETAILED’
) WHERE avg_fragmentation_in_percent > 10;
GO

# 3. 重建碎片化索引
— 重建索引
ALTER INDEX ALL ON dbo.fgedu_users REBUILD;
GO

— 重组索引
ALTER INDEX ALL ON dbo.fgedu_users REORGANIZE;
GO

# 4. 删除冗余索引
SELECT
OBJECT_NAME(i.object_id) AS table_name,
i.name AS index_name,
i.index_id,
i.type_desc,
i.is_unique,
i.is_primary_key
FROM sys.indexes i
INNER JOIN sys.indexes i2 ON i.object_id = i2.object_id
WHERE i.index_id > i2.index_id
AND i.columns = i2.columns
AND i.is_unique = i2.is_unique;
GO

# 5. 优化索引填充因子
— 设置填充因子为80
ALTER INDEX IX_fgedu_users_username ON dbo.fgedu_users REBUILD WITH (FILLFACTOR = 80);
GO

# 6. 创建覆盖索引
— 创建覆盖索引
CREATE INDEX IX_fgedu_orders_user_id_status
ON dbo.fgedu_orders (user_id, status)
INCLUDE (order_date, total_amount);
GO

# 7. 监控索引使用情况
SELECT
OBJECT_NAME(s.object_id) AS table_name,
i.name AS index_name,
i.type_desc,
s.user_seeks,
s.user_scans,
s.user_lookups,
s.user_updates
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE database_id = DB_ID()
ORDER BY (s.user_seeks + s.user_scans + s.user_lookups) DESC;
GO

# 1. 性能测试
— 测试优化前性能
DECLARE @start_time DATETIME = GETDATE();

SELECT
u.id,
u.username,
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.create_time BETWEEN ‘2023-01-01’ AND ‘2023-12-31’
GROUP BY u.id, u.username
ORDER BY total_spent DESC;

DECLARE @end_time DATETIME = GETDATE();
SELECT ‘Before optimization’ AS status, DATEDIFF(MS, @start_time, @end_time) AS execution_time_ms;
GO

— 应用优化（例如添加索引）
CREATE INDEX IX_fgedu_users_create_time ON dbo.fgedu_users(create_time);
CREATE INDEX IX_fgedu_orders_user_id ON dbo.fgedu_orders(user_id);
GO

— 测试优化后性能
DECLARE @start_time DATETIME = GETDATE();

SELECT
u.id,
u.username,
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.create_time BETWEEN ‘2023-01-01’ AND ‘2023-12-31’
GROUP BY u.id, u.username
ORDER BY total_spent DESC;

DECLARE @end_time DATETIME = GETDATE();
SELECT ‘After optimization’ AS status, DATEDIFF(MS, @start_time, @end_time) AS execution_time_ms;
GO

# 2. 资源使用监控
— 监控CPU使用
SELECT
session_id,
command,
cpu_time,
total_elapsed_time,
logical_reads,
physical_reads
FROM sys.dm_exec_requests
WHERE session_id = @@SPID;
GO

— 监控内存使用
SELECT
physical_memory_in_use_kb / 1024 AS physical_memory_used_mb,
locked_page_allocations_kb / 1024 AS locked_pages_mb,
page_fault_count,
memory_utilization_percentage,
available_commit_limit_kb / 1024 AS available_commit_limit_mb
FROM sys.dm_os_process_memory;
GO

— 监控I/O使用
SET STATISTICS IO ON;
GO

SELECT
u.id,
u.username,
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.create_time BETWEEN ‘2023-01-01’ AND ‘2023-12-31’
GROUP BY u.id, u.username
ORDER BY total_spent DESC;
GO

SET STATISTICS IO OFF;
GO

# 3. 执行计划对比
— 查看优化前后的执行计划
— 比较逻辑读取、物理读取、CPU时间等指标
— 分析执行计划的变化

# 4. 生产环境验证
— 在非高峰时段进行测试
— 监控系统整体性能
— 确认优化不会影响其他查询
— 评估长期性能影响

# 验证结果评估

验证项 优化前 优化后 改进率
————————————————————
执行时间 5000ms 500ms 90.0%
逻辑读取 10000 1000 90.0%
物理读取 1000 100 90.0%
CPU时间 2000ms 200ms 90.0%
内存使用 100MB 50MB 50.0%