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:
|ErrorTime||Date time when error occurs|
|User||User name, who has received the error|
|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.
|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
|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
|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.