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

# 检查数据库兼容性级别
SELECT name, compatibility_level FROM sys.databases;
GO

name compatibility_level
———– ——————-
fgedudb 150
master 150
model 150
tempdb 150
msdb 150

对于查询存储环境，需要确保SQLServer版本为2016或更高，数据库兼容性级别为130或更高。from SQLServer视频:www.itpux.com。

2.2 SQLServer查询存储配置建议

SQLServer查询存储配置建议包括：

1. 存储大小：根据数据库大小和查询量设置合理的存储大小，一般建议为数据库大小的5-10%

2. 清理策略：设置合理的清理策略，避免查询存储过大

3. 捕获模式：根据需要选择合适的查询捕获模式

4. 间隔长度：设置合理的统计信息收集间隔

5. 自动计划校正：启用自动计划校正，解决计划回归问题

更多视频教程www.fgedu.net.cn。

Part03-生产环境项目实施方案

3.1 SQLServer查询存储高级配置实施

# 启用查询存储
ALTER DATABASE fgedudb SET QUERY_STORE = ON;
GO

# 配置查询存储
ALTER DATABASE fgedudb 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,
PLAN_CAPTURE_MODE = AUTO,
SIZE_BASED_CLEANUP_MODE = AUTO,
QUERY_CAPTURE_POLICY = (
STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
EXECUTION_COUNT = 30,
TOTAL_COMPILE_CPU_TIME_MS = 1000,
TOTAL_EXECUTION_CPU_TIME_MS = 100
)
);
GO

# 查看查询存储配置
SELECT * FROM sys.database_query_store_options;
GO

database_id desired_state desired_state_desc actual_state actual_state_desc readonly_reason current_storage_size_mb max_storage_size_mb flush_interval_seconds interval_length_minutes stale_query_threshold_days size_based_cleanup_mode size_based_cleanup_mode_desc query_capture_mode query_capture_mode_desc wait_stats_capture_mode wait_stats_capture_mode_desc actual_state_additional_info
———— ————– —————— ————– —————— —————- ———————— ——————– ————————- ———————— ———————— ———————— —————————— ——————- ————————- ————————- —————————— ——————————-
5 1 READ_WRITE 1 READ_WRITE NULL 0 10240 900 15 30 1 AUTO 2 AUTO 1 ON NULL

# 优化查询存储性能

# 1. 调整存储大小
ALTER DATABASE fgedudb SET QUERY_STORE (
MAX_STORAGE_SIZE_MB = 20480
);
GO

# 2. 调整清理策略
ALTER DATABASE fgedudb SET QUERY_STORE (
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 15)
);
GO

# 3. 调整捕获模式
ALTER DATABASE fgedudb SET QUERY_STORE (
QUERY_CAPTURE_MODE = CUSTOM,
QUERY_CAPTURE_POLICY = (
STALE_CAPTURE_POLICY_THRESHOLD = 12 HOURS,
EXECUTION_COUNT = 10,
TOTAL_COMPILE_CPU_TIME_MS = 500,
TOTAL_EXECUTION_CPU_TIME_MS = 50
)
);
GO

# 4. 调整间隔长度
ALTER DATABASE fgedudb SET QUERY_STORE (
INTERVAL_LENGTH_MINUTES = 5
);
GO

# 5. 启用自动计划校正
ALTER DATABASE fgedudb SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
QUERY_CAPTURE_MODE = AUTO,
MAX_PLANS_PER_QUERY = 200,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
MAX_STORAGE_SIZE_MB = 10240
);
GO

# 检查查询存储状态
SELECT * FROM sys.database_query_store_options;
GO

学习交流加群风哥QQ113257174。

Part04-生产案例与实战讲解

4.1 SQLServer查询存储高级实战案例

# 案例1：使用查询存储分析性能问题

# 查看查询存储中的高频查询
SELECT
q.query_id,
qt.query_sql_text,
COUNT(*) AS execution_count,
SUM(rs.avg_duration * rs.count_executions) AS total_duration,
SUM(rs.avg_cpu_time * rs.count_executions) AS total_cpu_time
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_sql_text
ORDER BY total_duration DESC
TOP 10;
GO

# 查看查询的执行计划
SELECT
q.query_id,
p.plan_id,
p.is_forced_plan,
p.last_compile_start_time,
p.last_execution_time
FROM sys.query_store_query q
JOIN sys.query_store_plan p ON q.query_id = p.query_id
WHERE q.query_id = 123;
GO

# 强制使用特定计划
EXEC sp_query_store_force_plan @query_id = 123, @plan_id = 456;
GO

# 案例2：使用查询存储解决计划回归问题

# 查看计划回归
SELECT
q.query_id,
qt.query_sql_text,
p.plan_id,
rs.last_execution_time,
rs.avg_duration,
rs.avg_cpu_time
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE q.query_id = 123
ORDER BY rs.last_execution_time DESC;
GO

# 比较不同计划的性能
SELECT
p.plan_id,
rs.avg_duration,
rs.avg_cpu_time,
rs.avg_logical_io_reads,
rs.avg_physical_io_reads
FROM sys.query_store_plan p
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE p.query_id = 123
ORDER BY rs.avg_duration ASC;
GO

# 强制使用性能较好的计划
EXEC sp_query_store_force_plan @query_id = 123, @plan_id = 456;
GO

# 案例3：使用查询存储监控查询性能

# 创建查询性能监控脚本
CREATE PROCEDURE dbo.usp_monitor_query_performance
AS
BEGIN
SELECT
q.query_id,
qt.query_sql_text,
COUNT(*) AS execution_count,
SUM(rs.avg_duration * rs.count_executions) AS total_duration,
SUM(rs.avg_cpu_time * rs.count_executions) AS total_cpu_time,
SUM(rs.avg_logical_io_reads * rs.count_executions) AS total_logical_io,
SUM(rs.avg_physical_io_reads * rs.count_executions) AS total_physical_io
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time >= DATEADD(hour, -24, GETDATE())
GROUP BY q.query_id, qt.query_sql_text
ORDER BY total_duration DESC
TOP 20;
END;
GO

# 执行查询性能监控
EXEC dbo.usp_monitor_query_performance;
GO

# 创建查询存储自动化脚本
CREATE PROCEDURE dbo.usp_query_store_automation
AS
BEGIN
— 检查查询存储状态
SELECT * FROM sys.database_query_store_options;

— 清理过期数据
EXEC sp_query_store_flush_db;

— 识别性能问题查询
CREATE TABLE #problem_queries (
query_id INT,
query_sql_text NVARCHAR(MAX),
total_duration BIGINT,
total_cpu_time BIGINT,
execution_count INT
);

INSERT INTO #problem_queries
SELECT
q.query_id,
qt.query_sql_text,
SUM(rs.avg_duration * rs.count_executions) AS total_duration,
SUM(rs.avg_cpu_time * rs.count_executions) AS total_cpu_time,
COUNT(*) AS execution_count
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time >= DATEADD(hour, -24, GETDATE())
GROUP BY q.query_id, qt.query_sql_text
HAVING SUM(rs.avg_duration * rs.count_executions) > 1000000
ORDER BY total_duration DESC;

— 分析问题查询
DECLARE @query_id INT;
DECLARE @plan_id INT;

DECLARE query_cursor CURSOR FOR
SELECT query_id FROM #problem_queries;

OPEN query_cursor;
FETCH NEXT FROM query_cursor INTO @query_id;

WHILE @@FETCH_STATUS = 0
BEGIN
— 查找性能最好的计划
SELECT TOP 1 @plan_id = p.plan_id
FROM sys.query_store_plan p
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE p.query_id = @query_id
ORDER BY rs.avg_duration ASC;

— 强制使用性能最好的计划
IF @plan_id IS NOT NULL
BEGIN
EXEC sp_query_store_force_plan @query_id = @query_id, @plan_id = @plan_id;
END;

FETCH NEXT FROM query_cursor INTO @query_id;
END;

CLOSE query_cursor;
DEALLOCATE query_cursor;

— 清理临时表
DROP TABLE #problem_queries;
END;
GO

# 创建自动化作业
USE msdb;
GO

EXEC dbo.sp_add_job
@job_name = N’Query Store Automation’,
@enabled = 1,
@description = N’Automate query store tasks’;
GO

EXEC dbo.sp_add_jobstep
@job_name = N’Query Store Automation’,
@step_name = N’Run automation’,
@subsystem = N’TSQL’,
@command = N’USE fgedudb;
EXEC dbo.usp_query_store_automation;’,
@retry_attempts = 3,
@retry_interval = 5;
GO

EXEC dbo.sp_add_schedule
@schedule_name = N’Query Store Automation Schedule’,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 60;
GO

EXEC dbo.sp_attach_schedule
@job_name = N’Query Store Automation’,
@schedule_name = N’Query Store Automation Schedule’;
GO

EXEC dbo.sp_add_jobserver
@job_name = N’Query Store Automation’,
@server_name = N'(local)’;
GO

# 查看查询存储状态
SELECT * FROM sys.database_query_store_options;
GO

# 查看强制计划
SELECT
q.query_id,
qt.query_sql_text,
p.plan_id,
p.is_forced_plan
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
WHERE p.is_forced_plan = 1;
GO

风哥提示：定期检查查询存储状态，确保其正常运行并及时清理过期数据。from SQLServer视频:www.itpux.com。

Part05-风哥经验总结与分享

5.1 SQLServer查询存储最佳实践

1. 为所有生产数据库启用查询存储，以便及时发现性能问题

2. 合理配置查询存储参数，根据数据库大小和查询量进行调整

3. 定期分析查询存储数据，识别性能问题查询

4. 使用强制计划功能解决计划回归问题

5. 结合查询存储报告，可视化分析查询性能

6. 建立查询存储维护流程，定期清理过期数据

7. 利用查询存储自动化脚本，提高运维效率

5.2 SQLServer查询存储常见问题与解决方案

# 问题1：查询存储过大
# 解决方案：调整MAX_STORAGE_SIZE_MB参数，设置合理的清理策略

# 问题2：查询存储性能影响
# 解决方案：调整QUERY_CAPTURE_MODE参数，减少捕获的查询数量

# 问题3：查询存储数据过期
# 解决方案：设置合理的STALE_QUERY_THRESHOLD_DAYS参数

# 问题4：强制计划不生效
# 解决方案：检查查询是否符合强制计划的条件，确保查询文本没有变化

# 问题5：查询存储报告无法显示
# 解决方案：检查查询存储状态，确保其处于READ_WRITE模式

# 检查查询存储状态
SELECT * FROM sys.database_query_store_options;
GO

# 清理查询存储数据
EXEC sp_query_store_cleanup_db;
GO

# 重置查询存储
ALTER DATABASE fgedudb SET QUERY_STORE = OFF;
GO

ALTER DATABASE fgedudb SET QUERY_STORE = ON (
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
MAX_STORAGE_SIZE_MB = 10240
);
GO

更多学习教程公众号风哥教程itpux_com。