Hi, The Application Pending form is used to process "Application-Commands" and contains details about the command to execute. "Application-Command" entries can be generated from operations such as the Run Process action, Approvals, Configuration Updates, etc.
The records in this form generally trigger an external plug-in to start an operation that under normal server processing would slow down the system. There is an associated Application Dispatcher that handles the processing of these records and passes on the command for processing e.g. Approval commands are passed to the Approval Server. The Dispatcher also handles the cleanup of these records, so if there is an issue of commands not being processed correctly then this should be looked into as the only records should be actions waiting to be processed. _____ Kind Regards, Carl Wilson http://www.missingpiecessoftware.com/ From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Charlie Lotridge Sent: 22 January 2014 15:30 To: [email protected] Subject: Re: CPU utilization sometimes goes up to 100% ** Hi all, LJ, ARS absolutely WILL generate nvarchar strings (using the N prefix) on unicode servers, though I have no idea if this particular SQL is being generated by ARS or not. On my 8.1 dev box, Application Pending form has no defined indexes, so there's only the clustered index on the request id. This query doesn't hit the request id, so it's going to result in a full table scan which is as costly as it can get: each one of those queries will cause the DB to scan through every record in the table. If there are more than a few thousand records, I can see how it could start to use up the hours and CPU Jennifer has described. It's also going to severely impact all other usage of the DB. Although I know this form has been around for a while, I have no idea what it's for beyond the description in help text on the form itself. I don't work with ITSM (I've always done purely custom apps), but would guess it's involved with integrating ITSM with ancillary applications. However, the 'Status' field values suggest a lifecycle for entries that would cause an accumulation of entries over time. This is bad, since there doesn't seem to be any provision to delete old data (there's no Audit configuration to do this on the form). If ITSM (or whatever) is using this form frequently and creating many entries, then you're going to start having problems with any queries against this form pretty quickly. Jennifer, there are a number of things you can do. 1) Determine the source of that query if you can. It seems unlikely to me that it's being generated by any native ARS mechanism. I would guess that the queries generated by any native ARS mechanism (which would be looking to run a pending application, whatever that might be) would look more like WHERE C7 = 0 ('Status' = "Pending"). If the query is important for the business, at a minimum create an index on either of the 'Category' or 'Command' fields (it's hard to know which might be better individually without seeing the data), or possibly even both (a single index on both, not two indexes). This will likely very significantly improve the performance of those queries (from hours to seconds). 2) Determine if the historic data is useful. That it is being queried would suggest it is, but determine the business significance if any. If there is none, configure the audit mechanism to delete obsolete data using a qualification of, say, 'Status' >= "Deleted" AND 'Last Changed Time' > $DATE$ + (30 * 86400) [meaning, the entry was marked as Deleted or Cancelled more than 30 days ago]. PLEASE, someone who has more experience with this form chime in here - I have no idea if the lifecycle of entries can evolve after they're marked Deleted or Cancelled, but am assuming not. 3) If you do instead decide to keep the historic data, you might also then want to put an index on the Status field. Again, my guess is that whatever mechanism is responsible for executing the "pending application" entries in this form identifies them using a query that includes 'Status' = "Pending". Since there's no index on the 'Status' field, this will force a full table scan. If you do this, be sure that your DBA's are maintaining the statistics on the indexes (which they should be doing anyway), since this one could get very stale and cause bad DB optimizations. Anyway, I hope something here helps. Thanks, Charlie On Wed, Jan 22, 2014 at 6:07 AM, LJ LongWing <[email protected]> wrote: ** Brian, If you look into docs, the N being outside of the ' indicates that the string is going to be an nvarchar....one thing this indicates to me is that the query is NOT Remedy generated, but maybe a direct sql statement or something....but I'm not 100% sure. On Wed, Jan 22, 2014 at 6:48 AM, Brian Goralczyk <[email protected]> wrote: ** My first question is, Which system is having the cpu hit 100%? If it is the db then indexes should help. If it is the application server then you have either too much data being sent back or too much workflow firing on all the records. I would first check to see how many records you have in the table. Second I would look into the query. What is calling it, and why does it have C8 = N'BR-CHK-CONFIG' as part of the qualification. I am concerned initially about the N being outside the quotes. I would also run the query against the db directly and see how long that takes and how many rows it is returning. HTH, Brian Goralczyk On Wed, Jan 22, 2014 at 4:03 AM, Carl Wilson <[email protected]> wrote: ** Hi, I would verify the indexes that are present on Application Pending form to ensure that the field 'C501' has an index. Also, you may want to check the number of records in this form and delete entries that are not required (by default, processed records should be removed). _____ Kind Regards, Carl Wilson http://www.missingpiecessoftware.com/ From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Jennifer Varkey Sent: 22 January 2014 06:59 To: [email protected] Subject: Re: CPU utilization sometimes goes upto 100% ** sorry, i missed the server details; ARS/ITSM/RKM 7.5 on Windows 2k8 RAM is 12 GB. The databse is SQL 2k8 on another separate machine, having 12 GB RAM. On Wednesday, 22 January 2014, 12:24, Jennifer Varkey <[email protected]> wrote: Hello All, In my SQL logging each of the following statements are taking over 4 hours; SELECT T8.C1 FROM T8 WHERE ((T8.C8 = N'BR-CHK-CONFIG') AND (T8.C501 = N'SG-Check-Config')) ORDER BY C1 ASC SELECT T8.C1 FROM T8 WHERE ((T8.C8 = N'BR-CHK-CONFIG') AND (T8.C501 = N'SG-Check-Config')) ORDER BY C1 ASC SELECT T8.C1 FROM T8 WHERE ((T8.C8 = N'BR-CHK-CONFIG') AND (T8.C501 = N'SG-Check-Config')) ORDER BY C1 ASC SELECT T8.C1 FROM T8 WHERE ((T8.C8 = N'BR-CHK-CONFIG') AND (T8.C501 = N'SG-Check-Config')) ORDER BY C1 ASC During such time, the CPU utilization becomes very high, almost reaching 100%. The T8 table is "Application Pending". The co-rresponding API logging (against the form "Application Pending") does not show the same delay or lag. We ran the log files using the arloganalyzer. The field T8.C8 is "Category" field and the field T8.C501 is "Command" field. The category "BR-CHK-CONFIG" refers to BRIE (SLM). Could someone please help us out on why these are taking so much time and why the CPU is going so high? Thanks and Regards, Sonia. _ARSlist: "Where the Answers Are" and have been for 20 years_ _ARSlist: "Where the Answers Are" and have been for 20 years_ _ARSlist: "Where the Answers Are" and have been for 20 years_ _ARSlist: "Where the Answers Are" and have been for 20 years_ _ARSlist: "Where the Answers Are" and have been for 20 years_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
