SQL is not available for some queries when DPA is monitoring an Azure SQL database instance or ASMI

11/1/2018 8:23 PM

11/5/2019 6:13 AM

When DPA is monitoring an Azure SQL database instance or an Azure SQL Managed Instance (ASMI), occasionally DPA includes one or more unusual SQL statements in the trends charts:

• DPA does not display the SQL text. When you click the SQL Text tab, it displays the message SQL statement not available. 
• The machine name is DBnnn ("DB" followed by numbers -- for example, DB215).
• The user name includes either DBnnn\WF or NT AUTHORITY\SYSTEM.

Database Performance Analyzer

DPA is displaying a query that Azure runs to collect diagnostic data. These queries should be ignored by DPA's quickpoll.

You can prevent DPA from displaying these queries in either of the following ways:

• Configure the QUICKPOLL_WHERE_CLAUSE setting
• Exclude the SQL statement from the UI

Configure the QUICKPOLL_WHERE_CLAUSE setting 

1. Click Options > Administration > Advanced Options.
2. Click the DB Instance Options tab and select the database instance.
3. Select Support Options.
4. Locate the QUICKPOLL_WHERE_CLAUSE setting and click to edit it.
5. Enter the following as the New Value and click Update:

   AND (program_name not in ('BackupService', 'FullBackupService', 'DmvCollector', 'MetricsDownloader', 'TdService', 'NodeAgent', 'VaService') AND hostname not like 'DB[0-9]%\WF%')

Exclude the SQL statement from the UI 

1. From any trends chart, click the SQL hash to open the Query Details page.
2. In the upper-right corner, click SQL Properties.
3. Under Advanced Settings, deselect Show in Trends charts and click Save.
   The query is no longer displayed in trends charts.