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:

Field

Brief Description

ErrorTime Date time when error occurs
User User name, who has received the error
Message Error message
Source, StackTrace etc Any number of fields to store all additional information about error.
Type It 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.

Field

Brief Description

ReportName Unique name of the report.
User User name, who has executed the report
DateBegin Date time when the report was launched
DateEnd Date time when the report was received.
Paremeters What filters were transferred to the report procedure.

System objects operations’ duration

Field

Brief Description

ObjectName Unique name of the object.
User User name, who has performed the save\open operation
DateBegin Date time when the operation was started on the server
DateEnd Date time when the operation was finished on the server
Type Type of the operation (in our case SAVE, OPEN, LIST OPEN).
LogXML Additional 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

Field

Brief Description

LogType Type of the record (e.g. SAVE operation, number of timeouts, etc.)
ObjectName Name of the object (for global records (such as number of timeouts) – null value)
PeriodDate Date without time
LogXML All 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.

Advertisements
This entry was posted in Database development, SQL Server, Technology and tagged , , . Bookmark the permalink.

One Response to Database structure to store performance metrics

  1. Pingback: Автоматизация восстановления последовательности. Часть 2: логирование работы системы — выбор подхода | 1C: техника и управление

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s