SQL Server 2016 查询存储（Query Store）示例

英文原文：

<a href="https://www.mssqltips.com/sqlservertip/4046/sql-server-2016-query-store-example/" target="_blank">https://www.mssqltips.com/sqlservertip/4046/sql-server-2016-query-store-example/</a>

问题

SQL Server 2016的新特性之一是查询存储。在之前的文章中，我们回顾了如何在SQL Server Management Studio（SSMS）中配置和访问查询存储。我们也提供了SSMS中一个查询存储报表的详细回顾。本文我们将显示一个如何使用查询存储的示例，如何跟踪多个执行计划和如何强制一个有效执行计划。

解决方案

我们将对演示创建一个带有一个表和存储过程的数据库。这个表将会足够的数据用以显示执行计划的不同。

创建演示查询存储数据库

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

<code>CREATE</code> <code>DATABASE</code> <code>[qstore_demo]</code>

<code>ON</code> <code>PRIMARY</code>

<code>( </code><code>NAME</code> <code>= N</code><code>'qs_demo'</code><code>, FILENAME = N</code><code>'C:\DATA\qs_demo.mdf'</code> <code>, </code><code>SIZE</code> <code>= 102400KB ,</code>

<code>MAXSIZE = 1024000KB , FILEGROWTH = 20480KB )</code>

<code>LOG </code><code>ON</code>

<code>( </code><code>NAME</code> <code>= N</code><code>'qs_demo_log'</code><code>, FILENAME = N</code><code>'D:\DATA\qs_demo_log.ldf'</code> <code>, </code><code>SIZE</code> <code>= 20480KB ,</code>

<code>GO</code>

<code>ALTER</code> <code>DATABASE</code> <code>[qstore_demo] </code><code>SET</code> <code>AUTO_UPDATE_STATISTICS </code><code>OFF</code>

<code>ALTER</code> <code>DATABASE</code> <code>[qstore_demo] </code><code>SET</code> <code>AUTO_CREATE_STATISTICS </code><code>OFF</code>

<code>ALTER</code> <code>DATABASE</code> <code>[qstore_demo] </code><code>SET</code> <code>RECOVERY SIMPLE</code>

<code>ALTER</code> <code>DATABASE</code> <code>[qstore_demo] </code><code>SET</code> <code>QUERY_STORE = </code><code>OFF</code>

我们创建一个AUTO_UPDATE_STATISTICS为OFF和AUTO_CREATE_STATISTICS为OFF的数据库尽可能少的优化。这只是出于演示目的。

我们现在还没有启用查询存储。该表会生成数据并且我们不想要昂贵的INSERT查询在查询存储中。

现在我们将创建表和存储过程。我们将生成表并且在数据库上启用查询存储：

17

18

19

20

21

22

23

24

25

<code>USE qstore_demo</code>

<code>-- create a table</code>

<code>CREATE</code> <code>TABLE</code> <code>dbo.db_store (c1 </code><code>CHAR</code><code>(3) </code><code>NOT</code> <code>NULL</code><code>, c2 </code><code>CHAR</code><code>(3) </code><code>NOT</code> <code>NULL</code><code>, c3 </code><code>SMALLINT</code> <code>NULL</code><code>)</code>

<code>-- create a stored procedure</code>

<code>CREATE</code> <code>PROC dbo.proc_1 @par1 </code><code>SMALLINT</code>

<code>AS</code>

<code>SET</code> <code>NOCOUNT </code><code>ON</code>

<code>SELECT</code> <code>c1, c2 </code><code>FROM</code> <code>dbo.db_store</code>

<code>WHERE</code> <code>c3 = @par1</code>

<code>-- populate the table (this may take a couple of minutes)</code>

<code>INSERT</code> <code>INTO</code> <code>[dbo].db_store (c1,c2,c3) </code><code>SELECT</code> <code>'18'</code><code>,</code><code>'2f'</code><code>,2</code>

<code>go 20000</code>

<code>INSERT</code> <code>INTO</code> <code>[dbo].db_store (c1,c2) </code><code>SELECT</code> <code>'171'</code><code>,</code><code>'1ff'</code>

<code>go 4000</code>

<code>INSERT</code> <code>INTO</code> <code>[dbo].db_store (c1,c2,c3) </code><code>SELECT</code> <code>'172'</code><code>,</code><code>'1ff'</code><code>,0</code>

<code>go 10</code>

<code>INSERT</code> <code>INTO</code> <code>[dbo].db_store (c1,c2,c3) </code><code>SELECT</code> <code>'172'</code><code>,</code><code>'1ff'</code><code>,4</code>

<code>go 15000</code>

<code>-- enable Query Store on the database</code>

<code>ALTER</code> <code>DATABASE</code> <code>[qstore_demo] </code><code>SET</code> <code>QUERY_STORE = </code><code>ON</code>

SQL Server 2016查询存储性能测试

测试1 – 表上没有索引

在我们的第一个测试中我们在没有任何索引的表上执行存储过程。然后我们会在SSMS查询存储报表中回顾结果。我们会运行存储过程20次只会为了生成足够的执行和I/O，以便更容易在查询存储的其他查询中找到：

<code>EXEC</code> <code>dbo.proc_1 0</code>

<code>GO 20</code>

在SSMS中“Query Store”数据库的容器下打开“Top Resource Consuming Queries”面板：

<a href="http://s3.51cto.com/wyfs02/M00/79/F3/wKiom1afFWajNegSAAASfusM5XI751.gif" target="_blank"></a>

修改纵坐标使用“exec count”并修改“Metric”下拉列表的“Duration”为“Logical Reads”：

<a href="http://s3.51cto.com/wyfs02/M01/79/F3/wKiom1afFWewT-OHAAB7oyDmcKo149.gif" target="_blank"></a>

这里是我们的查询（查询#1）带有一个单一表扫描计划（计划#1）：

<a href="http://s3.51cto.com/wyfs02/M00/79/F2/wKioL1afFaTh_hJBAABKxN6rpU0800.gif" target="_blank"></a>

测试2 – 使用一个非聚集索引测试

在这个测试中我们将创建一个非聚集索引并再次执行存储过程：

<code>CREATE</code> <code>NONCLUSTERED </code><code>INDEX</code> <code>NCI_1</code>

<code>ON</code> <code>dbo.db_store (c3)</code>

注意，我们会创建一些索引（重复索引）只用于演示目的为了生成多个执行计划。

在SSMS中回到查询存储面板并刷新结果：

<a href="http://s3.51cto.com/wyfs02/M02/79/F3/wKiom1afFWqCnbYdAABOqjw3UL8219.gif" target="_blank"></a>

注意，新的执行计划（计划#13）被创建和使用。这个计划现在有更少的逻辑读。

测试3 – 创建另一个非聚集索引

现在我们将在新增列上创建新的索引：

<code>CREATE</code> <code>NONCLUSTERED </code><code>INDEX</code> <code>NCI_2</code>

<code>ON</code> <code>dbo.db_store (c3, c1)</code>

在SSMS中回顾结果并且注意到另一个执行计划（计划#20）被创建：

<a href="http://s3.51cto.com/wyfs02/M00/79/F2/wKioL1afFaaB-HH1AABPabkW9x8423.gif" target="_blank"></a>

强制执行计划（Force Plan）

让我们运行一些表更新并再次执行存储过程：

<code>UPDATE</code> <code>dbo.db_store </code><code>SET</code> <code>c1 =</code><code>'1'</code> <code>WHERE</code> <code>c3 = </code><code>'0'</code>

<code>UPDATE</code> <code>dbo.db_store </code><code>SET</code> <code>c2 =</code><code>'3ff'</code> <code>WHERE</code> <code>c3 = </code><code>'1'</code>

<code>DELETE</code> <code>FROM</code> <code>dbo.db_store </code><code>WHERE</code> <code>c3 = 3</code>

<code>INSERT</code> <code>INTO</code> <code>dbo.db_store (c1,c2,c3) </code><code>SELECT</code> <code>'173'</code><code>,</code><code>'1fa'</code><code>,0</code>

<code>GO 5</code>

在SSMS中返回查询存储面板并点击“View plan summary in a grid format”按钮：

<a href="http://s3.51cto.com/wyfs02/M00/79/F3/wKiom1afFWywu-AhAAAi5MALwWU836.gif" target="_blank"></a>

你现在可以看到带有详细统计信息的三个执行计划：

<a href="http://s3.51cto.com/wyfs02/M01/79/F3/wKiom1afFWzxQgm9AAAQb5_4bsQ368.gif" target="_blank"></a>

注意，最新的计划#20有比旧计划#13更多逻辑读。

在查询存储报表的“Plan Summary”或“Execution Plan”部分的下面，点击“Force Plan”按钮。你也可以右击计划#13并强制这个计划。确认对这个查询你想强制这个计划：

<a href="http://s3.51cto.com/wyfs02/M02/79/F3/wKiom1afFW6ipagnAACPs_p2mbo623.gif" target="_blank"></a>

<code>EXEC</code> <code>sp_query_store_force_plan @query_id = 1, @plan_id = 13;</code>

再次运行存储过程。然后在SSMS中返回查询存储。更新计划总结并注意到“plan forced”列值对于“plan id”#13现在被设为1。该计划的“exec count”也增加了，意味着强制计划#13这次被使用了而不是计划#20：

这里是以图表格式（打勾的泡）显示了强制计划的样子：

<a href="http://s3.51cto.com/wyfs02/M00/79/F2/wKioL1afFarzI_NJAAAoMownFfc850.gif" target="_blank"></a>

对于存储过程创建最后的，最优索引。

<code>CREATE</code> <code>NONCLUSTERED </code><code>INDEX</code> <code>NCI_3</code>

<code>INCLUDE (c1,c2)</code>

如果你再次运行存储过程，你会注意到强制计划将会被再次使用而不是创建新的使用新索引的计划。

现在我们不强制计划#13并再次运行存储过程：

<code>EXEC</code> <code>sp_query_store_unforce_plan @query_id = 1, @plan_id = 13</code>

这里是结果：

<a href="http://s3.51cto.com/wyfs02/M00/79/F2/wKioL1afFauBa--IAAAbWPpRt2Q892.gif" target="_blank"></a>

新的计划#23已经被创建和使用。它有最少的逻辑读。

注意：本文基于SQL Server 2016 CTP 2.2版本所写。一些截屏和行为在SQL Server 2016的最终版中可能会改变。

在接下来的文章中我们会提供大量针对查询存储运行的有用查询。

接下来

l 阅读关于查询存储的下一篇文章。

本文转自UltraSQL51CTO博客，原文链接： http://blog.51cto.com/ultrasql/1736757，如需转载请自行联系原作者