Breadcrumbs

How-to - Monitor DB Performance using Query Store (Lansweeper source)

Aim

CI Sync jobs are optimised to perform fast and reliably - however as all customers have different CMDB fleet profiles, these differences can impact performance.

On occasion, it can help to deep dive into performance metrics using the SQL Server Query Store feature. This article outlines how to leverage this feature to aid in performance improvements.


This KB applies to:

Applies to Versions

All

Applies to Source Connectors

Lansweeper (for all other source connectors refer to How-to - Monitor DB Performance using Query Store)

Applies to Destination Connectors

ServiceNow

Process

The process listed below is derived from the Microsoft article:

https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver17

In summary - the profiling process will consist of:

  1. Enabling Query Store and capturing execution metrics

  2. Exporting Query Store data for analysis

Enabling Query Store & capturing metrics

#

Step

1

You will need to know the name of the Lansweeper database being investigated. NB: Unless you have customized your installation then by default the Lansweeper database will be named “lansweeperdb”.

2

Using SQL Server Management Studio, run the SQL statement below

Note: Replace lansweeperdb with the name of your Lansweeper database if you have customized your installation. 

ALTER DATABASE lansweeperdb

SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY =
    (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    MAX_STORAGE_SIZE_MB = 3000,
    INTERVAL_LENGTH_MINUTES = 15,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 300
);

ALTER DATABASE lansweeperdb SET QUERY_STORE=ON;


3

Sufficient database activity will need to be recorded by Query Store to perform analysis.

Depending on the scenario under investigation, Syncfish will recommend a Job definition that is most likely to capture the metrics required. Please work with our Support Team to configure jobs as required.

It will be important to run a job exhibiting the slow performance!

4

Once jobs exhibiting the performance issues have been captured, the Query Store data is ready to be exported for analysis.

Exporting Query Store data for analysis

It is important to note that Query Store information does NOT contain any customer-specific or sensitive data.

Only query-based metadata such as table, index usage and execution statistics are captured.

#

Step

1

Clone the analysed database

Using SQL Server Management Studio, run the SQL statement below

Replacing:

  • lansweeperdb with the CI Sync RecVer DB that Query Store was enabled for

  • lansweeperdb_clone with the cloned database name 

DBCC CLONEDATABASE ('lansweeperdb', 'lansweeperdb_clone')


2

Backup the cloned database to disk, using compression

3

Send the backup file to Syncfish for analysis

4

The cloned database, performed in Step 1, can be dropped.

Disabling Query Store capturing metrics

Once investigations have concluded - Query Store can be disabled per below:

#

Step

1

You will need to know the name of the lansweeperdb database being investigated.

ALTER DATABASE lansweeperdb  SET QUERY_STORE=OFF;

How-to - Monitor DB Performance using Query Store (non-Lansweeper source)

Control Information

Created

Reviewed

Data Classification

PUBLIC
Classified in accordance with the Syncfish Data Classification Framework