Terry, that's kind of disturbing, though it is probably functioning by flawed design.
Just a thought - the Maximum Filters settings - are they high enough? I've seen that lock/crash a server before. Rick -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Terry Bootsma Sent: Monday, June 18, 2007 11:43 AM To: arslist@ARSLIST.ORG Subject: CMDB Performance Issue - Heads Up out there * Confirmed by Remedy as RFE SW00269736 * Environment: ITSM 6 , Asset management 6.0 patch 1470, with CMDB 1.1 Patch 004 on Remedy ARSystem 6.0.3 patch 020 and SQL Server 2000. CMDB Design is fundamentally the same for ITSM 7.X, thus would occur in this environment as well. Disclaimer: This performance issue is related to VIEWING the CMDB information via the Remedy User Tool. It does not occur when populating the CMDB. This situation occurs with a large amount of data populated in the CMDB. The situation is as follows. I have several Applications (AST:Application) that are related to several computers (AST:ComputerSystem) through the dependency relationship (BMC:BMC_Dependency). For one application, there are several dependency relationships that relate the Application to 3000+ computer systems (NOTE: I'm only creating one instance for an application and relating that application to all computers that are running the application. I'm updating license counts accordingly). If you're asking how I'm relating 3000+ computers to one application (that's a lot of mouse clicks!!), I'm keeping this information up-2-date via a custom interface to SMS through EIE. The situation is this, when a user (in the user tool) views this particular application and goes to the related items tab to display the dependencies, the user tool hangs while it tries to show the dependent computers, and the Remedy ARSystem hangs while the system tries to update the table OSRelationship_tbl. I'm forced to stop/restart the Remedy system in order to re-establish processing. If I view other applications that have 150 ->300 computers related to an application, the table does get refreshed, but it does take time (10 -> 30 seconds). The threshold seems to be around the 400 to 500 relationship mark. After a lot of investigation, I've come up with the following: The OSRelationship_tbl on the Related Items tab(Note:This table is the same table that is used on other AST forms that have relationships) on AST:Application is refreshing itself from the view AST_SearchFromBaseDependency which references AST_SearchFromBaseDependency1. AST_SearchFromBaseDependency1 is a join between BMC:BMC_Dependency and BMC:BMC_AssetBase. The problem is that the JOIN criteria on AST_SearchFromBaseDependency1 is an OR relationship. This results in a cartesian product between BMC:BMC_Dependency and BMC:BMC_AssetBase when queries are performed against the table in SQL Server 2000 (I suspect it would be the same in Oracle or any other DB). Because the join relationship on AST_SearchFromBaseDependency1 is an OR, a cartesian product is being generated and no utilization of any index would solve this performance issue. Performance is not an issue when an object only has a few dependency relationships or if BMC:BMC_AssetBase is fairly small, but when the opposite is true, it causes the application to hang (I've turned on SQL logging and the same is true if the SQL is issued directly to the database via SQL Advantage. The SQL will take 5+ minutes to run). For the instance I am talking about, I have 20,000 objects in BMC:BMC_AssetBase and 10,480 items in BMC:BMC_Dependency. The application that I am viewing has 3700 items in BMC:BMC_Dependency (ie. 3700 computers are dependent upon this application (are running it). I checked out the ITSM 7.X design, and the design is still, fundamentally the same, with the AST:SearchFromBaseRelationship being an OR join between BMC:CORE:BMC_BaseRelationship and BMC:CORE:BMC:BaseElement. This problem will also occur in this version as well. Has anyone else out there experienced this issue ? I've been forced to write code to check on the number of dependencies that an object has, and if over the 400 mark, to hide the related items tab and pop up an error message so that we don't hang the server. Any thoughts? Terry ____________________________________________________________________________ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
