CCF databases: tables ApplicationState, Interaction and stored procedure UpdateHostedApplication

CCF 2005 has two databases ContactCenter and ContacCenterAIF. CCF 2008 has one database named CCFInfrastructure. But both CCF versions contain tables ApplicationState, Interaction. Tables ApplicationState and Interaction does have update and delete cascade constraints on table Actions which in turn has update and delete cascade constraints on Application table. You can observe consequences of this when trying to update Hosted Application configuration through Admin Console. You may receive the following error “Unable to update requested information”. This error rises when AdminConsole web service has timed out. And the reason of this time out is long execution of stored procedure UpdateHostedApplication.

If you look at execution plan of procedure you will notice that query’s “delete from dbo.Actions where applicationid=@applicationid” cost is very high relative to the other queries. This is because of foreign key constraints and lack of indexes in ApplicationState and Interaction tables. To resolve this issue recommended regularly purge threes tables or create indexes and disable writing in this tables by commented insert statements in stored procedures AddInteraction and AddApplicationState. You can also run truncate operation on these tables.

CCF 2009 have only table ApplicationState which doesn't have any delete or update constraints. Hence this situation not occurs on this version.

PS. Don’t forget to monitor health of you system including database on regular basis J