Perform the following steps to ensure optimum performance on MSSQL database:
Procedure
While creating a database for POM schema, if you are using the MSSQL database, set the READ_COMMITTED_SNAPSHOT database parameter ON.
If you do not set the parameter to On, you might experience that campaigns are getting stuck and you might see the following error message in the logs files:
Exception occurred Module POMCM Method JobContactBO.java.updateActionState Exception Type org.hibernate.exception.LockAcquisitionException: could not execute update query, Please see POM log files for details
To verify that the READ_COMMITTED_SNAPSHOT parameter is ON on existing database, type the query SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= 'YourDatabase'.
The query returns one of the following:
1 = READ_COMMITTED_SNAPSHOT option is ON. Read operations under the read-committed isolation level are based on snapshot scans and do not acquire locks.
0 = READ_COMMITTED_SNAPSHOT option is OFF (default). Read operations under the read-committed isolation level use share locks.
You can change the parameter by typing ALTER DATABASE<database_name> SET_READ_COMMITTED_SNAPSHOT ON;
Note:
Before running this query make sure that you stop all the POM servers and also stop VPMS services. It is advised to run this SQL command immediately after creating the database or after running installDB.sh script.
If a database uses either the full, or bulk-logged recovery model, you must back up the transaction log regularly to protect your data and to prevent the transaction log from getting full. For more information on Recovery Model and transaction log management, refer Microsoft SQL server documentation or consult a qualified database administrator.
Perform the following steps to configure tempDB:
Set the tempDB to auto grow.
Ensure the disk has free space.
Set the initial size of tempDB to one third of the database size.
Put the tempDB on a separate disk.
Set the recovery model of the tempDB to SIMPLE. This model reclaims the log space.
Schedule a maintenance job on the database to rebuild indexes on the pim_job_contact_restrict table to run every hour.
Perform a periodic database maintenance to avoid database performance issues due to fragmentation of the table index.
For de-fragmenting indexes, do the following on the database index:
Get the list of fragmented indexes on each table.
If the percentage fragmentation is more than thirty, rebuild the index.
If the percentage fragmentation is more than ten and less than thirty, ensure that the database administrator reorganizes the index.
Note:
For a better performance of the database, ensure that the percent index fragmentation is less than ten.
