SQL Server 2016 查询存储（Query Store）查询语句

小雪崩

　　SQL Server 2016 查询存储（Query Store）查询语句
　　问题
　　SQL Server 2016的新特性之一是查询存储。在之前的查询存储相关文章中我们回顾了如何使用SQL Server Management Studio配置和访问查询存储。我们也提供了查询存储使用的示例。使用查询存储的一些实际应用程序是什么？什么查询可以针对查询存储运行？我可以回答哪些问题？
　　解决方案
　　对一下查询语句，我们将使用查询存储目录视图和存储过程。我们将在微软MSDN网站提供其他可用查询的参考。
　　有用的SQL Server 2016查询存储查询语句
　　找出查询存储的大小
　　使用这个查询你可以找出查询存储使用的大小和最大值：
SELECT current_storage_size_mb, max_storage_size_mb FROM sys.database_query_store_options　　在查询存储中找出一个查询的ID
　　基于部分查询的文本或者部分对象（视图、存储过程等）名称找到一个查询的ID：
SELECT q.query_id, t.query_sql_text, object_name(q.object_id) AS parent_object　　
FROM sys.query_store_query_text t JOIN sys.query_store_query q
　　
ON t.query_text_id = q.query_text_id
　　
WHERE t.query_sql_text LIKE N'%insert %db_store%'
　　
OR object_name(q.object_id) = 'proc_1'
　　找到一个查询相关的执行计划
　　基于查询的ID（如果知道），找到执行计划的ID，部分查询文本或父对象（视图、存储过程等）名称：
SELECT t.query_sql_text, q.query_id, p.plan_id, object_name(q.object_id) AS parent_object　　
FROM sys.query_store_query_text t JOIN sys.query_store_query q
　　
ON t.query_text_id = q.query_text_id
　　
JOIN sys.query_store_plan p ON q.query_id = p.query_id
　　
WHERE q.query_id = 1
　　
-- OR t.query_sql_text LIKE N'%SELECT c1, c2 FROM dbo.db_store%'
　　
-- OR object_name(q.object_id) = 'proc_1'
　　在SQL Server查询存储中找到带有多个执行计划的排名前十查询
SELECT TOP 10 t.query_sql_text, q.query_id,　　
object_name(q.object_id) AS parent_object,
　　
COUNT(DISTINCT p.plan_id) AS num_of_plans
　　
FROM sys.query_store_query_text t JOIN sys.query_store_query q
　　
ON t.query_text_id = q.query_text_id
　　
JOIN sys.query_store_plan p ON q.query_id = p.query_id
　　
GROUP BY t.query_sql_text, q.query_id, object_name(q.object_id)
　　
HAVING COUNT(DISTINCT p.plan_id) > 1
　　
ORDER BY COUNT(DISTINCT p.plan_id) DESC
　　在查询存储中找出最常执行的SQL Server查询语句排名前十名
SELECT TOP 10 t.query_sql_text, q.query_id,　　
object_name(q.object_id) AS parent_object,
　　
SUM(s.count_executions) total_executions
　　
FROM sys.query_store_query_text t JOIN sys.query_store_query q
　　
ON t.query_text_id = q.query_text_id
　　
JOIN sys.query_store_plan p ON q.query_id = p.query_id
　　
JOIN sys.query_store_runtime_stats s ON p.plan_id = s.plan_id
　　
WHERE s.count_executions > 1 -- used to make the query faster
　　
GROUP BY t.query_sql_text, q.query_id, object_name(q.object_id)
　　
ORDER BY SUM(s.count_executions) DESC
　　在查询存储中找出影响最大数量行数的SQL Server查询排名前十名
　　这可能对检查是否有返回大量行的查询有用，并且后面可能要研究是否过滤条件可以被添加到查询。
　　我们有一个旧的应用程序初始化使用了一个小表。开发使用一个查询从该表返回所有的记录。当数据库和表增长数年后，这的确影响了应用的性能。
SELECT top 10 t.query_sql_text, q.query_id,　　
object_name(q.object_id) AS parent_object,
　　
s.plan_id, s.avg_rowcount
　　
FROM sys.query_store_query_text t JOIN sys.query_store_query q
　　
ON t.query_text_id = q.query_text_id
　　
JOIN sys.query_store_plan p ON q.query_id = p.query_id
　　
JOIN sys.query_store_runtime_stats s ON p.plan_id = s.plan_id
　　
WHERE s.avg_rowcount > 100
　　
ORDER BY s.avg_rowcount DESC
　　我们需要过滤查询排除对大表的索引操作。
　　在查询存储中找出每次执行的编译的最大百分比的排名前十名SQL Server查询
　　有时查询性能可能会被过度的重编译锁影响。使用它找出大量编译的前十名查询：
WITH Query_Stats　　
AS
　　
(
　　
SELECT plan_id,
　　
SUM(count_executions) AS total_executions
　　
FROM sys.query_store_runtime_stats
　　
GROUP BY plan_id
　　
)
　　
SELECT TOP 10 t.query_sql_text, q.query_id, p.plan_id,
　　
s.total_executions/p.count_compiles avg_compiles_per_plan
　　
FROM sys.query_store_query_text t JOIN sys.query_store_query q
　　
ON t.query_text_id = q.query_text_id
　　
JOIN sys.query_store_plan p ON q.query_id = p.query_id
　　
JOIN Query_Stats s ON p.plan_id = s.plan_id
　　
ORDER BY s.total_executions/p.count_compiles DESC
　　在这里阅读更多关于重编译执行计划。
　　清理SQL Server查询存储数据
　　你可以使用sp_query_store_remove_plan存储过程从查询存储移除一个特定执行计划（对于执行计划的运行时统计也将会被清空）：
EXEC sp_query_store_remove_plan @plan_id = 1　　
GO
　　使用sp_query_store_reset_exec_stats存储过程，你可以对特定的执行计划删除运行时统计，但是保持执行计划在查询存储中：
EXEC sp_query_store_reset_exec_stats @plan_id = 1　　
GO
　　你也可以从查询存储使用sp_query_store_query存储过程移除整个查询。这也将会从查询存储移除相关的执行计划和统计信息：
EXEC sp_query_store_remove_query @query_id = 1　　其他有用查询
　　一些其他有用查询可以在微软MSDN网站这里找到：
　　l 数据库上最近执行的n条查询
　　l 每个查询的执行次数
　　l 最近1小时最长平均执行时间的查询的数量
　　l 最近24小时最大平均物理IO读的查询的数量，以及平均行数和执行数量
　　l 当前查询性能回归的查询（比较不同的时间点）
　　l 当前查询性能回归的查询（比较当前和历史执行）
　　l 删除即席查询
　　对于你的查询的其他列
　　对于你的查询，你可以找到以下有用列：
　　l is_parallel_plan列在sys.query_store_plan目录视图
　　l avg_dop列在sys.query_store_runtime_stats目录视图
　　l query_parameterization_type列在sys.query_store_query目录视图
　　l is_internal_query列在sys.query_store_query目录视图
　　接下来
　　l 使用查询存储目录视图写你自己的查询。
　　l 下载最新的SQL Server 2016评估版本。
　　l 阅读SQL Server 2016联机帮助文档。
　　l 阅读如何使用查询存储监控性能。
　　l 阅读其他SQL Server 2016的文章。