Guidelines for Sizing Database

This document explains the database growth pattern of AVEVA Work Tasks databases. It is intended to help the user understand about the disk space, memory, and processor speed requirements for best performance of AVEVA Work Tasks.

Note: The performance characteristics presented in this document are not to be interpreted as a benchmark measurement for all the supporting systems. Only empirical testing on the target system can provide accurate data. The actual database size, memory used, I/O required is based on the respective implementation, concurrent user access, and so on.

Overview

AVEVA Work Tasks supports different versions of Microsoft SQL Server to store operational data. The analysis for database sizing is done on Microsoft SQL Server 2008 (SP3) - 10.0.5512.0 (X64).

By default, AVEVA Work Tasks databases are set to grow in an "Auto growth" mode as SQL Server default settings. Based on the best practices recommended by Microsoft and database experts, you should set the size of the repository database depending on the transactions and growth pattern.

A database should be sized appropriately so that the database does not need to auto grow, but should be configured by the user when required. It is recommended to configure the File Growth value to fix growth, based on the database growth pattern.

A desirable approach is to have a separate disk for repository and farm database.

Note: It is highly recommended that a Database Professional performs the tasks mentioned in this document. The purpose of the document is to provide guidelines for sizing AVEVA Work Tasks database. Essential maintenance tasks may vary based on specific implementation.

Farm Database

The farm database is a configuration database, basically used to map repositories, support Enterprise Server configuration, and so on. The database may grow when there is a massive usage of communication activities, such as email and SMS.

Repository Database

The repository database is a database for the repository related data. All the operational data required for the application exists in the database to keep the database healthy. Data such as various activities related information, the list data, forms definition, and workflow definitions is stored in the repository database. The database growth always relates to the features, such as list and queue, used by the application.

BI Database

The BI database is a placeholder for future development on business intelligence reports and does not occupy any space.

Parameters that affect the performance of an application include the environment, various databases involved, kind of workflow, and number and types of activities. For database sizing, the prospective data is captured to show the database growth pattern based on a typical workflow having 72 activities with a combination of engine and human activities. The information of the environment on which the data is captured along with the workflow used is included.

Database Growth Pattern

Table Level Growth

Table level growth is purely based on the features and Workflow used. Based on the sample workflow, the table level record growth is as follows:

Table Size

Farm Database

The details regarding the table size help in arriving at the maximum database growth pattern.

To calculate the storage needs, multiply the average row length and the number of records per month that will be entered into the database. This provides the details of the amount of storage required by each table. If a table has any variable length string fields, add 4 bytes to the average row length to account for overhead, which SQL Server requires when maintaining the table.

The following table shows different tables of the repository database and their size:

Repository Database

Default table size of the repository database is as follows and this may help to arrive at maximum database growth pattern.

Environment

The environment used to capture the database sizing related information is the Enterprise server with two web server nodes, two workflow engine servers, and an SQL Server mentioned as Database Server. The system configuration of the environment is as follows:

Web Server Configuration

Engine Servers

The configurations for the engine servers are same as that of the Web Server.

Database Server

Workflow Used

The database growth pattern is captured based on the performance test workflows, which include 72 activities altogether and 5 queues with a combination of automatic dispatch pattern and manual pick. In parallel, work item tasks are assigned to the queues. The workflow diagram is as follows:

LogFile Size

Every SQL Server database has a transaction log, which records all the transactions and the database modifications made by each transaction. The transaction log must be truncated on a regular basis to keep it from filling up. However, some factors can delay log truncation. Therefore, monitoring log size is important. This can be achieved by logging some operations minimally to reduce their impact on transaction log size.

The transaction log is a critical component of the database. If there is a system failure, the transaction log is required for database recovery to bring the database back to a consistent state.

Note: Ensure that the transaction log is never deleted or moved without having a clear understanding of its effects.

For more information regarding management of log files, it is recommended to consult a Database Professional.

TempDB

A temporary database is not directly used by AVEVA Work Tasks. However, the SQL Server internally uses this database for operations, such as sorting indexing. Therefore, it is recommended to maintain an ideal space for this database, to facilitate the above mentioned SQL server internal operations.

A default installation of any SQL Server edition will create a TempDB database with an 8MB data file and a 1MB transaction log file. For a lot of SQL Server installations, these file sizes are not sufficient. However, they are configured to auto grow by 10% as needed.

The auto grow feature enables a more hands-off approach to maintaining many SQL Server installations. However, this approach is not recommended because the database storage files cannot be used while they are autogrowing. This can lead to fragmentation of the files on the hard disk; thereby, leading to poor performance.

This recommendation is applicable to any SQL Server database, but it is even more relevant for the TempDB. When you restart your SQL Server instance, the TempDB is re-created (files will be reused if they already exist) and sized to the value specified in the database properties. The default values are 8MB for the data file and 1MB for the log file.

Note: The I/O operations are the most time consuming processes and the TempDb is being used exclusively by SQL Server for most of its operations. Therefore, it is recommended to have a separate disk for the TempDb than the operational database. A managed growth gives better performance. Please consult the Database Professional for the desired settings.

CPU Utilization

The CPU utilization on the database server is based on the queries or updates considered as a part of the workflow engine database activities. The CPU utilization varies with user interaction taken place as part of the UI activities. Various other parameters such as file fragmentation, I/O overheads, and so on also affect the CPU utilization. For more realistic information on the implementation, please consult a Database Professional.

Graph shows the CPU utilization.

I/O Statistics

Reading and writing to the disk is the heart of any database management system. Input/Output (I/O) performance is critical for databases and can make or break an application. In many environments, I/O is the main bottleneck for the database performance. The main task of I/O is to retrieve and to store records within files. The I/O performance can be improved by analyzing the specific issues by a Database Professional. Various SQL Server reports are available for analyzing the I/O related performance. The I/O performance purely depends on the specific environment. Thus, providing a snapshot of the I/O statistics may not be helpful.

http://blogs.msdn.com/b/sqljourney/archive/2013/06/04/how-to-troubleshooting-sql-server-i-o-bottlenecks.aspx

http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf

Performance Matrix

Following are some of the important parameters to consider for database sizing. Ideally, an implementation should provide the following performance. Count values above the following are not advisable.

References

For further information, refer the following links.

http://blogs.technet.com/b/patricg/archive/2007/10/18/sql-server-tips-n-5-tempdb-best-practices.aspx?Redirected=true

http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx

Definitions

Special Notes

• Actual growth pattern will be based on the implementation.

• Set the size of the database for a better performance.

• Keep enough space in the SQL Server TempDB.

• Use 64-bit SQL server environment for better performance based on business requirement.

• Actual server configuration purely depends on business requirement, such as concurrent users and workflow instances.

• Every implementation should have proper database management monitored by a Database Professional.

• I/O statistics must be monitored to check if it affects the actual performance an application.

Conclusion

The following values are approximate values, and might vary based on the implementation:

• Average Growth of Database per instance is 118.1726 KB.

• Average Growth of Database per instance per Action is 1.641286 KB.

• Database size requirement per day for a workflow having 10 activities and 100 instance is 1.6412 * 10 * 100 = 1641.2 KB, that is, 1.627MB approximately.