Microsoft SQL Server adapter collection times out in Aria Operations

Products

• VCF Operations/Automation (formerly VMware Aria Suite)

Issue/Introduction

• Unable to Fetch Microsoft SQL Server Instance Data In Aria Operations Manager in large memory SQL Server environments
• Sometimes the SQL dashboards will show empty or "No Data".
• Collector logs show: pollCompletionService method returned null or timed out before the result was returned.
• Log analysis (/storage/vcops/log/adapters/MicrosoftSQLServerAdapter/MicrosoftSQLServerAdapter_id.log) after the com.bluemedora.exuno.DataProvider class was added and enabling DEBUG logging on Cloud Proxy (Increasing Log Level of Aria Operations) reveals that a high-latency query, identified as 'Buffer Alone,' is consuming 120 seconds—accounting for 96% of the total collection window.
  • DEBUG [Collector worker thread 23] (861) com.bluemedora.exuno.DataProvider.debug - com.bluemedora.exuno.common.ExUnoTimer: Logging 88 durations.
  • DEBUG [Collector worker thread 23] (861) com.bluemedora.exuno.DataProvider.debug - com.bluemedora.exuno.common.ExUnoTimer:
 125.185s |----------------------------------------------------------------------------------------------------| Overall Duration
 125.157s |--------------------------------------------------------------------------------------------------- | Performing concurrent collection
   0.008s |-                                                                                                   | database Query: USE "master";WITH re..._id < 2 THEN a....Y database_name
   0.001s |-                                                                                                   | file Query: USE "master"SELECT  ...owth / 128.0 EL....database_files
   0.000s |-                                                                                                   | instance Query: SELECT CASE WHEN CON...DEX('Windows NT...windows_version
   0.000s |-                                                                                                   | instance Query: SELECT (@@servername...ervicename, (@@...) AS servername
 120.098s |-----------------------------------------------------------------------------------------------     | instance Query: SELECT CAST(COUNT_BI...)/(1024.0*4096....s WITH (NOLOCK)
• The slow query:
  
  • SELECT t5.cntr_value AS buffer_page_life_expectancy, buffer_ideal_page_life_expectancyFROM (SELECT * FROM sys.dm_os_performance_counters WITH (NOLOCK) WHERE counter_name = 'Page life expectancy' AND object_name LIKE '%Manager%') t5,(SELECT CAST(COUNT_BIG(*) * ((8 * 300)/(1024.0*4096.0)) AS DECIMAL (10,2)) AS buffer_ideal_page_life_expectancy FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)) T6WHERE t5.cntr_value < buffer_ideal_page_life_expectancy
• Timeout errors occurring every 5 minutes:
  
  • ERROR [Collector worker thread 23] (861) com.bluemedora.exuno.DataProvider.error - com.bluemedora.microsoft.sqlserver.ThreadUtils.JdbcPoller: pollCompletionService method returned null or timed out before the result was returned with timeout set to {120} seconds
  • DEBUG [Collector worker thread 23] (861) com.bluemedora.exuno.DataProvider.debug - com.bluemedora.exuno.common.ExUnoTimer: Run Time Since 'Performing concurrent collection': 125.157 seconds.

Environment

Aria Operations 8.x and above

Cause

This issue occurs because some queries executed by the Microsoft SQL Server Management Pack take a long time to complete, causing data collection delays or failures.

The adapter executes a specific query against sys.dm_os_buffer_descriptors to calculate Buffer Page Life Expectancy. On instances with large memory allocations, this query can take over 120 seconds to complete, causing the entire collection thread to time out and fail.

Resolution

To workaround this issue, exclude specific long-running queries by updating the microsoftsqlserver.properties file. Do doing this techically it would just disables them.

Steps: 

1. SSH into the Aria Operations node where the SQL Server adapter is configured.
2. Open the microsoftsqlserver.properties file using a text editor:
       vi /usr/lib/vmware-vcops/user/plugins/inbound/sql_server_adapter/conf/microsoftsqlserver.properties
3. Add the following line in the file:
       excluded_queries=Low Page Life Expectancy\,Buffer Alone\,Effective Memory\,Buffer Pool Size
4. Save the file.

Note:

If the Buffer Alone query is excluded, the following metrics will not be collected:

• Buffer Pool Size on Microsoft SQL Server Instance resources
• Buffer Ideal Page Life Expectancy on Microsoft SQL Server Database resources
• Memory|Effective Server Total on Microsoft SQL Server Database resources
• Memory|Effective Used on Microsoft SQL Server Database resources
• Memory|Effective Used Pages on Microsoft SQL Server Database resources
• MS SQL Server Low Page Life Expectancy alert

Follow the below to address the Widget Interactions to reflect the data

1. Try unselecting and reselecting the VM in the VMs Running Microsoft SQL widget.
2. On macOS, hold the Command (⌘) key while clicking the VM.
3. On Windows, try using Ctrl or Alt while clicking.
4. If a tooltip appears while reselecting, click on "Internal interactions" to refresh the widget.