Database structure to store performance metrics

In this I will continue previous one What performance metrics are important and how to collect them and will describe the database structure to store performance statistics. I will take as basis the structure designed by me and generalize it for common use.

The database can be divided into two parts:

  • Log tables, where every activity in the system is putting down. The data is stored for different types of records from two days to three months. These data is used to track errors and analyze details of system behavior for short periods of time.
  • Report table (in the future I’m planning to transfer it to Analysis Services), in which general information for the day is stored. This table is used for reports. We do not delete data in this table.

Below I will describe some basic structures that can be used to store performance metrics, and in the next posts I will discuss in details how to use each table to calculate system performance metrics.

Log Errors table

Let’s start from the table, in which we’ll put all errors that occur in the system. Its structure can be the following:

FieldBrief Description
ErrorTimeDate time when error occurs
UserUser name, who has received the error
MessageError message
Source, StackTrace etcAny number of fields to store all additional information about error.
TypeIt will be great to log an error type. You may use the following type: system exception, business logic error, sql server error, etc.

Reports log table

In the next table, we will store all data about work of the reports.

FieldBrief Description
ReportNameUnique name of the report.
UserUser name, who has executed the report
DateBeginDate time when the report was launched
DateEndDate time when the report was received.
ParemetersWhat filters were transferred to the report procedure.

System objects operations’ duration

FieldBrief Description
ObjectNameUnique name of the object.
UserUser name, who has performed the save\open operation
DateBeginDate time when the operation was started on the server
DateEndDate time when the operation was finished on the server
TypeType of the operation (in our case SAVE, OPEN, LIST OPEN).
LogXMLAdditional XML field for some additional logs. I will dwell on how this field can be used in the future posts.

General table to be used for reports

FieldBrief Description
LogTypeType of the record (e.g. SAVE operation, number of timeouts, etc.)
ObjectNameName of the object (for global records (such as number of timeouts) – null value)
PeriodDateDate without time
LogXMLAll needed information for reports in XML format. It will be different for each type, so XML is the best option.

This is how I’m presenting structure to store performance metrics. Here I’ve listed only basic fields and these tables can be expanded with additional fields as it is required for each system.

In the next post I will talk about custom reports that indicate problem directions.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *