*** ADDITIONAL THOUGHTS *** So I was thinking about this last night on the drive home. Here is what I believe is the anatomy of the problem:
1) The Remedy client allows end user to construct their own queries, including against diary fields 2) End users query diary fields 3) Because of the voluminous amount of data in the diary field and because searching a diary is effectively performing a LIKE against that voluminous amount of data, that puts a heavy query load on the underlying DB 4) The underlying DB locks the table during such a query to prevent itself from returning stale/uncommitted/dirty data 5) Because the table is locked, other users attempting to do a search or commit lock up until the table is unlocked Now the thing that makes me wonder is step 3. Most assuredly, performing a LIKE statement against a voluminous amount of data in a diary field is intensive. However, the CPU utilization during this operation remains under 10%. One would think that if the query is burdensome, the CPU would peg out at 100% to complete the request faster. Is it, perhaps, not the processor that's causing the query to return so slowly? Perhaps it's a memory issue? I'm going to monitor memory consumption by the SQL process to test this hypothesis. Thoughts? -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Martin, Conny Sent: Thursday, December 04, 2008 2:03 AM To: arslist@ARSLIST.ORG Subject: AW: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED) There is an option in ar.conf Select-Query-Hint: NOLOCK Documentation says: The text to be used in a query hint (in the WITH clause of a SELECT statement) when queries are supplied to SQL Server databases. This parameter works only on queries triggered by GLE, GLEWF, and GME API calls. If this configuration item is an empty string or is not present, no WITH clause is generated. Consult your SQL Server to determine the appropriateness of using this feature in your environment. The Select-Query-Hint option is commonly used with a NOLOCK setting for allowing queries to execute without being blocked by simultaneous updates, thereby improving performance. For example, to allow SQL Server to read data in the process of being updated and avoid blocking, specify: Select-Query-Hint: NOLOCK Here is a useful site explaining NOLOCK http://articles.techrepublic.com.com/5100-10878_11-6185492.html Using NOLOCK has some drawbacks. If you don't want these drawbacks use Oracle! Oracle never put's locks on tables/rows that are read by a select statement. Kind Regards Conny -----Ursprüngliche Nachricht----- Von: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] Im Auftrag von LJ Longwing Gesendet: Donnerstag, 4. Dezember 2008 03:31 An: arslist@ARSLIST.ORG Betreff: Re: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED) I completely agree that we are talking about a like statement on a wide table that won't use an index with over 500K records in it...this is a 'bad' thing to say the least....but according to my DBA, you can instruct SQL to not block other query/update/insert to the table while the inefficient query is being run...while this isn't ideal...it makes it so that it's not blocking. It's dirty, not because it's inefficient, but because it's possible for the data to change before it's all given to you...and I agree that this is likely to happen on any DB....any db that allows searching on the diary fields. One 'solution' that you could look into that others recommended earlier in the thread is to use the Full Text Search capability reintroduced in the 7.x world....it is a separate indexing service that indexes specific fields that you want to search on (diary fields especially) and allows for efficient searching of those records without hurting the DB. -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Kaiser, Norm E CIV USAF AFMC 96 CS/SCCE Sent: Wednesday, December 03, 2008 4:54 PM To: arslist@ARSLIST.ORG Subject: Re: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED) Well, that's called SQL injection, but that doesn't apply here, as Remedy guards against it through parameterized statements. In this case, the "dirty" query isn't really dirty, it's just inefficient. That is, searching a massive diary field against a half million records, which Remedy allows you to do out-of-the-box. I hope Doug is monitoring this thread, as I think what I'm describing could happen on ANY database. -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of LJ Longwing Sent: Wednesday, December 03, 2008 4:43 PM To: arslist@ARSLIST.ORG Subject: Re: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED) I'm told by my DBA that it's possible to write 'dirty' queries that won't lock the DB, but I haven't been able to figure out how to make Remedy run them...it's basically an appendage to the end of the sql statement. -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Kaiser, Norm E CIV USAF AFMC 96 CS/SCCE Sent: Wednesday, December 03, 2008 2:14 PM To: arslist@ARSLIST.ORG Subject: Re: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED) Well, it looks like I found it. Microsoft SQL handles lock escalation dynamically. The escalation can, well, escalate to a table lock. Here's Microsoft article on the issue: http://support.microsoft.com/kb/323630 I ran the Profiler, and sure enough, that's exactly what's happening! The fix? Write better, smaller queries. Damn. -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Darrell Reading Sent: Wednesday, December 03, 2008 2:30 PM To: arslist@ARSLIST.ORG Subject: Re: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED) Is there something going on with that table that locks it when someone searches? Maybe it is as simple as changing the lockmode from table to row? Darrell Reading Systems Engineer Phone 479.204.5739 [EMAIL PROTECTED] Wal-Mart Stores, Inc. 805 Moberly Lane, MS-0560-68 Bentonville, AR 72716 Save Money. Live Better -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Kaiser, Norm E CIV USAF AFMC 96 CS/SCCE Sent: Wednesday, December 03, 2008 14:18 To: arslist@ARSLIST.ORG Subject: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED) *** UPDATE *** Well, it appears I don't have to toilet paper my own house after all. As I reported below, I attempted to fix my problem with spotty, intermittent performance by increasing my fast and list threads from 5 to 30. That did nothing. I still have the same problem. The issue is most definitely related to diary searches, but I would *expect* that a diary search would not so drastically impact ALL users as it does. I can understand the person who invoked the diary search having a problem, but considering there are so many other threads available and CPU utilization remains under 10%, I would think other users would not be impacted so dramatically. But it never fails...I can reproduce the problem 100% of the time. I kick off a diary search, and everyone's client stops responding. It's almost like (I know this is dreaded and sometimes over-reported), but it's almost like a memory leak in the server app. Restarting the service, naturally, rectifies the situation and Remedy just hums along until someone else does a diary search. Now, I understand I can block diary searches. But my issue is wondering why diary searches impact EVERYONE. Ideas? Norm -----Original Message----- From: Kaiser, Norm E CIV USAF AFMC 96 CS/SCCE Sent: Tuesday, November 25, 2008 5:39 PM To: 'arslist@ARSLIST.ORG' Subject: RE: Intermittent, Spotty ARS Performance (UNCLASSIFIED) Well, a little bird (ahem...cough...Doug...cough) suggested I double up my list and fast threads, which I've done, and that seems -- at least on the surface -- to have corrected the problem. I did have multiple threads, but I guess just not enough. If that was the problem, I'm going to toilet paper my own house. Norm -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Mayfield, Andy L. Sent: Tuesday, November 25, 2008 5:02 PM To: arslist@ARSLIST.ORG Subject: Re: Intermittent, Spotty ARS Performance (UNCLASSIFIED) Never mind...... I guess I should read the entire thread before responding. Andy L. Mayfield Sr. System Operation Specialist Alabama Power Company Office: 205-226-1805 Cell: 205-288-9140 SoLinc: 10*19140 -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Mayfield, Andy L. Sent: Tuesday, November 25, 2008 4:17 PM To: arslist@ARSLIST.ORG Subject: Re: Intermittent, Spotty ARS Performance (UNCLASSIFIED) We had a similar problem recently. We found that an Active Link was causing our problem. It was a newly created Active Link that was somehow corrupted and caused the server to hang. It might be worth a look. Check to see if any objects have been created or modified recently. Good Luck. Andy L. Mayfield Sr. System Operation Specialist Alabama Power Company Office: 205-226-1805 Cell: 205-288-9140 SoLinc: 10*19140 -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Michaud, Christopher W Mr CTR USA MEDCOM USAMITC Sent: Tuesday, November 25, 2008 9:27 AM To: arslist@ARSLIST.ORG Subject: Re: Intermittent, Spotty ARS Performance (UNCLASSIFIED) Classification: UNCLASSIFIED Caveats: NONE Norm, You may want to investigate whether you can use BMC or SQL Full Text search options to improve the performance. Alternatively, I've found it helps to interview the culprits to understand how they are utilizing the system to do their job. Often you can add an indexed field that allows them to categorize/track what they are looking for on a repeat basis. Christopher Michaud -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Kaiser, Norm E CIV USAF AFMC 96 CS/SCCE Sent: Tuesday, November 25, 2008 8:25 AM To: arslist@ARSLIST.ORG Subject: Re: Intermittent, Spotty ARS Performance (UNCLASSIFIED) Good suggestion...I'm pretty familiar with the new worklog model in version 7 and its advantages and disadvantages. Unfortunately, that entails a very large coding effort, which I'm not able to do on this system. -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Benedetto Cantatore Sent: Tuesday, November 25, 2008 8:12 AM To: arslist@ARSLIST.ORG Subject: Re: Intermittent, Spotty ARS Performance (UNCLASSIFIED) ** Norm, Perhaps you need to steal an idea from version 7 and make the worklogs a parent-child relationship with the main form. This would accomodate the individuals that need to get to specific information in the worklog and ease up the burden on your database. If you can install version 7 on a server, you'll see how it works and adopt it. Ben Cantatore Remedy Manager (914) 457-6209 Emerging Health IT 3 Odell Plaza Yonkers, New York 10701 >>> [EMAIL PROTECTED] 11/25/08 8:56 AM >>> Yeah, I suspected the same thing going in, but free disk space is abundant. Only about 20% of the disk is used. I have concluded that the issue is the diary searches. I suspected that this was a problem about a month ago, so I created a form and a filter that would capture a record every time a user did a diary search. Sure enough, I discovered users were doing diary searches dozens of times per day. There are now over 500,000 tickets in this system, and each ticket contains diary entries of up to 30 pages (or more) in length. Users were repeatedly searching for things like, "The ticket was placed on hold because the customer is unavailable." To prove the theory, I had the administrator at the site repeatedly log on to her User client. That is, TOOL...LOGIN...TOOLS...LOGIN...TOOLS...LOGIN...etc. The User client would faithfully log her on to Remedy in under a second. I told her, "Keep doing it!" while I went to my client and issued a diary search. Bam! She could no longer log in. She got the dreaded, "Setting server port..." message that never went away. So I have locked down the diary field to prevent these searches, but I'm already hearing all sorts of dissent: "That puts us out of business! We HAVE to be able to search the worklog!" So now I'm considering other options. I suppose the only thing I can do is set up some type of archival system, but that comes with two problems: 1) Users will hate it and 2) It doesn't really solve the problem. Putting a voluminous amount of free text on another form and telling users, "Go search there," still puts a huge burden on the database to sift through all that garbage. Norm -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Joe DeSouza Sent: Monday, November 24, 2008 8:09 PM To: arslist@ARSLIST.ORG Subject: Re: Intermittent, Spotty ARS Performance (UNCLASSIFIED) ** Another thing could be your disk space getting full on the Remedy server. We had that issue recently when one of the operation some user would do would eventually timeout and would create a temp file on the servers Windows Temp directory that would grow and keep growing even if the user quit the user tool from the client. The disk would eventually be full and the AR Server would get extremely slow and eventually impossible to login. Bouoncing the Remedy Service would kill that temp file and release all the used space.. Joe ________________________________ From: "Kaiser, Norm E CIV USAF AFMC 96 CS/SCCE" <[EMAIL PROTECTED]> To: arslist@ARSLIST.ORG Sent: Monday, November 24, 2008 12:58:53 PM Subject: Re: Intermittent, Spotty ARS Performance (UNCLASSIFIED) Yes, that's my suspicion. I have a big suspicion that people are searching the worklog diary field. -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Michaud, Christopher W Mr CTRUSA MEDCOM USAMITC Sent: Monday, November 24, 2008 11:20 AM To: arslist@ARSLIST.ORG Subject: Re: Intermittent, Spotty ARS Performance (UNCLASSIFIED) Classification: UNCLASSIFIED Caveats: NONE Norm, You may want to look closer at the SQL side. Look for locks. Perhaps someone querying a diary or un-indexed field. Also, are you using SQL replication? In particular, are snapshots turned on? Christopher Michaud -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Kaiser, Norm E CIV USAF AFMC96 CS/SCCE Sent: Monday, November 24, 2008 11:03 AM To: arslist@ARSLIST.ORG Subject: Intermittent, Spotty ARS Performance ** Hi everyone: This problem has me perplexed. At a site I support, the Remedy server inexplicably stops responding to requests. It's very intermittent. It runs fine for awhile, then seemingly without warning, it just hangs. Users attempting to log on get stuck at the "Setting server port" dialog, which eventually times out. Other users who are already logged who try to pull up a ticket get stuck at a blank screen that never comes back. To resolve the issue, they have to bounce the Remedy server service. The system works for awhile...until it hangs up again. Any ideas what might be causing this? - I have monitored CPU utilization when this occurs, and the CPU hums along at about 3% - 5% utilization - Network utilization is flat-lined whenever this occurs (i.e., no spike) - Memory utilization appears normal - CNET bandwidth tests resolve to better than dedicated T1 performance (for what that's worth) Any thoughts are greatly appreciated. The interesting thing is, we have the same exact Remedy apps running on the same exact type of server in the same exact environment in four other locations, and those four other locations never experience any problems. Norm Remedy ARS 6.3 Microsoft SQL 2000 SP4 Microsoft Windows 2000 SP2 100% Custom Apps - No ITSM __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" Classification: UNCLASSIFIED Caveats: NONE ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" ----------------------------------------- ********************************************************************** This email and any files transmitted with it are confidential and intended solely for the individual or entity to whom they are addressed. If you have received this email in error destroy it immediately. ********************************************************************** Wal-Mart Confidential ********************************************************************** ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" ________________________________________________________________________ ____ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" ____________________________________________________________________________ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
