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
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.
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.
More about the Query Store can be found on the SQL Server Query Store – Overview page.
In this article the installation process of the OpenQueryStore, setting of the OpenQueryStore Dashboards and some of the OpenQueryStored procedures/options will be explained.
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.
Before installation of the OpenQueryStore, data collection mode needs to be chosen first.
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.
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.
Also, before installing the OpenQueryStore, one of the two scheduling methods needs to be chosen, Service Broker or 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.
To do that, execute the following code in a query editor:
|
1 2 3 4 |
ALTER DATABASE Test SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE; GO |
To see whether Service Broker is enabled or not, execute the following code:
|
1 2 3 4 |
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.
When the mode is picked and the method is set, we can install the OpenQueryStore.
From the OpenQueryStore release page download and unzip the latest OpenQueryStore version:
Automated installation
Open the Window PowerShell dialog and navigate to the unzipped OpenQueryStore folder and paste the following code:
.\Install.ps1 -SqlInstance <Instance> -Database <dbName> -OQSMode Classic -SchedulerType “Service Broker” -CertificateBackupPath “<path>”
Replace the values <Instance>, <dbName> and <path> with appropriate one:
and press the Enter key.
Manual installation
Go to the unzipped OpenQueryStore folder and, under the setup folder, locate the 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.
For example, {DatabaseWhereOQSIsRunning} replace with the name of the database, in this case [AdventureWorks2014] database.
Replace the {OQSMode} placeholder with the Classic or Centralized mode and execute the script:
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:
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:
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.
More information about the installation process of the OpenQueryStore can be found on the Installing OQS page
After installing the OpenQueryStore to start the data collection process, execute the stored procedure [master].[dbo].[open_query_store_startup]:
|
1 |
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.
Even the installation goes well and the data collection is activated, the OpenQueryStore may not start collecting data.
To check why the OpenQueryStore didn’t start data collection execute the following code:
|
1 |
SELECT * FROM oqs.activity_log |
In the Results grid under the log_message column and view the generated message:
To resolve the above mentioned problem, in a query editor, execute the following code:
|
1 |
SELECT collection_active FROM oqs.collection_metadata |
If the result in the Results grid for the collection_active colum is false or zero (0):
Then this column in the oqs.collection_metadata table needs to be changed (updated) in order the OpenQueryStore starts collecting data:
|
1 |
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:
|
1 |
SELECT * FROM oqs.activity_log |
All data that the OpenQueryStore collects, as well as settings, are placed under the following tables:
- oqs.activity_log
- oqs.collection_metadata
- oqs.excluded_queries
- oqs.intervals
- oqs.monitored_databases
- oqs.plan_dbid
- oqs.plans
- oqs.queries
- oqs.query_runtime_stats
- oqs.wait_stats
- oqs.wait_type_filter
Note, this can be changed over time since the OpenQueryStore is in active development.
Configuration information for the OpenQueryStore are placed under the oqs.collection_metadata table:
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.
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.
The oqs_mode column is used to inform the OpenQueryStore which mode is used (Classic or Centralized)
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.
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.
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.
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)
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.
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.
OpenQueryStore Dashboards
The OpenQueryStore Dashboards visualize information that the OpenQueryStore has collected by showing them in vireos chart formats.
To install dashboards, go to the OpenQueryStore Dashboards GitHub page and, from there, download the OpenQueryStoreDashboard.rdl and OpenQueryStoreWaitStatsDashboard.rdl files:
Or if you have already downloaded the latest installation OpenQueryStore folder, these two dashboards are located under the Dashboards folder:
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:
Navigate to the OpenQueryStoreDashboard.rdl and OpenQueryStoreWaitStatsDashboard.rdl files and press the Open button:
The Run Custom Report warning message box will appear. Press the Run button to continue:
This will open the OpenQueryStore Dashboards.
Note if these steps you do in SSMS 2012 or SSMS 2014, the following message may appear:
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)
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:
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.
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:
The next chart shows the top 10 queries in the last hour based on the amount of executions:
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:
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:
The Wait Stats Dashboard shows the waits status of the last hours:
All available wait types are placed in the wait_type_filter table:
|
1 |
SELECT * FROM oqs.wait_type_filter |
Also, these dashboards can be printed or exported in Excel, PDF, Word file:
Removing the 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:
The OpenQueryStore is in active development and, for updates or any issues, you can check the twitter account or the OpenQueryStore GitHub page.
Other implementations
The OpenQueryStore has been implemented in ApexSQL Plan, a tool for SQL Server query execution plan analysis and performance tuning. OpenQueryStore features and updates will be released with new versions of ApexSQL Plan
References
- OQS Dashboard
- Query Store functionality in earlier versions of SQL Server
- Introducing Open Query Store
- Query Store functionality without SQL Server 2016
He is a prolific author of authoritative content related to SQL Server including a number of “platinum” articles (top 1% in terms of popularity and engagement). His writing covers a range of topics on MySQL and SQL Server including remote/linked servers, import/export, LocalDB, SSMS, and more.
In his part-time, Zivko likes basketball, foosball (table-soccer), and rock music.
See more about Marko at LinkedIn
View all posts by Marko Zivkovic
- How to connect to a remote MySQL server using SSL on Ubuntu - April 28, 2020
- How to install MySQL on Ubuntu - March 10, 2020
- Using SSH keys to connect to a remote MySQL Server - November 28, 2019