Submit a ticketCall us
Home > Success Center > Database Performance Analyzer (DPA) > Knowledgebase Internal > SQL is not available for some queries when DPA is monitoring an Azure SQL database instance

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

Updated September 5, 2018

Overview

When DPA is monitoring an Azure SQL database instance, 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.

Environment

  • DPA 11.0 to 12.0
  • Monitoring an Azure SQL database instance

Cause 

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

Resolution

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', 'DmvCollector', 'MetricsDownloader', 'TdService', 'NodeAgent', 'VaService') AND hostname not like 'DB[0-9]%[0-9]')

Exclude the SQL statement from the UI

  1. From any trend chart, click the SQL hash to open the Query Detail 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.

 

Last modified

Tags

Classifications

Internal Use Only