Summary
In Documentum Server, what are the general best practices guidelines for performance tuning of SQL Server databases?
This issue was reported in (but may not be limited to):
Documentum Server 7.0, 7.1, 7.2, 7.3, 16.4
Resolution
Configurations on the SQL Server side
Here are the recommended SQL Server configurations, according to our general performance best practices guidelines:
- Max Degree of Parallelism (MAXDOP) = 1
- Parameterization = Forced
- Allow Snapshot Isolation (ALLOW_SNAPSHOT_ISOLATION) = True (ON)
- Is Read Committed Snapshot On (READ_COMMITTED_SNAPSHOT) = True (ON)
- Auto Create Statistics (AUTO_CREATE_STATISTICS) = False (OFF)
- Auto Update Statistics (AUTO_UPDATE_STATISTICS) = True (ON) *
- Auto Update Statistics Asynchronously (AUTO_UPDATE_STATISTICS_ASYNC) = True (ON) *
- Maximum server memory (in MB) = set a limit to avoid exceeding the amount of free RAM physical memory available on the machine
- ALLOW_PAGE_LOCKS = OFF for all custom indices
* Set to False (OFF) and manually update statistics if excessive recompilation is observed on the server.
Additionally, check the Power Options settings in the Control Panel of the Windows machine hosting the SQL Server database.
The default power plan setting of “Balanced” is not recommended for SQL Server because of performance issues.
Instead, set the power plan option to “High performance”.
Configurations on the Documentum Server side
On the Documentum Server side, make sure the following ongoing maintenance Documentum Jobs are active and scheduled to run on a regular basis in Documentum Administrator (DA > Administration > Job Management > Jobs node):
- dm_QueueMgt
- dm_AuditMgt
- dm_LogPurge
- dm_DmClean
- dm_DmFileScan
- dm_UpdateStats (with option -dbreindex FIX)
These Jobs are responsible for cleaning up old data from the Repository, in order to keep a good overall performance over time.
The first execution of these Jobs (mainly dm_AuditMgt) could take really long, so running them over the weekend is highly recommended.
Even if they time out after running for 24 hours, you can run them again later, and they should continue from the point where they stopped.
Running these Jobs after a long period of inactivity will cause table fragmentation, so you are recommended to run the following commands on SQL Server to reorganize all indices and update all statistics once those Jobs are done:
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"
EXEC sp_MSforeachtable 'Update Statistics? WITH FULLSCAN'
Performance degradation related to Permission Sets
If slowness is not affecting Superusers, this could be related to the permission-check conditions being added to the SQL query’s predicate (WHERE clause), mainly if you have users who are members of more than 250 groups.
The following Operating System environment variables can be set on the Content Server machine in situations like that:
- DM_GROUP_LIST_LIMIT: should be higher than the number of groups the affected user belongs to (default = 250)
- DM_GROUP_LIST_LIMIT_TEMP_TBL=T (default = F)
- DM_LEFT_OUTER_JOIN_FOR_ACL=T (default = F)
The following DQL query should help determining how many groups each user belongs to on your environment, listing the top 10 users with the highest number of groups and roles:
DQL> select u.user_name, count(g.r_object_id) from dm_user u, dm_group g where any g.i_all_users_names = u.user_name group by u.user_name order by 2 desc enable (return_top 10)
Additionally, you should check if “Mandatory Access Control Lists” (MACL) are in use in your Repository.
Removing the MACL checks will simplify the SQL statement and could reduce the load on SQL Server quite a bit.
The results of the following DQL queries will confirm if MACLs are in use in your environment:
DQL> select i_has_access_restrictions, count(*) from dm_acl group by i_has_access_restrictions DQL> select i_has_required_groups, count(*) from dm_acl group by i_has_required_groups DQL> select i_has_required_group_set, count(*) from dm_acl group by i_has_required_group_set
The results will show how many ACLs use MACL controls (0 is off, 1 is on).
If you do not have any ACLs using MACL (that is, if all 3 queries above only return 1 row each, where the first column always shows value “0”), then you can disable MACL with the following API commands (either from IAPI on the Content Server machine or from API Tester in DA > Tools menu):
API> retrieve,c,dm_docbase_config
API> set,c,l,macl_security_disabled
SET> T ===> T to turn off, F to turn on
API> save,c,l
API> reinit,c