Detection of Locking Events Exploiting the TempDB Service Database in Information Systems

This article discusses the TempDB service object in a relational database management system (RDBMS). The TempDB object is designed to temporarily store information and improve system efficiency. The DBMS plays a key role in the composition of analyzed information systems. All data operations in a relational DBMS are performed using SQL (Structured Query Language). Regardless of the client-server interaction model implemented, the application server always interprets SQL queries from any programming language. The relational algebra underlying relational DBMSs includes fundamental mathematical operations from set theory, such as union, intersection, and difference, as well as selection and sorting. SQL enables the efficient generation of queries for sets of tables while minimizing RAM usage costs. Some processes involving TempDB are explicit, including the creation of temporary tables with the “CREATE #Temp_Table” instruction and working with cursors and indexes. Other operations are implicitly implemented within the relational algebra and automatically access the service database. These operations include “JOIN” and “UNION,” as well as sorting via “ORDER BY” and “GROUP BY” constructs. The most controversial SQL commands from the perspective of relational algebra are “LEFT JOIN” and “RIGHT JOIN”. According to experts and database administrators, these operations significantly differ in resource consumption. However, this study found that using the “RIGHT JOIN” construct causes the highest load. Thus, frequent use of the “JOIN” command with the “RIGHT” parameter may indicate the accidental or deliberate exploitation of TempDB to initiate locking processes, which results in a decrease in overall system performance. This study aims to identify and analyze events that trigger locks in TempDB during relational algebra operations, which negatively impact system performance. The study aims to monitor information processes in retrieval systems to identify blocking events associated with the TempDB service database.