# 1. 检查SQLServer版本
SELECT @@VERSION;
GO

# 2. 创建测试数据库
CREATE DATABASE fgedu_plan;
GO

# 3. 创建测试表
USE fgedu_plan;
GO

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

CREATE TABLE dbo.fgedu_orders (
id INT PRIMARY KEY IDENTITY,
user_id INT NOT NULL,
order_date DATETIME DEFAULT GETDATE(),
total_amount DECIMAL(10, 2) NOT NULL,
status NVARCHAR(20) DEFAULT ‘Pending’
);
GO

# 4. 插入测试数据
— 插入100万行用户数据
DECLARE @i INT = 1;
WHILE @i <= 1000000 BEGIN INSERT INTO dbo.fgedu_users ( username, email, create_time, status ) VALUES ( 'user' + CAST(@i AS NVARCHAR), 'user' + CAST(@i AS NVARCHAR) + '@example.com', DATEADD(DAY, @i % 365, '2023-01-01'), CASE @i % 3 WHEN 0 THEN 'Active' WHEN 1 THEN 'Inactive' ELSE 'Pending' END ); SET @i = @i + 1; END; GO -- 插入500万行订单数据 DECLARE @i INT = 1; WHILE @i <= 5000000 BEGIN INSERT INTO dbo.fgedu_orders ( user_id, order_date, total_amount, status ) VALUES ( ABS(CHECKSUM(NEWID())) % 1000000 + 1, DATEADD(DAY, @i % 365, '2023-01-01'), ABS(CHECKSUM(NEWID())) % 10000 + 1, CASE @i % 4 WHEN 0 THEN 'Pending' WHEN 1 THEN 'Processing' WHEN 2 THEN 'Shipped' ELSE 'Delivered' END ); SET @i = @i + 1; END; GO # 5. 创建索引 CREATE INDEX IX_fgedu_users_create_time ON dbo.fgedu_users(create_time); GO CREATE INDEX IX_fgedu_orders_user_id ON dbo.fgedu_orders(user_id); GO # 6. 测试初始查询性能 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-06-01' AND '2023-06-30' AND u.status = 'Active' GROUP BY u.id, u.username ORDER BY total_spent DESC TOP 100; DECLARE @end_time DATETIME = GETDATE(); SELECT 'Initial performance' AS status, DATEDIFF(MS, @start_time, @end_time) AS execution_time_ms; GO

# 1. 查看估计执行计划
— 使用SSMS查看估计执行计划（Ctrl+L）
— 或使用SET SHOWPLAN_XML ON
SET SHOWPLAN_XML 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-06-01’ AND ‘2023-06-30’
AND u.status = ‘Active’
GROUP BY u.id, u.username
ORDER BY total_spent DESC
TOP 100;

GO

SET SHOWPLAN_XML OFF;
GO

# 2. 查看实际执行计划
— 使用SSMS查看实际执行计划（Ctrl+M）
— 或使用SET STATISTICS XML ON
SET STATISTICS XML 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-06-01’ AND ‘2023-06-30’
AND u.status = ‘Active’
GROUP BY u.id, u.username
ORDER BY total_spent DESC
TOP 100;

GO

SET STATISTICS XML OFF;
GO

# 3. 使用SQL Server Profiler捕获执行计划
— 启动SQL Server Profiler
— 创建跟踪，选择事件：Showplan XML
— 运行跟踪，执行查询
— 查看捕获的执行计划

# 4. 使用Extended Events捕获执行计划
— 创建Extended Events会话
CREATE EVENT SESSION [CaptureExecutionPlans] ON SERVER
ADD EVENT sqlserver.query_plan_xml(
ACTION(sqlserver.database_name,sqlserver.sql_text,sqlserver.username)
),
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/ExecutionPlans.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 [CaptureExecutionPlans] ON SERVER STATE = START;
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-06-01’ AND ‘2023-06-30’
AND u.status = ‘Active’
GROUP BY u.id, u.username
ORDER BY total_spent DESC
TOP 100;
GO

— 查看捕获的执行计划
SELECT
event_data = CONVERT(xml, event_data)
FROM sys.fn_xe_file_target_read_file(‘/sqlserver/fgdata/ExecutionPlans*.xel’, NULL, NULL, NULL);
GO

# 5. 分析执行计划
— 查看执行计划运算符
— 分析执行计划成本
— 识别性能瓶颈
— 检查索引使用情况

# 6. 查看执行计划缓存
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

# 7. 查看执行计划详细信息
SELECT
TOP 10
query_plan,
TEXT
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE TEXT LIKE ‘%fgedu_users%’
ORDER BY usecounts DESC;
GO

# 1. 查看执行计划缓存设置
— 查看最大服务器内存
SELECT
name,
value,
value_in_use
FROM sys.configurations
WHERE name LIKE ‘%memory%’;
GO

— 查看执行计划缓存大小
SELECT
cache_size_mb = CONVERT(DECIMAL(12, 2), SUM(size_in_bytes) / 1024.0 / 1024.0),
cache_count = COUNT(*)
FROM sys.dm_exec_cached_plans;
GO

# 2. 配置执行计划缓存
— 设置最大服务器内存
EXEC sp_configure ‘max server memory (MB)’, 16384;
RECONFIGURE;
GO

— 设置最小服务器内存
EXEC sp_configure ‘min server memory (MB)’, 4096;
RECONFIGURE;
GO

# 3. 监控执行计划缓存
— 查看执行计划缓存使用情况
SELECT
objtype,
cache_size_mb = CONVERT(DECIMAL(12, 2), SUM(size_in_bytes) / 1024.0 / 1024.0),
cache_count = COUNT(*),
avg_usecounts = AVG(usecounts)
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY cache_size_mb DESC;
GO

— 查看执行计划缓存详细信息
SELECT
TOP 20
usecounts,
cacheobjtype,
objtype,
size_in_bytes / 1024.0 / 1024.0 AS size_in_mb,
TEXT
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY size_in_bytes DESC;
GO

# 4. 清除执行计划缓存
— 清除所有执行计划缓存
DBCC FREEPROCCACHE;
GO

— 清除特定执行计划缓存
DECLARE @plan_handle VARBINARY(64);
SELECT @plan_handle = plan_handle
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE TEXT LIKE ‘%fgedu_users%’;

DBCC FREEPROCCACHE(@plan_handle);
GO

# 5. 优化执行计划缓存
— 使用参数化查询
DECLARE @start_date DATETIME = ‘2023-06-01’;
DECLARE @end_date DATETIME = ‘2023-06-30’;
DECLARE @status NVARCHAR(20) = ‘Active’;

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 @start_date AND @end_date
AND u.status = @status
GROUP BY u.id, u.username
ORDER BY total_spent DESC
TOP 100;
GO

— 使用存储过程
CREATE PROCEDURE dbo.GetUserOrders
@start_date DATETIME,
@end_date DATETIME,
@status NVARCHAR(20)
AS
BEGIN
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 @start_date AND @end_date
AND u.status = @status
GROUP BY u.id, u.username
ORDER BY total_spent DESC
TOP 100;
END;
GO

— 执行存储过程
EXEC dbo.GetUserOrders ‘2023-06-01’, ‘2023-06-30’, ‘Active’;
GO

# 1. SQL Server Management Studio (SSMS)配置
— 启用执行计划显示
— 工具 -> 选项 -> 查询执行 -> 高级 -> 启用执行计划

# 2. SQL Server Profiler配置
— 创建执行计划捕获模板
— 文件 -> 模板 -> 新建模板
— 选择事件：Showplan XML, Showplan Statistics Profile
— 设置筛选器：Duration > 1000ms

# 3. Extended Events配置
— 创建执行计划捕获会话
CREATE EVENT SESSION [ExecutionPlanAnalysis] ON SERVER
ADD EVENT sqlserver.query_plan_xml(
ACTION(sqlserver.database_name,sqlserver.sql_text,sqlserver.username)
),
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/ExecutionPlanAnalysis.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

# 4. Dynamic Management Views (DMVs)配置
— 启用高级DMVs
EXEC sp_configure ‘show advanced options’, 1;
RECONFIGURE;
GO

— 启用查询存储
ALTER DATABASE fgedu_plan SET QUERY_STORE = ON;
GO

— 配置查询存储
ALTER DATABASE fgedu_plan SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
DATA_FLUSH_INTERVAL_SECONDS = 900,
MAX_STORAGE_SIZE_MB = 10240,
INTERVAL_LENGTH_MINUTES = 15,
QUERY_CAPTURE_MODE = AUTO,
MAX_PLANS_PER_QUERY = 200,
WAIT_STATS_CAPTURE_MODE = ON
);
GO

# 5. 第三方工具配置
— 根据实际使用的第三方工具进行配置
— 例如：SQL Sentry Plan Explorer, Redgate SQL Monitor等

# 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-06-01’ AND ‘2023-06-30’
AND u.status = ‘Active’
GROUP BY u.id, u.username
ORDER BY total_spent DESC
TOP 100;

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_status_create_time ON dbo.fgedu_users(status, create_time);
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-06-01’ AND ‘2023-06-30’
AND u.status = ‘Active’
GROUP BY u.id, u.username
ORDER BY total_spent DESC
TOP 100;

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

# 2. 执行计划对比
— 查看优化前后的执行计划
— 比较执行计划运算符
— 比较执行计划成本
— 比较索引使用情况

# 3. 资源使用分析
— 监控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-06-01’ AND ‘2023-06-30’
AND u.status = ‘Active’
GROUP BY u.id, u.username
ORDER BY total_spent DESC
TOP 100;

GO

SET STATISTICS IO OFF;
GO

— 监控CPU使用
SET STATISTICS TIME 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-06-01’ AND ‘2023-06-30’
AND u.status = ‘Active’
GROUP BY u.id, u.username
ORDER BY total_spent DESC
TOP 100;

GO

SET STATISTICS TIME OFF;
GO

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

# 验证结果评估

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