Network Management

Long running queries in SWQL

This article discusses long running SWQL queries.

First published date

11/4/2018 7:50 PM

Last published date

3/10/2022 1:21 PM

Overview

Reasons for Long-Running SWQL Queries:

SQL Server - Performance optimization and best practices
Deadlocks
Timeouts
SWIS Performance

Product section

Orion Platform

Resolution

Debugging SWQL Queries

Query anatomy

Below is an example of a long-running query error from the Information Service v3 logs. The actual query is highlighted in yellow and the execution time in red.

2015-12-16 02:49:33,321 [4] WARN  SolarWinds.InformationService.Core.InformationService - (null) Support! -- LONG RUNNING QUERY: 
Session uuid:e975dde9-9eb6-4e0a-b877-902d339092bf;id=11 - Query took 26975.5012 ms: 
SELECT AlertObjectID FROM Orion.AlertObjects ao 
LEFT JOIN Orion.VolumesForecastCapacity no ON ao.EntityUri=no.Uri 
WHERE no.Uri IS NULL AND ao.EntityType = 'Orion.VolumesForecastCapacity' 
RETURN XML RAW

First, we will need to extract and format the query so that it is easily readable:

SELECT AlertObjectID FROM Orion.AlertObjects ao

LEFT JOIN Orion.VolumesForecastCapacity no ON ao.EntityUri=no.Uri

WHERE no.Uri IS NULL

AND ao.EntityType = 'Orion.VolumesForecastCapacity'

SWQL queries work just like SQL (© 2018 Refsnes Data, available at https://www.w3schools.com, obtained on December 3, 2018). The first line tells us what columns we are selecting and from which table. The second line is using a JOIN (© 2018 Refsnes Data, available at https://www.w3schools.com, obtained on December 3, 2018) to add in the VolumesForecastCapacity table. The 3rd and 4th lines filter the data from both tables. We have highlighted the table names in yellow here. This will help you to determine what data the query is pulling, in this case, Volume Forecasting Alerts.

Query Results

You can test the query by using one of the following tools

SWIS.aspx page on Orion Web Console

You can go to the following page to test SWQL queries:

http://[OrionServer]/Orion/Admin/Swis.aspx

SWQL Studio

This utility is installed with the Orion SDK

Default install location is:

C:\Program Files (x86)\SolarWinds\Orion SDK\SWQL Studio

You have several connection options for SWIS, the Orion (V3) Certificate will be the same one that our services use.

You can paste your query into a new Query window or you can generate a query using the list of tables on the left. This list is determined by which Schemas are installed. The Tables are not a 1=1 relationship with SQL, some SWIS tables will contain complex SQL queries.

Disclaimer: Please note, any content posted herein is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment. You elect to use third-party content at your own risk, and you will be solely responsible for the incorporation of the same, if any.

Disclaimer: Please note, any content posted herein is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment. You elect to use third-party content at your own risk, and you will be solely responsible for the incorporation of the same if any.