Charlie, Thank for that piece, I've never used a Unicode Remedy install, so that would be why I haven't seen that nomenclature used by Remedy itself :)
On Wed, Jan 22, 2014 at 8:30 AM, Charlie Lotridge <[email protected]>wrote: > ** > 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"
