Analyzing The Reasons For Optimization Failure In Operational Environments

Query optimization in relational databases is one of the main challenges in real-world operational environments. In many organizations, despite advanced indexes, statistics, and optimizer techniques, some queries still execute, which increases response time, overuses server resources, and reduces performance. This study analyzes 45 real-world queries from three different operational environments, including a user management system, an online store, and a university system, and attempts to identify and classify the main causes of query failures. Upon close examination, five main factors were identified: poor schema design, incorrect indexing, inappropriate execution plan selection, server load and resources, and human error of DBAs. Empirical analysis shows that there are significant effects that are due to incorrect and incomplete schema design and correcting it can reduce response time, sometimes by up to ~80%. Execution Plan Review and Managing (Parameter Sniffing) also play a vital role in real-world optimization. The results of this study are compared with previous research and show that it is much more complex than laboratory environments and requires a combination of technical and practical methods of DBAs. In addition to providing practical solutions to improve system performance, the findings provide a foundation for building future research in the field of query optimization in large and distributed environments.