MAGNABase Database Analysis and Tuning Tool

Having been originally conceived as “DBA-in-a-can” tool, this product quickly grew into an “IT-consulting-team-in-a-can” system. In a matter of seconds, it performs a wide array of functions that are time-consuming and are normally handled by expert DBAs, operations, and software developers. It can potentially save between hours and months of work, for small to medium IT teams that cannot afford full-time database experts on payroll. Here’s how it works.

Through its plugins, MAGNABase supports a growing number of database back-ends. Currently they are:

• MS SQL Server
• MySQL
• Oracle
• PostgreSQL

On each, it can perform a set of analytical operations. Support varies from engine to engine. Almost all of the analyses produce a script that can then be reviewed, adjusted, and applied in a controlled manner. No direct changes are immediately made to the database, and superuser access is not required by MAGNABase. Refer to the plugin feature matrix for the features supported by DB back-end plugins.

	MS SQL	MySQL	Oracle	PostgreSQL
Slow-running queries
Storage overstatement
Compression
Insecure table access
Table audit
Missing indices
Unused indices
Models

Slow-running queries

This analysis uses several objects that MAGNABase creates in its own database on your server. At the center of it lies a variation of the famous Who Is Active stored procedure that runs on a specified schedule, in SQL Agent. The schedule interval alone defines how long is considered ‘long’. During its every iteration, the job collects information about the queries that are still running since the previous one. This information can then be visualized by MAGNABase in a user-friendly format. It is retained for a user-configurable number of days.

Compression

This analysis calculates statistics on the data in table columns and determines whether they can benefit from compression. It then generates a script that compresses columns that would compress considerably.

Storage overstatement

This analysis looks at column data types across your database and analyzes data stored in them. By comparing these two values, it determines by how much column data width or data type can be adjusted, in order to reduce table and index storage requirements while still preserving the integrity of the data. This analysis can be configured to pad character data by a desired value.

Insecure table access

This analysis uses the pattern of access to tables via stored procedures. It generates a script that creates CRUD stored procedures and revokes select/insert/update/delete permissions from users. By applying this script, the DBA can then expose the database to the outside world only through the stored-procedure-based API, preventing any user from directly accessing the data.

Table audit

This analysis creates audits for the tables in your database.

Missing indices

MAGNABase reviews history of query plans and determines which tables and columns can benefit from the creation of indices, then generates a script that creates them. This can dramatically improve performance of some of slower queries.

Unused indices

Opposite the Missing Indices analysis, MAGNABase determines which indices are not used by any queries and generates a script that drops them. This is beneficial to the reduction of extra storage space and improves performance.

Models

Common among all of the supported storage engines is the generation of high-level programming language models from tables. Currently, the following languages are supported:

• C#
• Java
• PHP

For each language, MAGNABase can be configured to expose columns as public fields, getters, getters and setters, or getters and setters with property change notification. Minimal manual editing may be required, and it normally does not go beyond global search and replace.