Installation

Installing OQS

If you experience any issue while trying to install OQS please let us know by raising an issue here on GitHub.

Minimum requirements

  • Latest patched version of Windows Server 2008 or newer
  • Latest patched version of .Net Framework (3.5 SP1 or newer)
  • SQL Server 2008 or newer (any edition)
  • SQL Server Management Studio 16.x or newer (For Custom Reports)
  • Systems using FIPS-140-2 compliance are not supported

Pre-Installation

Before installing OQS, a decision must be made about which mode should be used. OQS offers two different data collection modes: “Classic” and “Centralized”

Classic (Available from v1.0)

Classic mode acts very similarly to the Query Store feature inside SQL Server 2016 and above. OQS is activated at the database level i.e. you choose which database should have OQS installed into it. The OQS setup then creates a schema (oqs) and creates the OQS objects to enable data capture and storage. Query runtime statistics are collected for the queries run inside the database where OQS has been installed.

Centralized (Available from v2.0)

Centralized mode is ideal for users who wish to have OQS collect query statistics for multiple databases inside one instance and store the statistics in a separate central database. This mode offers more flexibility in the collection method and provides a mode which avoids requiring to create new objects inside an existing database (possibly a problem for 3rd party application databases).

There are two possible scheduling methods for both classic and centralized modes:

Service Broker

Service Broker can be chosen to control the scheduling of data collection and background maintenance. This requires that Service Broker is activated either in the database where classic mode is running or in the OQS database created for centralized mode. A certificate, specialized SQL Login and a startup stored procedure are created for Service Broker scheduling

The uses of certificates is both a requirement of running OQS using Service Broker and a security aspect to allow data collection with a minimized permission set.

Service Broker allows for scheduling similar to that of “real” Query Store, in that no jobs are created and no job schedules are required – the scheduling and configuration is more tightly integrated into the target OQS database.

IMPORTANT NOTICE: Service Broker scheduling requires Service Broker to be activated in the target database. This operation requires an exclusive database lock. The PowerShell installer will fail installation if this exclusive lock cannot be obtained. We therefore recommend that Service Broker is activated manually in the target database if it is also being used by other users/processes.

SQL Agent

SQL Agent can be chosen for controlling the scheduling of data collection and background maintenance. This allows are more active administration of scheduling and is more “front and center” in a SQL Server instance with OQS installed on it.

Wait Stats

Both modes of OQS collect instance wide wait stats and store these in the OQS data store. There is a matching dashboard available from this project site to allow you to view this information too.

Installation of OQS

Installing OQS is a simple process.

Automated setup (preferred method)

  1. Download the latest release of Open Query Store from the releases page and unzip the file.
  2. Open a PowerShell console and navigate to the location of the unzipped files. Copying the command below change the values of <Instance><dbName> and <path> and OQS will be installed for you. (This example will install OQS in classic mode using Service Broker for scheduling)

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

Note: If you do not have internet access you can copy all the files from other computer and then, on a PowerShell session navigate to your folder and just run the 2nd line of code.

Manual

  1. Open the appropriate installer files (install_*.sql) in your favorite T-SQL editor.
  2. Connect to the SQL Server instance and database where you wish to install OQS.
  3. Run install_open_query_store_base.sql (ensuring to replace the placeholders in braces {} )
  4. Run install_gather_statistics.sql (ensuring to replace the placeholders in braces {} )

The remaining install_*.sql files need to be chosen depending on the scheduler method you wish to use.

Service Broker

  1. Run install_service_broker.sql (ensuring to replace the placeholders in braces {} )
  2. Run install_service_broker_certificate.sql (ensuring to replace the placeholders in braces {} )
  3. Delete the certificate file that was exported from SQL Server in the last step
  4. Start the data collection process by executing stored procedure [master].[dbo].[open_query_store_startup]
  5. You’re done!

SQL Agent

  1. Run install_sql_agent_job.sql (ensuring to replace the placeholders in braces {} )
  2. Create an appropriate schedule for the job
  3. You’re done!

OQS is now installed on your system, but will not actually start to collect data until you are ready (even if the Service Broker / SQL Agent Job is started). The next step is to ensure the settings are as you desire and then begin data collection.

Head over to the “Setup” page for more details on the next steps.

Importing the OQS Dashboards

The OQS Dashboards can be viewed directly from SSMS (v16. or V17.).

  1. Download the OpenQueryStoreDashboard.rdl and OpenQueryStoreWaitStatsDashboard.rdl files from the GitHub page and store it on a machine with SSMS installed.
  2. Right-click on a user database that has the OQS enabled and select “Reports – Custom Reports”.
  3. Navigate to the location were you stored the .rdl files and open it.
  4. The OQS Dashboard should open, if you get a error message make sure the OQS is installed and running on the database.

For more details on the dashboards, head over to the dashboard page