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.
