SQL Sentry - Azure DB target not collecting Top SQL data

1/11/2022 5:07 PM

11/7/2025 10:54 PM

Overview troubleshooting Top SQL not collecting for Azure SQL DB target(s). The following message may be displayed within the Client when opening the Top SQL tab.

SQL Sentry (SQLS)

SQL Sentry schema is blocking the XML from collecting in Top SQL.

Removing the SQL Sentry Schema, then rebuilding them when the monitoring services are restarted resolves it in this specific scenario.
Here are the resolution steps:

1. Connect to your Azure SQL DB via SSMS (or any other management application you use)
2. Expand the tables for the database
3. There should be some tables that were added related to SQL Sentry
4. Delete all the tables with the SQLSentry schema on the Azure SQL DB target(s) manually in SSMS or use the script below to remove objects for Azure SQL DB target.
5. After you've deleted the tables, please restart the SentryOne Monitoring Service in order to re-create the tables.
6. After that, run some queries against the target (if possible) to see if they are collected. Sample query to test with below.

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.


--Azure SQL DB Removal Script
if exists (select * from sys.objects where object_id = object_id(N'[SQLSentry].[spGetProcedureStatsData]') and OBJECTPROPERTY(object_id, N'IsProcedure') = 1)
drop procedure [SQLSentry].[spGetProcedureStatsData]
if exists (select * from sys.objects where object_id = object_id(N'[SQLSentry].[spGetQueryStatsData]') and OBJECTPROPERTY(object_id, N'IsProcedure') = 1)
drop procedure [SQLSentry].[spGetQueryStatsData]
if exists (select * from sys.objects where object_id = object_id(N'[SQLSentry].[SQLSentryObjectVersion_20]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1)
drop table [SQLSentry].[SQLSentryObjectVersion_20]
DECLARE @listOfSqlSentryTables VARCHAR(MAX)
DECLARE @sqlStatement VARCHAR(MAX)
select @listOfSqlSentryTables = COALESCE(@listOfSqlSentryTables+',' ,'') + 'SQLSentry.' +name from sys.objects where name like N'ProcedureStats%' and OBJECTPROPERTY(object_id, N'IsUserTable') = 1 and schema_id = schema_id('SQLSentry')
set @sqlStatement = 'drop table ' + @listOfSqlSentryTables
exec(@sqlStatement)
SET @listOfSqlSentryTables = NULL;
select @listOfSqlSentryTables = COALESCE(@listOfSqlSentryTables+',' ,'') + 'SQLSentry.' +name from sys.objects where name like N'QueryStats%' and OBJECTPROPERTY(object_id, N'IsUserTable') = 1 and schema_id = schema_id('SQLSentry')
set @sqlStatement = 'drop table ' + @listOfSqlSentryTables
exec(@sqlStatement)
if schema_id('SQLSentry') is not null
drop schema SQLSentry

Here is a sample query to run against the Azure SQL DB that goes along with the default minimum threshold for Top SQL statements that is configured in the client to ensure statements are being captured again. 

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

WAITFOR DELAY '00:00:05';