3198216 - CMC and BI Launchpad login page is not responding after entering valid credentials

When the below query is executed in query builder on a large CMS database (having significant data from previous years):
SELECT * FROM ci_infoobjects WHERE Descendants("si_name='Folder Hierarchy'","si_id in (<SI_ID #1>, <SI_ID #2>)") AND si_schedule_status=9

• subsequently entering credentials in CMC and BI Launchpad login page, it is observed that the page would not respond / lead the user to the the CMC and/or BI Launchpad home page (hung state with no error message)
• If logon is successful, end users observe slowness while performing workflows in CMC and/or BI Launchpad.
• Database logs reflect many locks in the CMS database which makes the platform inaccessible to users, and hangs many processes due to the locks generated in the database.
  
  
• Below query is observed at the database logs numerous times:
  SET NOCOUNT ON
  DELETE FROM #FRONTIER
  INSERT INTO #FRONTIER SELECT CMS_InfoObjects7.ObjectID, 1 FROM CMS_InfoObjects7 WHERE (CMS_InfoObjects7.ObjectID in (<SI_ID #1>, <SI_ID #2>))
  DECLARE @level int
  SELECT @level = 1
  WHILE @level > 0
  BEGIN
  IF EXISTS( SELECT * FROM #FRONTIER WHERE DEPTH = @level )
  BEGIN
  INSERT #FRONTIER
  SELECT DISTINCT RELATIONSHIP.CHILDID, @level + 1
  FROM #FRONTIER JOIN (SELECT 543 RELATIONSHIPID, ParentID PARENTID, ObjectID CHILDID, 0 ISMEMBER, 0 ORDINAL FROM CMS_InfoObjects7
  Executing cached statement: *ss0627340500_1007889725ss*
  Subordinate
  Subordinate INSERT #FRONTIER SELECT DISTINCT RELATIONSHIP.CHILDID, @level + 1 FROM #FRONTIER JOIN (SELECT 543 RELATIONSHIPID, ParentID PARENTID, ObjectID CHILDID, 0 ISMEMBER, 0 ORDINAL FROM CMS_InfoObjects7) RELATIONSHIP ON #FRONTIER.OBJECTID = RELATIONSHIP.PARENTID WHERE RELATIONSHIP.RELATIONSHIPID IN (543) AND #FRONTIER.DEPTH = @level AND RELATIONSHIP.CHILDID NOT IN ( SELECT #FRONTIER.OBJECTID FROM #FRONTIER WHERE DEPTH != 1)
  
  
• Below error is noticed in BO logs during the same timeframe:
  assert failure: (apsfactory.cpp:1618). (err.m_IDSError == IDS_ERR_CMSSYSTEMDB_NODATABASECONNECTIONSAVAILABLE : Error collecting resources: CMS system database is overloaded, please try again. If the problem persists, please contact the system administrator(FWB 00027) Writing to the database.

• SAP BusinessObjects BI Platform 4.x (4.1 / 4.2 / 4.3)

INSERT #FRONTIER SELECT DISTINCT RELATIONSHIP.CHILDID, @level + 1 FROM #FRONTIER JOIN (SELECT 577 RELATIONSHIPID, ParentID PARENTID, ObjectID CHILDID, 0 ISMEMBER, 0 ORDINAL FROM CMS_InfoObjects7) RELATIONSHIP ON #FRONTIER.OBJECTID = RELATIONSHIP.PARENTID WHERE RELATIONSHIP.RELATIONSHIPID IN (@@@V0_INT) AND #FRONTIER.DEPTH = @level AND RELATIONSHIP.CHILDID NOT IN ( SELECT #FRONTIER.OBJECTID FROM #FRONTIER WHERE DEPTH != @@@V1_INT)(@@@V0_INT INT, @level INT output, @@@V1_INT INT) CMS system database is overloaded recurring schedules instances SELECT DISTINCT RELATIONSHIP.CHILDID, @level + 1
FROM #FRONTIER JOIN (SELECT 577 RELATIONSHIPID, ParentID PARENTID, ObjectID CHILDID, 0 ISMEMBER, 0 ORDINAL FROM CMS_InfoObjects7) RELATIONSHIP ON #FRONTIER.OBJECTID = RELATIONSHIP.PARENTID
WHERE RELATIONSHIP.RELATIONSHIPID IN (577) AND #FRONTIER.DEPTH = @level AND RELATIONSHIP.CHILDID NOT IN ( SELECT #FRONTIER.OBJECTID FROM #FRONTIER WHERE DEPTH != 1) , KBA , BI-BIP-SRV , CMS / Auditing issues (excl. 3rd Party Authentication) , BI-BIP-ADM , BI Servers, security, Crystal Reports in Launchpad , Problem