In the previous two articles, we described in detail how DB-Service diagnoses infrastructure problems on DBMS servers and problems related to SQL logic. Completing the series of publications about the basic analysis of these problems, it is necessary to say a few words about the complex problems that arise due to the intersection of the hardware and DBMS peculiarities.
Scenarios of complex problems on a DBMS server
Based on the experience of our engineers, let us consider five cases that illustrate what complex problems are and what they can lead to.
1. A server weak in terms of data input/output receives a large number of queries for which the required index is not created. The result is an increased load on the CPU and hard disk, which leads to a huge queue of requests that are stuck in the execution status. From the query execution plans it will be possible to understand that the required index is missing, but the repetition of such situations is a reason to think about moving to a more powerful hardware.
2. Due to a job failure resulting in a long service absence, the main working table in the database becomes very highly fragmented (more than 90%). The result is progressive CPU load and abnormal database growth.
3. If we are talking about a system with a large number of users, it is possible that two people launch simultaneously conflicting processes, which may result in root locks and a “tail” of hundreds of SQL locks that can practically stop the database operation. The same is possible if one user launches a process that imposes a long-term monopoly lock on one or more database tables.
4. Another example of a malfunction causing complex problems is the loss of optimality in the query plan. If it takes two hours instead of the usual two seconds to execute a request under new circumstances, literally within half an hour several hundred such requests can accumulate. This results in overloaded CPUs and hard disks, huge queues, slow operation of the whole system, and, therefore, complaints from customers.
5. Query execution time within a session pool may grow not only because of a broken query plan but also because of a large number of phantom rows. This leads to difficulty in localizing the problem: there is no suspicious load, no SQL lock conflicts, statistics update does not help, and indexes are not fragmented.
Conclusions
To summarize, it is important to emphasize that there is no universal button that can get rid of any DBMS problems. However, properly selected monitoring methods and tools allow you to localize the problems and outline the ways of their elimination.
The instructions described in this and previous articles do not cover even a tenth of possible problems, but they allow you to get a general idea of the difficulties your business may encounter in its work with databases. The ability to solve problems related to analyzing problems on a DBMS server comes with experience – and engineers of «DB serv» have plenty of it. If you contact us, you will receive a wide range of services for monitoring and administration of DBMS of different types.