适用于SQL Server的OpenQueryStore –安装和配置

The OpenQueryStore is an Open source implementation of the popular Query store functionality introduced in SQL Server 2016 CTP2. The OpenQueryStore was first introduced in June 2017. Its main contributors are William Durkin and Enrico van de Laar

OpenQueryStore是SQL Server 2016 CTP2中引入的流行查询存储功能的开源实现。 OpenQueryStore于2017年6月首次引入。其主要贡献者是William Durkin和Enrico van de Laar

The OpenQueryStore collects query runtime execution statistics, wait statistics information against a specific database. This information helps identifying performance problems and troubleshooting by quickly finding performance differences.

OpenQueryStore收集查询运行时执行统计信息，并针对特定数据库等待统计信息。 此信息可通过快速发现性能差异来帮助确定性能问题并进行故障排除。

The main difference between the OpenQueryStore and the Query store feature is that the OpenQueryStore supports SQL Server 2005 to SQL Server 2014 while Query Store supports only SQL Server 2016 and higher. Also, the OpenQueryStore is open-source which means that the OpenQueryStore code can be changed whenever you like.

OpenQueryStore和查询存储功能之间的主要区别在于，OpenQueryStore支持SQL Server 2005至SQL Server 2014，而查询存储仅支持SQL Server 2016和更高版本。 另外，OpenQueryStore是开源的，这意味着可以随时更改OpenQueryStore代码。

More about the Query Store can be found on the SQL Server Query Store – Overview page.

有关查询存储的更多信息，请参见“ SQL Server查询存储–概述”页面。

In this article the installation process of the OpenQueryStore, setting of the OpenQueryStore Dashboards and some of the OpenQueryStored procedures/options will be explained.

在本文中，将说明OpenQueryStore的安装过程，OpenQueryStore仪表板的设置以及一些OpenQueryStored过程/选项。

安装 (Installation)

There are two ways to install the OpenQueryStore. One way is by using automated setup and the other is the manual installation by modifying and executing a couple of SQL scripts.

有两种安装OpenQueryStore的方法。 一种方法是使用自动设置，另一种方法是通过修改和执行几个SQL脚本进行手动安装。

Before installation of the OpenQueryStore, data collection mode needs to be chosen first.

在安装OpenQueryStore之前，首先需要选择数据收集模式。

There are two data collection modes one is Classic and the other is Centralized.

有两种数据收集模式，一种是“ 经典” ，另一种是“ 集中”。

Classic mode

经典模式

When the Classic mode is used, the OpenQueryStore will be installed on database level similar as the Query Store. This means that query runtime execution statistics will be collected for the queries that run on the database on which the OpenQueryStore is installed. All collected data will be stored inside that database under the oqs objects.

使用经典模式时，OpenQueryStore将以与查询存储类似的数据库级别安装。 这意味着将为在安装了OpenQueryStore的数据库上运行的查询收集查询运行时执行统计信息。 所有收集的数据将存储在该数据库内的oqs对象下。

Centralized mode

集中式

When the Centralized mode is used, the OpenQueryStore collects query runtime execution statistics for multiple databases inside one instance and stores collected data into a separate central database.

使用集中式模式时，OpenQueryStore收集一个实例中多个数据库的查询运行时执行统计信息，并将收集的数据存储到单独的中央数据库中。

Also, before installing the OpenQueryStore, one of the two scheduling methods needs to be chosen, Service Broker or SQL Server Agent.

另外，在安装OpenQueryStore之前，需要选择两种调度方法之一，即Service Broker或SQL Server Agent 。

These two methods are used to control the scheduling of data collection and background maintenance.

这两种方法用于控制数据收集和后台维护的计划。

If Service Broker scheduling methods are chosen for scheduling of data collection and background maintenance then before installation process of the OpenQueryStore begin, first, Service Broker needs to be enabled for the database on which want to install the OpenQueryStore.

如果选择Service Broker调度方法来调度数据收集和后台维护，则在开始OpenQueryStore的安装过程之前，首先，需要为要在其上安装OpenQueryStore的数据库启用Service Broker。

To do that, execute the following code in a query editor:

为此，请在查询编辑器中执行以下代码：

ALTER DATABASE Test
SET ENABLE_BROKER
WITH ROLLBACK IMMEDIATE;
GO

To see whether Service Broker is enabled or not, execute the following code:

要查看是否启用了Service Broker，请执行以下代码：

SELECT is_broker_enabled
FROM sys.databases
WHERE name = 'Test'
GO 

In order to use SQL Server Agent to control the scheduling of data collection and background maintenance, make sure that SQL Server Agent is enabled first. Also, bear in mind that SQL Server Agent is not available in SQL Server Express Edition.

为了使用SQL Server代理来控制数据收集和后台维护的计划，请确保首先启用了SQL Server代理。 另外，请记住，SQL Server Express Edition中不提供SQL Server代理。

When the mode is picked and the method is set, we can install the OpenQueryStore.

选择模式并设置方法后，我们可以安装OpenQueryStore。

From the OpenQueryStore release page download and unzip the latest OpenQueryStore version:

从OpenQueryStore发行页面下载并解压缩最新的OpenQueryStore版本：

Automated installation

自动安装

Open the Window PowerShell dialog and navigate to the unzipped OpenQueryStore folder and paste the following code:

打开“ Windows PowerShell”对话框，导航到解压缩的OpenQueryStore文件夹，然后粘贴以下代码：

.\Install.ps1 -SqlInstance <Instance> -Database <dbName> -OQSMode Classic -SchedulerType “Service Broker” -CertificateBackupPath “<path>”

。\ Install.ps1 -SqlInstance <实例>-数据库<dbName> -OQSMode经典-SchedulerType“服务代理” -CertificateBackupPath“ <路径>”

Replace the values <Instance>, <dbName> and <path> with appropriate one:

将值<Instance>，<dbName>和<path>替换为适当的值：

and press the Enter key.

然后按Enter键。

Manual installation

手动安装

Go to the unzipped OpenQueryStore folder and, under the setup folder, locate the install_open_query_store_base.sql scrpt:

转到解压缩的OpenQueryStore文件夹，然后在setup文件夹下找到install_open_query_store_base.sql scrpt：

In SQL Server Management Studio (SSMS), connect to the SQL Server instance and database in which you want to install the OpenQueryStore. After that, open the install_open_query_store_base.sql script and replace the values within braces {} with appropriate ones.

在SQL Server Management Studio（SSMS）中，连接到要在其中安装OpenQueryStoreSQL Server实例和数据库。 之后，打开install_open_query_store_base.sql脚本，并将括号{}中的值替换为适当的值。

For example, {DatabaseWhereOQSIsRunning} replace with the name of the database, in this case [AdventureWorks2014] database.

例如，将{DatabaseWhereOQSIsRunning}替换为数据库的名称，在本例中为[ AdventureWorks2014 ]数据库。

Replace the {OQSMode} placeholder with the Classic or Centralized mode and execute the script:

将{OQSMode}占位符替换为经典或集中模式，然后执行脚本：

After executing the install_open_query_store_base.sql script, in the OpenQueryStore folder, find and, in SSMS, open the install_gather_statistics.sql script:

执行install_open_query_store_base.sql脚本后，在OpenQueryStore文件夹中找到并在SSMS中打开install_gather_statistics.sql脚本：

Replace placeholders within braces {} with required values, the same as it was done with the install_open_query_store_base.sql script and press the Execute button:

用必需的值替换括号{}中的占位符，与使用install_open_query_store_base.sql脚本执行的操作相同，然后按Execute按钮：

If Service Broker is chosen to be used as a scheduler method then the install_service_broker.sql and install_service_broker_certificate.sql scripts need to be modified and executed as we did with install_open_query_store_base.sql and install_gather_statistics.sql scripts.

如果选择服务代理被用作调度方法则install_service_broker.sql和install_service_broker_certificate.sql脚本需要进行修改，当我们用install_open_query_store_base.sql和install_gather_statistics.sql脚本没有执行。

More information about the installation process of the OpenQueryStore can be found on the Installing OQS page

有关OpenQueryStore的安装过程的更多信息，请参见“ 安装OQS”页面。

After installing the OpenQueryStore to start the data collection process, execute the stored procedure [master].[dbo].[open_query_store_startup]:

在安装OpenQueryStore以开始数据收集过程之后，执行存储过程[master]。[dbo]。[open_query_store_startup]：

EXECUTE [master].[dbo].[open_query_store_startup]

This will activate the OpenQueryStored data collection by using Service Broker. The [open_query_store_startup] procedure is called at every SQL Server startup.

这将通过使用Service Broker激活OpenQueryStored数据收集。 在每次SQL Server启动时都会调用[open_query_store_startup]过程。

Even the installation goes well and the data collection is activated, the OpenQueryStore may not start collecting data.

即使安装进行顺利并且激活了数据收集，OpenQueryStore可能也不会开始收集数据。

To check why the OpenQueryStore didn’t start data collection execute the following code:

要检查为什么OpenQueryStore没有启动数据收集，请执行以下代码：

SELECT * FROM oqs.activity_log

In the Results grid under the log_message column and view the generated message:

在“结果”网格中的log_message列下，查看生成的消息：

To resolve the above mentioned problem, in a query editor, execute the following code:

要解决上述问题，请在查询编辑器中执行以下代码：

SELECT collection_active FROM oqs.collection_metadata

If the result in the Results grid for the collection_active colum is false or zero (0):

如果collection_active列的结果网格中的结果为false或零（0）：

Then this column in the oqs.collection_metadata table needs to be changed (updated) in order the OpenQueryStore starts collecting data:

然后，需要更改（更新） oqs.collection_metadata表中的此列，以便OpenQueryStore开始收集数据：

UPDATE oqs.collection_metadata SET collection_active=1

To verify that the OpenQueryStore start collecting data checkthat collected under the log_message column of the oqs.activity_log table:

要验证OpenQueryStore是否开始收集数据，请检查在oqs.activity_log表的log_message列下收集的数据：

SELECT * FROM oqs.activity_log

All data that the OpenQueryStore collects, as well as settings, are placed under the following tables:

OpenQueryStore收集的所有数据以及设置都放在下表中：

• oqs.activity_log
  oqs.activity_log
• oqs.collection_metadata
  oqs.collection_metadata
• oqs.excluded_queries
  oqs.excluded_queries
• oqs.intervals
  oqs.intervals
• oqs.monitored_databases
  oqs.monitored_databases
• oqs.plan_dbid
  oqs.plan_dbid
• oqs.plans
  计划
• oqs.queries
  oqs.queries
• oqs.query_runtime_stats
  oqs.query_runtime_stats
• oqs.wait_stats
  oqs.wait_stats
• oqs.wait_type_filter
  oqs.wait_type_filter

Note, this can be changed over time since the OpenQueryStore is in active development.

请注意，由于OpenQueryStore正在开发中，因此可以随时间更改。

Configuration information for the OpenQueryStore are placed under the oqs.collection_metadata table:

OpenQueryStore的配置信息位于oqs.collection_metadata表下：

The command column contains the gather_statistics stored procedure call. The OpenQueryStore call this procedure in regular intervals to get runtime statistics and store them in the OpenQueryStore.

命令列包含collect_statistics存储过程调用。 OpenQueryStore定期调用此过程以获取运行时统计信息并将其存储在OpenQueryStore中。

The collection_interval column contains the collection interval which is used to control interval of collection data. By default, the collection_interval column is set to 60 seconds.

collection_interval列包含用于控制收集数据间隔的收集间隔。 默认情况下，collection_interval列设置为60秒。

The oqs_mode column is used to inform the OpenQueryStore which mode is used (Classic or Centralized)

oqs_mode列用于通知OpenQueryStore使用哪种模式（经典或集中式）

The oqs_classic_db column contain the name of the database on which the OpenQueryStore is installed and directs classic mode to only run in that database.

oqs_classic_db列包含安装OpenQueryStore的数据库的名称，并指示经典模式仅在该数据库中运行。

The collection_active column indicates whether data collection in the OpenQueryStore is allowed or not. During the installation process this column is set to 0 and needs to be changed to 1 in order the OpenQueryStore start data collection.

collection_active列指示是否允许OpenQueryStore中的数据收集。 在安装过程中，此列设置为0，并且需要将其更改为1，以便OpenQueryStore开始数据收集。

The execution_threshold column contains the number which presents the minimum executions of a query plan before the OpenQueryStore starts capturing. By default, the execution_threshold is 2 to skip single-use plans.

execution_threshold列包含一个数字，该数字表示OpenQueryStore开始捕获之前查询计划的最小执行量。 默认情况下，execution_threshold为2以跳过一次性计划。

The data_cleanup_active column indicates if the automation cleanup process of old data in the OpenQueryStore is activated or not. By default, this is set to 1 (activate)

data_cleanup_active列指示是否激活了OpenQueryStore中的旧数据的自动化清理过程。 默认情况下，它设置为1（激活）

The data_cleanup_threshold column shows how many days the OpenQueryStore should keep data; the automated cleanup process removes data older than this. By default, this is set to 30 days.

data_cleanup_threshold列显示OpenQueryStore应该保留数据的天数； 自动清除过程会删除早于此时间的数据。 默认情况下，此设置为30天。

The data_cleanup_throttle column determines how many rows can be deleted in one pass. This avoids large deletions from trashing the transaction log and blocking the OpenQueryStore tables. By default, this is set to 5000 rows.

data_cleanup_throttle列确定一次可以删除多少行。 这样可以避免大量删除操作浪费事务日志并阻止OpenQueryStore表。 默认情况下，此设置为5000行。

OpenQueryStore仪表板 (OpenQueryStore Dashboards)

The OpenQueryStore Dashboards visualize information that the OpenQueryStore has collected by showing them in vireos chart formats.

OpenQueryStore仪表板通过以vireos图表格式显示它们来可视化OpenQueryStore收集的信息。

To install dashboards, go to the OpenQueryStore Dashboards GitHub page and, from there, download the OpenQueryStoreDashboard.rdl and OpenQueryStoreWaitStatsDashboard.rdl files:

要安装仪表板，请转到OpenQueryStore Dashboards GitHub页面，然后从此处下载OpenQueryStoreDashboard.rdl和OpenQueryStoreWaitStatsDashboard.rdl文件：

Or if you have already downloaded the latest installation OpenQueryStore folder, these two dashboards are located under the Dashboards folder:

或者，如果您已经下载了最新安装的OpenQueryStore文件夹，则这两个仪表板位于Dashboards文件夹下：

After downloading the dashboards, open SSMS 2016 or SSMS v17, navigate to the database on which the OpenQueryStore is installed, right click and, from the context menu, under the Reports sub-menu, choose the Custom Reports command:

下载仪表盘后，打开SSMS 2016或SSMS v17，导航到安装了OpenQueryStore的数据库，右键单击，然后从上下文菜单的Reports子菜单下，选择Custom Reports命令：

Navigate to the OpenQueryStoreDashboard.rdl and OpenQueryStoreWaitStatsDashboard.rdl files and press the Open button:

导航到OpenQueryStoreDashboard.rdl和OpenQueryStoreWaitStatsDashboard.rdl文件，然后按“打开”按钮：

The Run Custom Report warning message box will appear. Press the Run button to continue:

将显示“运行自定义报告”警告消息框。 按运行按钮继续：

This will open the OpenQueryStore Dashboards.

这将打开OpenQueryStore仪表板。

Note if these steps you do in SSMS 2012 or SSMS 2014, the following message may appear:

请注意，如果您在SSMS 2012或SSMS 2014中执行这些步骤，则可能会出现以下消息：

An error occurred during local report processing. (Microsoft.ReportViewer.WinForms)
ADDITIONAL INFORMATION:
The definition of the report ” is invalid. (Microsoft.ReportViewer.Common)
The definition of this report is not valid or supported by this version of Reporting Services. The report definition may have been created with a later version of Reporting Services, or contain content that is not well-formed or not valid based on Reporting Services schemas. Details: The report definition has an invalid target namespace ‘http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition’ which cannot be upgraded. (Microsoft.ReportViewer.Common)

本地报告处理期间发生错误。 （Microsoft.ReportViewer.WinForms）
附加信息：
报告“”的定义无效。 （Microsoft.ReportViewer.Common）
此版本的Reporting Services无效或不支持此报告的定义。 报表定义可能是使用更高版本的Reporting Services创建的，或者包含基于Reporting Services架构格式不正确或无效的内容。 详细信息：报表定义具有无效的目标名称空间'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition'，该名称空间无法升级。 （Microsoft.ReportViewer.Common）

At the beginning, these two dashboards will not show any information, since it needs some time for the OpenQueryStore to collect data and show them:

最初，这两个仪表板将不显示任何信息，因为OpenQueryStore需要一些时间来收集数据并显示它们：

After a while, the dashboards will be filed with charts and data:

一段时间后，仪表板将与图表和数据一起归档：

Hourly overview section shows information that are collected by the OpenQueryStore in the last hour.

每小时概述部分显示了OpenQueryStore在过去一个小时中收集的信息。

The first chart on the OpenQueryStoreDashboard shows the top 10 queries that have been executed in the last hour based on the average elapsed time of the query:

OpenQueryStoreDashboard上的第一张图表显示了基于查询的平均经过时间在最近一小时内执行的前10个查询：

The next chart shows the top 10 queries in the last hour based on the amount of executions:

下图显示了基于执行量的最近一小时中排名前10位的查询：

The next chart returns the number of unique query executed in the last hour. This chart does not return a total number of executions but the number of unique, distinct, queries that have been executed:

下一个图表返回在过去一小时内执行的唯一查询的数量。 此图表不返回执行总数，而是返回已执行的唯一，不同查询的数量：

The Execution plan diversity chart shows the count of different types of execution plans in the last hour. When mouse-over the blocks, a popup will appear and show the plan type (Prepared or Adhoc):

执行计划多样性图表显示了最近一小时不同类型的执行计划的数量。 将鼠标悬停在块上时，将出现一个弹出窗口，并显示计划类型（“准备”或“临时”）：

The Resource usage overview section shows performance metrics of all queries aggregated over the collection intervals.

资源使用概述部分显示了在收集间隔内聚合的所有查询的性能指标。

The area chart shows the minimum and maximum values while the line displays the average value. All graphs are based on the performance metrics that are collected in the last eight hours.

面积图显示最小值和最大值，而线显示平均值。 所有图表均基于最近八个小时内收集的性能指标。

The bottom right is the Queries with multiple plans chart, which shows the query IDs of query statements that have generated more than one Execution Plan:

右下角是“具有多个计划的查询”图表，该图表显示了已生成多个执行计划的查询语句的查询ID：

In the OpenQueryStore info section, it shows information about the OpenQueryStore itself. The first chart shows how many space the OpenQueryStore tables takes in KB inside the database. The chart in the middle returns the number of items in the OpenQueryStore. The table shows the information what the OpenQueryStore did in the last iteration of collecting data:

在OpenQueryStore信息部分中，它显示有关OpenQueryStore本身的信息。 第一张图表显示了OpenQueryStore表在数据库内部以KB为单位占用多少空间。 中间的图表返回OpenQueryStore中的项目数。 下表显示了OpenQueryStore在上一次收集数据迭代中的操作信息：

The Wait Stats Dashboard shows the waits status of the last hours:

等待统计信息显示板显示了最近几个小时的等待状态：

All available wait types are placed in the wait_type_filter table:

所有可用的等待类型都放置在wait_type_filter表中：

SELECT * FROM oqs.wait_type_filter

Also, these dashboards can be printed or exported in Excel, PDF, Word file:

此外，这些仪表板可以打印或导出为Excel，PDF，Word文件：

Removing the OpenQueryStore

删除OpenQueryStore

To remove the OpenQueryStore from the database, in SSMS, open the uninstall_open_query_store.sql file, enter the name of the database for which what to remove the OpenQueryStore and execute the script:

要从数据库中删除OpenQueryStore，请在SSMS中打开uninstall_open_query_store.sql文件，输入要删除OpenQueryStore的数据库名称并执行脚本：

The OpenQueryStore is in active development and, for updates or any issues, you can check the twitter account or the OpenQueryStore GitHub page.

OpenQueryStore正在开发中，有关更新或任何问题，您可以检查twitter帐户或OpenQueryStore GitHub页面。

其他实施 (Other implementations)

The OpenQueryStore has been implemented in ApexSQL Plan, a free tool for SQL Server query execution plan analysis and performance tuning. OpenQueryStore features and updates will be released with new versions of ApexSQL Plan

OpenQueryStore已在ApexSQL Plan中实现， ApexSQL Plan是用于SQL Server查询执行计划分析和性能调整的免费工具。 OpenQueryStore功能和更新将随新版本的ApexSQL Plan一起发布

参考资料 (References)

• OQS Dashboard OQS仪表板
• Query Store functionality in earlier versions of SQL Server SQL Server早期版本中的查询存储功能
• Introducing Open Query Store 引入开放查询存储
• Query Store functionality without SQL Server 2016 没有SQL Server 2016的查询存储功能

翻译自: https://www.sqlshack.com/openquerystore-sql-server-installation-configuration/