OpenQueryStore edited this page Sep 6, 2017 · 6 revisions

Setting up OQS

After installation OQS is in a dormant state and will not collect any data until you configure and activate it.

OQS is designed to require minimal configuration before it can be used, and has a set of defaults that are based on the defaults supplied with the Query Store feature in SQL Server 2016.

Configuration Table

The configuration information for OQS is stored in a single table:

CREATE TABLE [oqs].[collection_metadata]

 (
     [command]             nvarchar (2000),         
     [collection_interval] bigint         NOT NULL, 
     [oqs_mode]            nvarchar (20)  NOT NULL, 
     [oqs_classic_db]      nvarchar (128) NOT NULL, 
     CONSTRAINT [chk_oqs_mode] CHECK ( [oqs_mode] IN ( N'classic', N'centralized' )),
     [collection_active]   bit            NOT NULL
 );

The "Command" column stores the data collection stored procedure call. OQS will call this data collection stored procedure in regular intervals to gather query runtime statistics and store them in the OQS.

The "collection_interval" column stores the collection interval in seconds. OQS will use this to control the collection interval.

OQS is designed to read this configuration information for each iteration of the collection loop, meaning the configuration can be reconfigured at any time and be used immediately.

The "oqs_mode" column informs OQS which mode is running.

The "oqs_classic_db" column stores the database where OQS is installed and also directs classic mode to only run in that database.

The "collection_active" column allows OQS data collection to be activated and deactivated. The installation process sets this to 0 (deactivated) and needs changing to allow data collection to occur.

Classic Mode

Classic mode uses Service Broker to control the regular collection of query run-time statistics. The value in the column "collection_interval" is directly called inside the Service Broker activation stored procedure "oqs.activate_oqs_scheduler". This is the only value that would need to be changed if the default value (60 seconds) is incorrect.

Once the collection interval has been configured to the desired value, data collection is activated by running the stored procedure "oqs.start_scheduler". This stored procedure starts an instance of OQS data collection if it isn't already running.

The logic inside the stored procedure "oqs.start_scheduler" allows for the procedure to be called multiple times without adverse effects.

The opposite of starting collection is stopping it. To deactivate data collection in OQS, you simply run the stored procedure "oqs.stop_scheduler". This will deactivate the Service Broker queue and data collection will no longer occur.

Automating Data Collection with Service Broker

Classic mode uses Service Broker for regular data collection, as this avoids the need to use the SQL Agent (which isn't available in Express Edition). However, we need to ensure that the queue is activated at SQL Server startup. The only way that we know is to use a startup stored procedure. A template for creating and activating this stored procedure can be found in the code file "install_service_broker.sql".

This file creates a simple stored procedure that calls the "start_scheduler" stored procedure inside the database where OQS has been activated. The code file needs modification to fit your environment.

Once the startup procedure has been installed, the OQS data collection will occur whenever SQL Server is started.

Important note: Startup stored procedures are not run if Traceflag 4022 is activated, or if SQL Server is started in minimal configuration mode, or if "scan for startup procs" option is deactivated via sp_configure

Centralized Mode

Centralized mode can use either Service Broker (as described above in Classic Mode) or using SQL Agent.

TODO: Description of SQL Agent

You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.