Treffer: Praćenje i podešavanje performansi baze podataka u sustavu SQL Server

This thesis addresses the problem of monitoring and tuning the performance of databases in SQL Server, focusing on the use of the Database Engine Tuning Advisor (DTA) tool. The thesis investigates how optimization recommendations affect query performance under different conditions. A test environment was implemented that includes two databases with different structural complexity on which the influence of the data load level can be analyzed. Under the given conditions of the experiment, DTA generated optimization recommendations whose actual impact was quantified through a comparative analysis of the main performance metrics (query execution time, CPU time, number of logical reads and writes) captured by the Query Store tool - before and after applying the recommendations. The query execution plans were also analyzed in the same context. The results confirmed that correct indexing is one of the main prerequisites for improving performance, especially in terms of optimizing the execution of SELECT queries. However, the analysis showed that the recommendations can also lead to performance degradation, as the trigger-based costs and the additional load that can be caused by new indexes are neglected.
Ovaj rad bavi se problematikom praćenja i podešavanja performansi baza podataka u sustavu SQL Server, s fokusom na korištenje alata Database Engine Tuning Advisor (DTA). U radu se istražuje kako preporuke za optimizaciju utječu na performanse upita pod različitim uvjetima. Implementirano je testno okruženje koje uključuje dvije baze podataka različite strukturalne složenosti nad kojima se može ispitivati utjecaj razine opterećenja podacima. U zadanim uvjetima eksperimenta DTA je generirao preporuke za optimizaciju, a njihov stvarni utjecaj kvantificiran je komparativnom analizom ključnih metrika performansi (trajanje upita, CPU vrijeme, broj logičkih čitanja i pisanja) prikupljenih alatom Query Store – prije i nakon primjene preporuka. Također su, u istom kontekstu, analizirani i planovi izvršavanja upita. Rezultati su potvrdili da je ispravno indeksiranje jedan od glavnih preduvjeta za poboljšanje performansi, pogotovo u aspektu optimizacije izvršavanja SELECT upita. Međutim, analizom je utvrđeno da preporuke mogu dovesti i do degradacije performansi zbog zanemarivanja troškova okidača i dodatnog opterećenja kojeg mogu uvesti novi indeksi.