- The recommendation is to insure that any activities such as the OA Purge job, database backup or large intrusive queries against the cmscallhistory and hcmsagent tables only occur during the middle of the night and/or during non-production/low-activity times. Included are 2 links below regarding deadlock troubleshooting and best practices.
- Review of the cmscallhistory table size/row number indicates that it averages around 23GB with 104 million rows and a data retention period of 90 days. Please follow up with your report team to see if it is necessary to retain the data for 90 days or if 30 or 60 days worth of data is sufficient. If the retention period is changed in the OA Admin client recommend that you decrement the retention period only a couple days at a time.
- Lastly there are 7 indexes on the cmscallhistory table - 3 of those indexes appear to be the out of the box indexes and the other are custom indexes. If you also follow up with the report team as to whether or not these 4 indexes are needed.
- SQL deadlock information links:
http://www.sqlusa.com/bestpractices/deadlock/
http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/
