CMS.exe spikes upto 1.9GB 4.0 SP5 Patch 5

Former Member · ‎11-19-2014

Hi ,

My BO Prod box CMS.exe spikes upto 1.9GB . Here's the environment information -

4.0 SP5 Patch 5

Tomcat

Distributed environment with 5 nodes -01,02,05,03,06 server

CMS exists on one node 01 server .

AD/Enterprise Authentication

Windows Server

2 core CPU , 16 GB RAM

CMS cannot be installed on other nodes except 01 as there are no dll related to it, it grayes out for the adding cms from ccm on other nodes.

Noticed this error messsage in CMS logs with HIGH tracing.

Error in CMS logs –

(..\ODBCStatement.cpp:199) ExecDirect: SQL: SELECT
LOCKTABLE.PARENTID,LOCKTABLE.CHILDID,0,LOCKTABLE.ISMEMBER FROM dbo.CMS_LOCKS7
LOCKTABLE WHERE LOCKTABLE.CHILDID IN (8835)

Found a KBA related to it -

1640918 - Frequent deadlocks in CMS system database on MS SQL server in SAP BI 4.0

Performed these steps-

Stopped and disabled Platfrom search server.

Stopped and disabled monitoring APS

Pinged the DB from 01 CMS server , ping output was normal.

Checked for Multiple NIC .

Restarting the box causes CMS.exe memory to go low but after two hours it again spkies and remains between 1.5 to 1.9 GB

No performance issues observered w.r.t to login , navigation, report viewing and scheduling.

Event viewer logs mentions CMS stopped unexpectedly error message.

Info received from DBA-

In a 50 sec trace, there were 2568 lines of sql executed against that database.not all these below were executed within the first 1 second of the trace….

eclare @p1 int

set @p1=4698

exec sp_prepare @p1 output,N'@P1
int,@P2 varbinary(32),@P3 varbinary(30),@P4 image,@P5 int,@P6 int',N'UPDATE
dbo.CMS_InfoObjects7 SET Version = @P1, LastModifyTime = @P2, CRC = @P3,
Properties = @P4 WHERE ObjectID = @P5 AND Version = @P6',1

select @p1

SELECT SI_CUID, ObjectID FROM
dbo.CMS_InfoObjects7 WHERE SI_CUID IN (CAST('Ab3pb48nDjNLvgdY8fKQKPc' AS
VARBINARY(56)))

SELECT SI_CUID, ObjectID FROM
dbo.CMS_InfoObjects7 WHERE SI_CUID IN (CAST('AbSujQ2d95FKsx9t_BFocA0' AS
VARBINARY(56)))

SELECT LOCKTABLE.PARENTID,LOCKTABLE.CHILDID,0,LOCKTABLE.ISMEMBER
FROM dbo.CMS_LOCKS7 LOCKTABLE WHERE LOCKTABLE.CHILDID IN (4929115)

declare @p1 int

set @p1=4534

exec sp_prepexec @p1

output,N'@P1 int,@P2 varbinary(32),@P3 int,@P4 int',N'UPDATE
dbo.CMS_InfoObjects7 SET Version = @P1, LastModifyTime = @P2 WHERE ObjectID =
@P3 AND Version =
@P4',8394,0x3230313420313120313220313720333420313320373434,4929115,8393

select @p1

SELECT SI_CUID, ObjectID FROM
dbo.CMS_InfoObjects7 WHERE SI_CUID IN (CAST('Ab3pb48nDjNLvgdY8fKQKPc' AS VARBINARY(56)))

BEGIN

DELETE FROM dbo.CMS_LOCKS7 WHERE
RELATIONSHIPID = 474 AND PARENTID = 4929113 AND CHILDID IN (4929115);

END;

SELECT
dbo.CMS_InfoObjects7.ObjectID FROM dbo.CMS_InfoObjects7 WHERE
(dbo.CMS_InfoObjects7.ParentID = 259265 AND dbo.CMS_InfoObjects7.SI_TABLE = 0
AND dbo.CMS_InfoObjects7.SI_HIDDEN_OBJECT = 0) ORDER BY
dbo.CMS_InfoObjects7.LastModifyTime DESC, dbo.CMS_InfoObjects7.ObjName ASC,
dbo.CMS_InfoObjects7.ObjectID ASC

SELECT
LOCKTABLE.PARENTID,LOCKTABLE.CHILDID,0,LOCKTABLE.ISMEMBER FROM dbo.CMS_LOCKS7
LOCKTABLE WHERE LOCKTABLE.CHILDID IN (711)

SELECT
dbo.CMS_InfoObjects7.ObjectID FROM dbo.CMS_InfoObjects7 WHERE TypeID = 16 AND
SI_PLUGIN_OBJECT = 0

SELECT
dbo.CMS_InfoObjects7.ObjectID FROM dbo.CMS_InfoObjects7 WITH ( NOLOCK ) WHERE
NextRunTime <= CAST('2014 11 12 17 34 14 524' AS VARBINARY(32)) AND
SI_RUNNABLE_OBJECT = 1 AND TypeID = 67 AND FLOOR(ObjectID / 2) -
(FLOOR(FLOOR(ObjectID / 2) / 2) * 2) = 1 ORDER BY NextRunTime ASC

SELECT RELTABLE.CHILDID,
RELTABLE.PARENTID FROM dbo.CMS_RELATIONS7 RELTABLE WHERE
RELTABLE.RELATIONSHIPID IN (490) AND RELTABLE.CHILDID IN (273063) ORDER BY
RELTABLE.ORDINAL

SELECT RELTABLE.CHILDID,
RELTABLE.PARENTID FROM dbo.CMS_RELATIONS7 RELTABLE WHERE
RELTABLE.RELATIONSHIPID IN (508) AND RELTABLE.CHILDID IN
(882,885,897,903,906,933,939,954) ORDER BY RELTABLE.ORDINAL

Deadlock error messages from DBA ---

2014-11-17 20:04:39.46
spid25s deadlock-list

2014-11-17 20:04:39.46
spid25s deadlock victim=process9a62988

2014-11-17 20:04:39.46
spid25s process-list

2014-11-17 20:04:39.46
spid25s process id=process9a62988
taskpriority=0 logused=0 waitresource=PAGE: 18:1:20467 waittime=1871 ownerId=2510651344
transactionname=SELECT lasttranstarted=2014-11-17T20:04:37.503 XDES=0x8041d970
lockMode=S schedulerid=10 kpid=7236 status=suspended spid=176 sbid=0 ecid=0
priority=0 trancount=0 lastbatchstarted=2014-11-17T20:04:37.503
lastbatchcompleted=2014-11-17T20:04:37.480 hostpid=3348 loginname=sbo_admin
isolationlevel=read committed (2) xactid=2510651344 currentdb=18
lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

2014-11-17 20:04:39.46
spid25s executionStack

2014-11-17 20:04:39.46
spid25s frame
procname=adhoc line=1
sqlhandle=0x020000001bc5251a8374ef75fafde4f9b6fe9bd58bbd067c

2014-11-17 20:04:39.46
spid25s (@1 tinyint,@2 tinyint)SELECT
[dbo].[CMS_InfoObjects7].[ObjectID] FROM [dbo].[CMS_InfoObjects7] WHERE [dbo].[CMS_InfoObjects7].[SI_TABLE]=@1
AND [dbo].[CMS_InfoObjects7].[SI_HIDDEN_OBJECT]=@2 ORDER BY
[dbo].[CMS_InfoObjects7].[ObjectID] ASC

2014-11-17 20:04:39.46
spid25s frame
procname=adhoc line=1 sqlhandle=0x020000007def67149b2391b4bfc23e63c7cd6b20f2276c65

2014-11-17 20:04:39.46
spid25s SELECT dbo.CMS_InfoObjects7.ObjectID FROM
dbo.CMS_InfoObjects7 WHERE (dbo.CMS_InfoObjects7.SI_TABLE = 2 AND
dbo.CMS_InfoObjects7.SI_HIDDEN_OBJECT = 0) ORDER BY
dbo.CMS_InfoObjects7.ObjectID ASC

2014-11-17 20:04:39.46
spid25s inputbuf

2014-11-17 20:04:39.46
spid25s SELECT dbo.CMS_InfoObjects7.ObjectID FROM
dbo.CMS_InfoObjects7 WHERE (dbo.CMS_InfoObjects7.SI_TABLE = 2 AND
dbo.CMS_InfoObjects7.SI_HIDDEN_OBJECT = 0) ORDER BY dbo.CMS_InfoObjects7.ObjectID
ASC

2014-11-17 20:04:39.46
spid25s process id=process6030988
taskpriority=0 logused=53184 waitresource=PAGE: 18:1:29574 waittime=1786
ownerId=2510651217 transactionname=implicit_transaction
lasttranstarted=2014-11-17T20:04:37.490 XDES=0x800f56f0 lockMode=IX
schedulerid=17 kpid=9788 status=suspended spid=114 sbid=0 ecid=0 priority=0
trancount=2 lastbatchstarted=2014-11-17T20:04:37.500
lastbatchcompleted=2014-11-17T20:04:37.500 hostpid=3348 loginname=sbo_admin
isolationlevel=read committed (2) xactid=2510651217 currentdb=18
lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058

2014-11-17 20:04:39.46
spid25s executionStack

2014-11-17 20:04:39.46
spid25s frame
procname=adhoc line=3 stmtstart=448 stmtend=2116 sqlhandle=0x02000000b7f9d1033657cd0b60cb5555e99f55e90444377c

2014-11-17 20:04:39.46
spid25s DELETE FROM dbo.CMS_InfoObjects7 WHERE ObjectID
= 4964665 AND Version = 1 OR ObjectID = 4964666 AND Version = 1 OR ObjectID =
4964693 AND Version = 1 OR ObjectID = 4966824 AND Version = 2 OR ObjectID =
4966825 AND Version = 1 OR ObjectID = 4966826 AND Version = 2 OR ObjectID =
4966827 AND Version = 1 OR ObjectID = 4966835 AND Version = 2 OR ObjectID =
4966836 AND Version = 1 OR ObjectID = 4966839 AND Version = 2 OR ObjectID =
4966840 AND Version = 1 OR ObjectID = 4966842 AND Version = 2 OR ObjectID =
4966843 AND Version = 1 OR ObjectID = 4966845 AND Version = 2 OR ObjectID =
4966846 AND Version = 1 OR ObjectID = 4966912 AND Version = 2 OR ObjectID =
4966913 AND Version = 1 OR ObjectID = 4966914 AND Version = 2 OR ObjectID =
4966915 AND Version = 1 OR ObjectID = 4966916 AND Version = 2 OR ObjectID =
4966917 AND Version = 1;

2014-11-17 20:04:39.46
spid25s inputbuf

2014-11-17 20:04:39.46
spid25s BEGIN

DELETE FROM dbo.CMS_Aliases7
WHERE ObjectID IN
(4964665,4964666,4964693,4966824,4966825,4966826,4966827,4966835,4966836,4966839,4966840,4966842,4966843,4966845,4966846,4966912,4966913,4966914,4966915,4966916,4966917);

DELETE FROM dbo.CMS_InfoObjects7
WHERE ObjectID = 4964665 AND Version = 1 OR ObjectID = 4964666 AND Version = 1
OR ObjectID = 4964693 AND Version = 1 OR ObjectID = 4966824 AND Version = 2 OR
ObjectID = 4966825 AND Version = 1 OR ObjectID = 4966826 AND Version = 2 OR
ObjectID = 4966827 AND Version = 1 OR ObjectID = 4966835 AND Version = 2 OR
ObjectID = 4966836 AND Version = 1 OR ObjectID = 4966839 AND Version = 2 OR
ObjectID = 4966840 AND Version = 1 OR ObjectID = 4966842 AND Version = 2 OR
ObjectID = 4966843 AND Version = 1 OR ObjectID = 4966845 AND Version = 2 OR
ObjectID = 4966846 AND Version = 1 OR ObjectID = 4966912 AND Version = 2 OR
ObjectID = 4966913 AND Version = 1 OR ObjectID = 4966914 AND Version = 2 OR
ObjectID = 4966915 AND Version = 1 OR ObjectID = 4966916 AND Version = 2
OR

2014-11-17 20:04:39.46
spid25s resource-list

2014-11-17 20:04:39.47
spid25s pagelock fileid=1
pageid=20467 dbid=18 objectname=sbo_prod_db.dbo.CMS_InfoObjects7
id=lock8d83c0580 mode=IX associatedObjectId=72057594050969600

2014-11-17 20:04:39.47 spid25s
owner-list

2014-11-17 20:04:39.47
spid25s owner
id=process6030988 mode=IX

2014-11-17 20:04:39.47
spid25s waiter-list

2014-11-17 20:04:39.47
spid25s waiter
id=process9a62988 mode=S requestType=wait

2014-11-17 20:04:39.47
spid25s pagelock fileid=1
pageid=29574 dbid=18 objectname=sbo_prod_db.dbo.CMS_InfoObjects7
id=lock8a63e7380 mode=SIU associatedObjectId=72057594050969600

2014-11-17 20:04:39.47
spid25s owner-list

2014-11-17 20:04:39.47
spid25s owner
id=process9a62988 mode=S

2014-11-17 20:04:39.47
spid25s waiter-list

2014-11-17 20:04:39.47
spid25s waiter
id=process6030988 mode=IX requestType=convert

Any steps or suggestion provided on this issue would be helpful over here .

Regards,

Ketan Vesvikar

denis_konovalov · ‎11-19-2014

having CMS at close to 2gb memory is not necessarily a cause for concern in itself.
Check how many total objects you have in CMS DB, and how many objects are now in CMS cache.
You see those in CMS server metrics in CMC.

TammyPowlas · ‎11-19-2014

Moved to the BI platform space

CMS.exe spikes upto 1.9GB 4.0 SP5 Patch 5

Accepted Solutions (0)

Answers (2)

Answers (2)

Re: Feature Request: Allow Process Intelligence Co...

Re: Table DDNTF is not active in the dictionary er...

CDS View Table functions Abap Class consume with p...

Re: Incorrect documentation regarding "Error Sanit...

Can CrestalReports.NET be developed even if the DB...