Database Performance Tuning and Its Principles

Performed on the client side, SQL performance tuning is used to generate the query will return the desired effect in the least amount of time, using the least amount of the server's resources as possible.

What is database performance tuning?

Database performance tuning is a set of procedures and practices geared towards optimizing the performance of the database and its queries. The goal is to have the queries execute as fast as possible, creating the most efficient system.

What is the focus of most performance tuning activities, and why does that focus exist?

The focus of performance tuning activities is on the database's queries. This is because queries require the most processing power and ultimately the most time to complete. They also generate the results needed by end users. By tuning the performance of the queries, users are benefited by faster results, allowing for quicker decisions.

What are database statistics, and why are they important?

Database statistics are characteristics gathered about database objects to paint a picture of its structure and resources. They may obtain information about tables, indexes, and computer resources that are being used. They are important because they can provide useful information used in the performance tuning of a database. From these characteristics, decisions can be made about optimization.

How are database statistics obtained?

These statistics are stores in the system catalog in specially designed tables. These statistics are obtained from these tables and are often updated frequently to generate the most accurate information.

What database statistics measurements are typical of tables, indexes, and resources?

Tables - number of rows, disk blocks used, row length, number of columns in each row, number of distinct values in each column, maximum & minimum values in each column, columns that have indexes, etc.

Indexes - number and name of columns in the index key, number of key values in the index, number of distinct key values in the index key, histogram of key values in an index, etc.

Resources - logical and physical disk block size, location and size of data files, number of extends per data file, etc.

How is the processing of SQL DDL statements (such as CREATE TABLE) different from the processing required by DML statements?

DDL statements will make changes to the data dictionary or system catalog while DML statements focus on manipulation of end-user data.

In simple terms, the DBMS processes queries in three phases. What are those phases, and what is accomplished in each phase?

Parsing - DBMS chooses most efficient execution plan for SQL query.

Execution - the SQL query is executed using the selected execution plan.

Fetching - the data is fetched by the DBMS and returned to the client.

If indexes are so important, why not index every column in every table? (Include a brief discussion of the role played by data sparsity.)

Creating indexes can be very beneficial as long as a practical balance is achieved. If every column were index would just generate too much maintenance processing for the many created indexes. Data sparsity can help with this determination of needed indexes. It refers to the number of different values a column could have. If the sparsity is low, a high percentage of table rows may be read anyway and creating an index unnecessary.

What is the difference between a rule-based optimizer and a cost-based optimizer?

Rule-based optimizers operate on a set of preset rules to determine query execution while cost-based optimizers use sophisticated algorithms. The cost-based optimizer will utilize statistics to help determine the lowest cost and best execution method.

What does RAID stand for and what are some commonly used RAID levels?

RAID (Redundant Array of Independent Disks) - utilize multiple disks to create virtual disks formed by several individual disks.

Levels:

0 - known as a striped array, the data blocks are spread over separate drives. Minimum of two drives.

1 - known as mirroring or duplexing, the same data blocks are written to separate drives. Minimum of two drives.

3 - data striped across separate drives and parity data is computed and stored on a dedicated drive. Minimum of three drives.

5 - data and parity striped across separate drives. Minimum of three drives.