Optimiser-based recommendations of physical database design

Die Komplexitiät aktueller relationaler Datenbank Management Systeme stellt eine immer größere Herausforderung an Datenbankadministratoren dar. Jede Laufzeitumgebung benötigt eine für sie angepasste Konfiguration, um performant zu operieren. Selbst innerhalb einer Umgebung können sich die Anforderungen im Laufe der Zeit ändern und eine erneute Anpassung erfordern. Dies zwingt den DBA sich kontinuierlich und intensiv mit dem System zu beschäftigen. Das Ziel eines modernen DBMS muss die Unterstützung des DBAs sein, um seine Arbeit mit automatisierten Prozessen und Handlungsabläufen zu erleichtern und ihm so stets schnelle und prezise Entscheidungen zu ermöglichen. Diese Arbeit zielt auf die Beschreibung und teilweise Umsetzung eines unterstützenden Systems, das die aktuelle DBMS Konfiguration zusammen mit dem aktuellen Anfrageverhalten analysiert und dem DBA Vorschläge unterbreitet, wie sich die Performanz und Effizienz des Systems verbessern lässt.Today's relational database management systems are made up of many complex components and managing these presents a growing challenge for database administrators. Every runtime environment can require different configurations to deliver adequate performance. Even withinthe same environment demands can shift over time when workloads change. Keeping up with these demands requires continuous effort from the DBA. The goal of a modern DBMS must be to support the DBA in his work with automated processes and workflows that allow him tomake quick and precise decisions. This work aims at describing and partially implementing asupportive system that will analyse the current DBMS configuration together with its workload to give recommendations on how to improve its performance and efficiency.Ilmenau, Techn. Univ., Diplomarbeit, 200

A Framework for the Automatic Physical Configuration and Tuning of a Mysql Community Server

Manual physical configuration and tuning of database servers, is a complicated task requiring a high level of expertise. Database administrators must consider numerous possibilities, to determine a candidate configuration for implementation. In recent times database vendors have responded to this problem, providing solutions which can automatically configure and tune their products. Poor configuration choices, resulting in performance degradation commonplace in manual configurations, have been significantly reduced in these solutions. However, no such solution exists for MySQL Community Server. This thesis, proposes a novel framework for automatically tuning a MySQL Community Server. A first iteration of the framework has been built and is presented in this paper together with its performance measurements

PerfXplain: Debugging MapReduce Job Performance

While users today have access to many tools that assist in performing large scale data analysis tasks, understanding the performance characteristics of their parallel computations, such as MapReduce jobs, remains difficult. We present PerfXplain, a system that enables users to ask questions about the relative performances (i.e., runtimes) of pairs of MapReduce jobs. PerfXplain provides a new query language for articulating performance queries and an algorithm for generating explanations from a log of past MapReduce job executions. We formally define the notion of an explanation together with three metrics, relevance, precision, and generality, that measure explanation quality. We present the explanation-generation algorithm based on techniques related to decision-tree building. We evaluate the approach on a log of past executions on Amazon EC2, and show that our approach can generate quality explanations, outperforming two naive explanation-generation methods.Comment: VLDB201

Evaluation of Sql Performance Tuning Features in Oracle Database Software

Timely access to data is one of the most important requirements of database management systems. Having access to data in acceptable time is crucial for efficient decision making. Tuning inefficient SQL is one of the most important elements of enhancing performance of databases. With growing repositories and complexity of underlying data management systems, maintaining decent levels of performance and tuning has become a complicated task. DBMS providers acknowledge this tendency and developed tools and features that simplify the process. DBAs and developers have to make use of these tools in the attempt to provide their companies with stable and efficient systems. Performance tuning functions differ from platform to platform. Oracle is the main DBMS provider in the world, and this study focuses on the tools provided in all releases of their software. A thorough literature analysis is performed in order to gain understanding of the functionality and assessment of each tool is performed. It also provides insight into factual utilization of tools by gathering responses through the use of an online survey and an analysis of the results

Techniques for Ensuring Index Usage Predictability in Microsoft SQL Server

The demand for carrying out high-performance operations with data is growing in parallel with the vast growth of data itself. The retrieval of data for analysis, the manipulation of data, as well as its insertion in data stores must all be performed very efficiently, using techniques that ensure speed, reliability and accuracy. This paper investigates the techniques and practices that improve the performance of data retrieving by the use of SQL and Microsoft SQL Server. Being that SQL is a declarative language that specifies what should be produced as a result, instead of how to achieve that result, this paper will look at the internals of SQL Server that affect the "how" of queries and data operations, in order to propose techniques that ensure performance gains. The paper will aim to shed light on the limitations and variance in index usage, and to answer the question why indexes are sometimes used, and other times not, for the same query. To overcome the index limitations the "index fusion" technique is proposed

Security of MSSQL databases

Cílem bakalářské práce je rozebrat problematiku bezpečnosti dat v rámci databází MSSQL 2005, 2008, 2008 R2 a 2012, kryptografickým algoritmům, šifrováním, zabezpečením databáze, sledováním výkonu databáze a nástrojům pro správu a analýzu dat. V práci jsou rozebrány bezpečnostní rizika, možnosti řešení jednotlivých rizik a optimální návrh varianty z hlediska zabezpečení/výkon.The aim of bachelor thesis is to discuss issues of data security within the databases MSSQL 2005, 2008, 2008 and 2012 R2, cryptographic algorithms, encryption, database security database performance monitoring and management tools and data analysis.The thesis contains analyzed the security risks, possible solutions for each risk and the optimal design options in terms of security / performance.

Suggestive Local Engine for SQL Developer: SLED

Information Technology (IT) industry recruits junior staff on regular basis. Most of the applications use databases to store or access the data. Structure Query Language (SQL) is used to communicate with database middleware. An expensive SQL statement may engage the data centers for longer time forcing the organizations to sellout high cost for data storage and maintenance. A tool is required for training the junior developers. This study proposes a Suggestive Local Engine for SQL Developer (SLED). It develops a warehouse using the optimized SQL statements collected from reputed software firms or expert team. This study uses the concept of data marts to grouped the data and frequent pattern search algorithm to calculate frequencies and support of patterns of SQLstatements. This system suggests the developers based on the common patterns of SQL statements used by those experts. It also warns the developers if their writing pattern maps to the outlier statement. This system helps all the junior developers in an organization and graduates in colleges or universities to practice with suggestions.Keywords: Suggestive engine optimized SQL, Data Warehous