ERP SQL Query Monitoring Module for System Reliability
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Enterprise resource planning systems face issues due to sub-optimal or defective SQL queries that can degrade system performance, leading to database saturation and unavailability, often caused by users forming or modifying queries with defects, which can be unintentional or malicious.
Innovation Solution
An ERP system with a monitoring module that analyzes SQL queries in real-time, determines execution plan metrics, and compares them against estimated metrics to identify and address queries that exceed performance thresholds, issuing alerts and terminating invalid queries to prevent system degradation.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If users are allowed to form and modify SQL queries freely, then query flexibility and user autonomy are improved, but system reliability and performance are degraded due to defective queries
Solution Approach 1:
The system implements a feedback mechanism by monitoring query execution metrics and comparing them against estimated metrics. When actual metrics deviate significantly from estimates (indicating potentially defective queries), the system provides feedback by identifying and flagging these queries for review, thus maintaining system reliability while preserving user flexibility in query formulation.
Solution Approach 2:
The patent introduces an intermediary monitoring layer between the user and the database execution engine. This intermediary analyzes query execution plans and metrics without preventing users from forming their own queries, thus acting as a mediator that preserves user autonomy while protecting system reliability by filtering out defective queries.
2Reliability
If comprehensive monitoring of SQL queries is implemented, then system reliability is improved, but device complexity increases
Solution Approach 1:
The monitoring system implements partial monitoring by focusing only on critical execution metrics that indicate defective queries, rather than comprehensively analyzing every aspect of query execution. This selective approach maintains system reliability while avoiding the complexity of exhaustive monitoring of all query parameters and execution details.
Solution Approach 2:
The system monitors changes in execution plan metrics rather than absolute values. By detecting significant deviations between estimated and actual metrics, the system can identify defective queries without requiring complex monitoring of every execution parameter, thus maintaining simplicity while ensuring reliability.
3Ease of operation
If defective SQL queries are allowed to execute, then ease of operation is maintained, but productivity decreases due to system degradation
Solution Approach 1:
The system performs preliminary analysis of query execution plans before full execution, estimating key metrics in advance. This preliminary action allows the system to identify potentially defective queries before they cause system degradation, thus protecting productivity while maintaining ease of operation by allowing legitimate queries to execute without interruption.
Solution Approach 2:
The monitoring system quickly compares actual execution metrics against estimated metrics and rapidly identifies defective queries that exceed threshold deviations. This rapid detection and skipping of problematic queries prevents system degradation and maintains productivity, while legitimate queries continue to execute with minimal interruption.
Data Source
AI summary
An enterprise resource planning system server comprising a SQL database and an executing module to execute an SQL query according to an execution plan to generate execution data relative to the execution of each SQL query; a monitoring module to determine an estimated execution plan metric associated with executing the SQL query according to the execution plan, and to determine whether a hardware performance metric associated with executing the query plan exceeds a hardware performance metric threshold. The monitoring module also determines whether the SQL query is invalid by comparing an execution plan metric associated with executing the execution plan with the estimated execution plan metric in response to determining that the server performance metric exceeds the server performance metric threshold.