Elevate Your Database Performance: The Power of Custom Query Monitoring With DX UIM

In today's data-driven world, while new storage solutions and data lakes continue to emerge, many companies still use traditional databases with specific needs for tracking activities.

Custom queries, tailored to particular applications or use cases, are crucial for identifying performance bottlenecks, slow-running queries, and resource-intensive operations.

Monitoring custom database queries is essential for the following efforts:

1. Query analysis: Capture and analyze query execution data, including execution plans, query duration, and resource usage. Identify slow-running queries, bottlenecks, areas for optimization, and ways to enhance system efficiency.
2. Alerts and thresholds: Set up alerts based on query execution time, resource utilization, or other relevant metrics. Receive notifications when thresholds are exceeded, allowing proactive intervention.
3. Trend analysis: Monitor query performance over time to detect patterns or anomalies. Identify long-term trends and plan for capacity scaling or query optimization.
4. User behavior insights: Understand how users interact with the database by tracking frequently executed queries, user access patterns, and potential security risks.
5. Security and compliance: Monitoring custom queries allows you to detect unusual patterns or unexpected behavior. It helps identify potential security risks, such as unauthorized access or suspicious activity, ensuring compliance with data protection regulations.
6. Troubleshooting and debugging: When applications encounter issues, monitoring custom queries provides insights into database-related problems. You can track down errors, investigate unexpected results, and troubleshoot issues efficiently.
7. Capacity planning: Understanding query workloads helps with capacity planning. By monitoring custom queries, you can anticipate resource requirements and scale your database infrastructure appropriately.

What and how

DX Unified Infrastructure Management (DX UIM) is capable of monitoring custom checkpoints for most relational enterprise databases, including:

• Microsoft SQL Server
• Oracle
• IBM DB2
• IBM Informix
• Sybase

DX UIM is an enterprise solution with around 200 probes that monitor various technologies, such as big data, storage, cloud, containers, virtualization, network infrastructure, servers, applications, systems, service response, and databases.

Getting started: Configure the database probe

1. Go to your DX UIM Infrastructure Manager or Admin Console.
2. Select the “robot” and “probe” you want to configure, in this case, sqlserver.
   Figure 1: Configure the sqlserver probe.
3. Proceed to the Templates tab, right-click, and Create new.
   
   Figure 2: Create new checkpoint.
4. Enter a name for your checkpoint/template.
5. Open the query tab and define a DB connection to the database you want to monitor the checkpoint (1).
6. Define the name for the file that your query will be stored in (2).
7. Specify the query you want to run (3) on this checkpoint and click Test (4).
   
   Figure 3: Configure the new checkpoint.
8. Verify the query by selecting the top 10 and check if the output is what you expected.
   
   Figure 4: Query execution results.
9. Establish the Message Variables you intend to utilize. In this example, obtain the STATEMENT_TEXT as the row key. Click Edit.
   
   Figure 5: Edit message variables.
10. Once the following screen is displayed, define the names you will use. Remember to define the row key, and click OK.
    
    Figure 6: Final variables visualization.
11. After defining your template, the checkpoint will appear as follows where you will then choose the Row identification.
    
    Figure 7: Screen after you define the message variables.
12. After setting the Message Variables, move to the General tab and create the QoS, click on the QoS List.
    
    Figure 8: QoS List.
13. A new QoS List window will open where you can right-click and click New. Configure as needed.
    
    Figure 9: QoS Configuration
    
    Figure 10: QoS list final view.
    
14. Enable the checkpoint, and you will be able to see the QoS and configure the threshold as needed.
    
    Figure 11: Performance Report Designer with custom QoS created.
    

In conclusion, DX UIM is a robust solution for managing traditional, virtual, and modern hybrid cloud infrastructures. With over two decades of proven resilience, reliability, and adaptability, the solution has secured the trust of its customers by offering a comprehensive, single-pane-of-glass view for full-stack infrastructure observability. Offering seamless integration with Broadcom's AIOps portfolio, DX UIM enhances operational efficiency and provides valuable data for smart incident correlation. As technology evolves, including advancements in cloud, microservices, and AI, DX UIM continues to adapt, focusing on four key value pillars:

1. Unified hybrid infrastructure monitoring: A single platform for infrastructure, thereby helping to alleviate tool sprawl and providing a unified view.
2. Intelligent operations: Supporting IT teams in running IT operations intelligently and efficiently with smart incident correlation.
3. Scalability for IT complexity: Ensuring reliability, security, and scalability.
4. AIOps and observability: Ingesting the data that will fuel AIOps in terms of data granularity, dependencies, and topology, enabling accurate correlation and analytics.