Database Query Performance Analysis and Notification Delivery to External Applications

U ovom je radu s nekoliko različitih aspekata analiziran problem učinkovitosti baze podataka. Opisan je i testiran cjelokupni proces optimizacije u sustavu SQL Server, od detekcije neučinkovitih upita do unaprjeđenja u potrošnji resursa. Također, obrađena je problematika učinkovitosti komunikacije između web stranice i baze podataka. U radu je modelirana i implementirana ogledna baza podataka koja služi za demonstraciju optimizacije upita i alata koje poslužitelj nudi za uspješno obavljanje tog procesa. Opisan je rad ugrađenog optimizatora, a detaljnije su razrađeni pojmovi statistike, planova izvršavanja te procjene cijene koji se koriste u procesu odabira najučinkovitijih operacija. Kroz nekoliko primjera opisano je korištenje sustava Extended Events (EE) za praćenje događaja na poslužitelju te alata Database Tuning Advisor (DTA) za automatizirano pronalaženje mogućih poboljšanja. S aspekta komunikacije web stranice i potporne baze podataka, prikazana su i međusobno uspoređena dva pristupa – Query Notification i Polling. Usporedba navedenih pristupa zasniva se na detekciji prednosti i mana, pri čemu se relevantne metrike prate u različitim scenarijima opterećenja baze podataka.In this thesis, the problem of database efficiency is analyzed from several different aspects. The entire optimization process within SQL Server is described and tested, from the detection of inefficient queries to improvements in resource consumption. Furthermore, the communication efficiency between the website and the database was addressed as well. A sample database was both modeled and implemented, in order to demonstrate query optimization and the server tools that support the corresponding process. The operation of the built-in optimizer is described, and the concepts of statistics, execution plans and cost estimations, being used for selecting the most efficient operations, are elaborated in more detail. Several examples are provided, which describe the utilization of Extended Events (EE) – for monitoring server events, and Database Tuning Advisor (DTA) – for finding possible improvements automatically. From the aspect of communication between the website and the underlying database, two approaches are presented and comparatively analyzed – Query Notification and Polling. The comparison of the mentioned methods is based on the detection of both advantages and disadvantages, wherein the relevant metrics are monitored in different database load scenarios