Adobe Connect Database Server Best Practices for Performance Optimization

Tuning up Database Activities in a Licensed Adobe Connect Server

Below are the Best Practices for Tuning up the performance in an On Premise Connect Server

1. Below entries in Cutom.ini to support any query delays

DB_URL_CONNECTION_RETRY_COUNT=15
DB_URL_CONNECTION_RETRY_DELAY=30

2. Use of Covered Index.

A covered index is an index that can satisfy a query just by its index keys without having the need to touch the data pages. This is one of the fastest methods to retrieve data in SQL Server.

Tuning Connect database using Covered Index

To harden the Connect database to facilitate robust support of a high-volume, on-premise Connect clustered deployment, employ a covered index. As of the writing of this article, Connect version 9.0.4 has been released. With 9.0.4 and prior versions of Connect going back to version 7.5, it is prudent to use this index. The index is on ACL_ID and QUOTA_ID. These are the 2 conditions in the queries. The index covers all of the columns returned by the SELECT queries. By adding this covering index, Connect now chooses to do an index seek on the new index rather than the index scan on the primary key. Because it is a covering index, SQL server has all the data it needs in the index, and does not need to go back to the table to get more columns. This results in much less IO and effectively eliminates ACL_QUOTAS deadlock issues that are otherwise prone to appear under heavy load.

CREATE NONCLUSTERED INDEX [cs_custom_cover_quotas] ON [dbo].[PPS_ACL_QUOTAS]
(
[QUOTA_ID] ASC,
[ACL_ID] ASC
)
INCLUDE ( [DATE_BEGIN],
[DATE_END],
[email_notif],
[LIMIT],
[login_notif],
[monthly_emails],
[SOFT_LIMIT],
[THRESHOLD_ABSOLUTE],
[threshold_pct],
[USED])

Note: Be sure to drop this index prior to running any upgrades or updates to Connect as it may be problematic with any updater that touches the Connect database.
For versions after 9.0.4, please check with Adobe to employing this as it may already be added to Connect via the updated installer.

3. Recommended Practices to be taken care while setting up

• Place the OS, data, and log directories on separate disk drives for improved performance
• Temp DB should also be on a separate disk drive.
• SQL data on striped disks can be a major tuning benefit.
• Reindex and Update Statistics regularly.
• Defragment OS data and log files regularly
• Ensure minimal latency between Connect and SQL Server
• The more RAM the better. Everything works much faster in memory. The more of the DB you can keep in memory the better.
• Never virtualize the DB server
• Priority that you should keep these things on separate disks. Top = most important is OS then Connect, SQL & Data, Log & TempDB
• Breaking tempdb into multiple files will be helpful as Microsoft recommends that number of CPU’s –1 = number of tempdb files.

• Follow the Best Practices of MS SQL SERVER