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 <lj.longw...@gmail.com> 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 <
> bgoralczyk.w...@gmail.com> 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 <carlbwil...@gmail.com>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:
>>> arslist@ARSLIST.ORG] *On Behalf Of *Jennifer Varkey
>>> *Sent:* 22 January 2014 06:59
>>> *To:* arslist@ARSLIST.ORG
>>> *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 <
>>> sonia_m...@yahoo.co.uk> 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_
>
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
"Where the Answers Are, and have been for 20 years"
