In the previous publication, we raised the topic of how DB Service diagnoses failures after receiving complaints from customers. We considered the first large group of possible failures - infrastructure problems.
In this article, we will discuss the second group, logical problems related to the DBMS internal operation. Although some of them may be caused by malfunctions in the physical hardware operation, it is the logical problem that our engineers are responsible for solving.
1. Interlocks
When executing transactions, DBMSs impose locks on the data - temporary restrictions on performing some data processing operations, such as reading and writing. This allows queuing all the transactions working with a particular row in the database and helps to protect data integrity.
SQL locks are an important mechanism of DBMS operation; however, it is not uncommon when the suboptimal intersection of processes causes different problems of simultaneous data access. In such situations, the most important thing is to find the root of the problem.
Analysis of expectation types and resources allows you to analyze the situation and decide what to do with it. If there are many locks and the culprit is not obvious or there are several of them, you should build a “lock tree” and it will become clear exactly who is the “root”.
2. Non-optimal state of tables that participate in queries
The next important aspect of logical problems hindering the normal operation of the database is the suboptimal state of tables. It is primarily about the fragmentation of indexes and tables (if they are without a clustered index) and the relevance of statistics on them.
● If an index is highly fragmented (more than 50% or even more than 90%), reading it increases the number of CPU cycles required to execute a query compared to a defragmented index (less than 10%).
● Irrelevant statistics, on the other hand, can lead to the selection of a suboptimal query plan that cannot be completed at the desired speed.
At DB-Service, we use our scripts to analyze statistics, allowing us to get the most accurate data.
3. Lack of appropriate data structure
Another aspect of logical problems in DBMSs is the lack of necessary data structure. It can be about indexes or even statistics.
● In the absence of a suitable index, a query may spend a lot of time scanning for an unsuitable one. Often the missing index is reported by the query optimizer at the top of the query plan.
● Lack of necessary statistics also does not contribute to fast query execution. In the query optimizer, data lacunae are also displayed as warnings on certain sections.
A query optimizer is a mechanism controlled by the DBMS kernel that translates TSQL commands into a query plan. This process itself is commonly referred to as compilation. Compilation is performed before each query execution if its plan has not been compiled earlier and saved in the cache.
As you can see from the example in the screenshot above, the query optimizer does not find the required index, but it does not mean that you should create it right away. The first thing that is done in such situations is to check the structure of existing indexes, estimate the table size, try to execute the query manually and analyze its plan. A suitable index for this query may exist, but the optimizer has not selected it for some reason.
4. Problem inside the DBMS logic
The query execution plan is not always built optimally. If it is already compiled, no work during the query execution will result in the system speeding up - all optimizations will be relevant only for subsequent runs.
There may be several reasons for problems with the query logic:
1. Irrelevant statistics. As a consequence, for example, an index scan operator is selected instead of a search operator, or the optimal order of table selection is violated when merging tables. In such a situation it is necessary to recalculate statistics for problem objects.
2. High fragmentation of a suitable index. As a consequence - the optimizer may choose a less fragmented but also less suitable index. In this case, it is necessary to defragment the index.
3. Large cache of old query plans given their variability. As a consequence - in the case of parameterized queries, previously compiled plans are poorly suited for new runs. In case of such problems, you should clear the procedural and session cache.
Symptoms of SQL logic problems inside the DBMS are not always obvious. For example, if many small queries are executed with 500 MS instead of 10 MS, the difference will not be noticeable to the human eye or many diagnostic scripts, but the processing speed will be slowed down by a factor of 50.
As a result, the problem of one user with one process, scaling up, can become business critical and hence also needs to be fixed as soon as possible.
In the case when we need to analyze a lot of small requests, we can configure a data collector for the sessions of interest.
Analyzing the number of queries and resource consumption in this example does not suggest a problem. However, if the first query was executed 120 times, it would be the first candidate for optimization. If after fragmentation, updating statistics, and restarting the procedure, the query would have been executed 1000 times, we could talk about an eightfold speedup as a result of troubleshooting.
Conclusions
In this article, we described the main aspects of logical problems occurring on DBMS servers. SQL lock conflicts, suboptimal states of tables involved in queries, lack of the required data structure, and problems within the DBMS logic were considered.
Although a general understanding of how to diagnose such problems may be useful for every system administrator, we would like to emphasize once again that the basic analysis and elimination of logical problems on DBMS servers is the responsibility of «DB Serv». Our engineers at any time of the day will help with the maintenance of any database and the whole complex of works on their administration so that your business is reliably protected from technical failures.
