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 leastbut 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 DBany 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 worldit 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
Re: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED)
Thanks! Good article. I wasn't aware of the Select-Query-Hint: NOLOCK option for the ar.cfg. While I certainly don't doubt the power of Oracle, if Oracle doesn't lock the table or row during a query, you run the risk of uncommitted or stale being returned by the query...which is exactly the drawback of NOLOCK. So as I see it, switching to Oracle would not overcome that risk. So I'm considering trying the NOLOCK option, but the dangers of stale/dirty data being returned really worry me. Norm -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 leastbut 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 DBany 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 worldit 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
Re: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED)
Thanks for the reply. I'm familiar with the FTS option, but unfortunately it's not an option because of a) Cost b) The site is running 6.3 and cannot upgrade (I don't think FTS licenses are available for 6.3) and c) Cost! Good suggestion, anyway. -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of LJ Longwing Sent: Wednesday, December 03, 2008 8:31 PM To: arslist@ARSLIST.ORG Subject: 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 leastbut 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 DBany 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 worldit 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
Re: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED)
*** 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 leastbut 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 DBany 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 worldit 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
AW: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED)
Hi Norm, If you do a table scan on a huge table, then storage is the bottleneck. The data must be read from disk and that's slow. Kind Regards Conny -Ursprüngliche Nachricht- Von: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] Im Auftrag von Kaiser, Norm E CIV USAF AFMC 96 CS/SCCE Gesendet: Donnerstag, 4. Dezember 2008 15:17 An: arslist@ARSLIST.ORG Betreff: Re: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED) *** 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 leastbut 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 DBany 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 worldit 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
Re: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED)
Conny is right. Check the disk I/O stats on your DB server while you run the diary search. Then compare that to what Performance Monitor recommends you stay under. There are some very useful stats that PerfMon doesn't have, but you can derive those from other stats. But just start with basics like Avg Queue, Reads/Sec, and Writes/Sec. Be sure to factor in your RAID configuration and # of disks when comparing. The NOLOCK hint is going to drastically help you. We've used it for several years for the same reason you need to - too many concurrent users and too many inefficient queries. There is a chance for dirty reads, but the only thing that could be dirty is something that commits while your search is executing. That's not likely to be significant. We have seen one potential side effect to this option though. It can lead to a Sev. 22 error in SQL Server 2000 (not sure about later versions). When this happens, all queries will randomly return zero results. It starts out being maybe once every 20 queries, and after a while its like every other query comes back empty. The quick fix is to just cycle SQL Server as soon as the error occurs. Fortunately for us this only happened on our reporting server, which didn't have many concurrent users, so we just removed the NOLOCK tag from that server. I think someone mentioned this before, but if these users are doing QBE searches, you could limit those by adding active links that force them to include something in an indexed field if they search on the diary field. That could help also. Chad Hall (501) 342-2650 -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Martin, Conny Sent: Thursday, December 04, 2008 8:23 AM To: arslist@ARSLIST.ORG Subject: AW: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED) Hi Norm, If you do a table scan on a huge table, then storage is the bottleneck. The data must be read from disk and that's slow. Kind Regards Conny -Ursprüngliche Nachricht- Von: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] Im Auftrag von Kaiser, Norm E CIV USAF AFMC 96 CS/SCCE Gesendet: Donnerstag, 4. Dezember 2008 15:17 An: arslist@ARSLIST.ORG Betreff: Re: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED) *** 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
Re: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED)
LOL...I never said 'I' would choose that option...because of reasons A and C...but you are correct on B as well... -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: Thursday, December 04, 2008 7:03 AM To: arslist@ARSLIST.ORG Subject: Re: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED) Thanks for the reply. I'm familiar with the FTS option, but unfortunately it's not an option because of a) Cost b) The site is running 6.3 and cannot upgrade (I don't think FTS licenses are available for 6.3) and c) Cost! Good suggestion, anyway. -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of LJ Longwing Sent: Wednesday, December 03, 2008 8:31 PM To: arslist@ARSLIST.ORG Subject: 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 leastbut 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 DBany 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 worldit 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
Re: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED)
Norm, I'm very interested in this subject...please keep the list up to date if you try this and what you find. -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: Thursday, December 04, 2008 6:59 AM To: arslist@ARSLIST.ORG Subject: Re: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED) Thanks! Good article. I wasn't aware of the Select-Query-Hint: NOLOCK option for the ar.cfg. While I certainly don't doubt the power of Oracle, if Oracle doesn't lock the table or row during a query, you run the risk of uncommitted or stale being returned by the query...which is exactly the drawback of NOLOCK. So as I see it, switching to Oracle would not overcome that risk. So I'm considering trying the NOLOCK option, but the dangers of stale/dirty data being returned really worry me. Norm -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 leastbut 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 DBany 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 worldit 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
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
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
Re: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED)
Hey Norm...have your DBA (or you if you would like) turn on profiler on your SQL Server right before you issue your searchand wait for the search to finish (instead of restarting in the middle)...I recently had a problem where periodically my server would just hangtracked it down to our reporting team doing a select * on a table with about 80 columns and 350K rows...the search was taking over 4 min's to completeall searches/inserts/updates/anything on that table were queue'd up until that one query was done...took me weeks to track it down to thiswe ended up optimizing their query and the problem went away. This unfortunately isn't likely to do anything to solve your problem...but it will show you what's happening at a DB level. -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 1:18 PM 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
Re: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED)
Yes, it does look like the table gets locked during the query...but the question is, how do you prevent that? -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
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
Re: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED)
I noticed that you can turn off the escalation and set the tables to lock row. The problem appears that it might be an overall performance hit, according to Microsoft. You could run your ARServer on a Windows server with the DB on an alternative OS with a more robust DB Just a thought. BTW, I have little to no experience with Microsoft's SQL server, so I will have to defer to someone with more experience in that realm... At least you know what is going on. 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 15:14 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
Re: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED)
Well, Microsoft SQL is known as very robust DB. The issue is the query being passed to it. Unfortunately, because of the WUT's open querying system, the end user can define very poor queries and pass it to the underlying DB. -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Darrell Reading Sent: Wednesday, December 03, 2008 4:10 PM To: arslist@ARSLIST.ORG Subject: Re: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED) I noticed that you can turn off the escalation and set the tables to lock row. The problem appears that it might be an overall performance hit, according to Microsoft. You could run your ARServer on a Windows server with the DB on an alternative OS with a more robust DB Just a thought. BTW, I have little to no experience with Microsoft's SQL server, so I will have to defer to someone with more experience in that realm... At least you know what is going on. 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 15:14 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
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
Re: UPDATE: Intermittent, Spotty ARS Performance (UNCLASSIFIED)
I am sorry, been a Unix/Linux guy forever and on top of that exposed to mainly Informix and DB2. I have no point of reference on that end. I wonder if you could set the DB up for dirty reads by default, that might be the key there if you can't lock by 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 LJ Longwing Sent: Wednesday, December 03, 2008 16:43 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
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
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 leastbut 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 DBany 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 worldit 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
Re: Intermittent, Spotty ARS Performance (UNCLASSIFIED)
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
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
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
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
Re: Intermittent, Spotty ARS Performance (UNCLASSIFIED)
Maybe some archiving of older data might help. Ben Cantatore Remedy Manager (914) 457-6209 Emerging Health IT 3 Odell Plaza Yonkers, New York 10701 [EMAIL PROTECTED] 11/25/08 10:26 AM 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
Re: Intermittent, Spotty ARS Performance (UNCLASSIFIED)
Yeah, I've already considered that. I've already interviewed the people who are the culprits and gotten their story. They tell me they look for specific solutions that they typed in long ago because they can't remember how they fixed it. They just remember a few keywords. So they search against the keywords they remember to pull up the solution. The system in question has a separate Solution field where they are supposed to document the solution so they can avoid this problem, but they don't use it. So it's a case of the, You guys really should use the Solution field, and they're like, OK, we'll do that, and they never do. -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
Re: Intermittent, Spotty ARS Performance (UNCLASSIFIED) (U)
UNCLASSIFIED What about creating workflow to copy the final Work Log entry to the Solution Description field? Sandra Hennigan OSD Enterprise Remedy Administrator Office # 703-601-0789 Apparently, there is nothing that cannot happen today. Mark Twain -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 11:32 AM To: arslist@ARSLIST.ORG Subject: Re: Intermittent, Spotty ARS Performance (UNCLASSIFIED) Yeah, I've already considered that. I've already interviewed the people who are the culprits and gotten their story. They tell me they look for specific solutions that they typed in long ago because they can't remember how they fixed it. They just remember a few keywords. So they search against the keywords they remember to pull up the solution. The system in question has a separate Solution field where they are supposed to document the solution so they can avoid this problem, but they don't use it. So it's a case of the, You guys really should use the Solution field, and they're like, OK, we'll do that, and they never do. -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
Re: Intermittent, Spotty ARS Performance (UNCLASSIFIED)
Well...in that case I think locking down the diary and saying 'that's not where you should be searching, fill in the solution and you don't need to search in there'...and leave it that way... -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 9:32 AM To: arslist@ARSLIST.ORG Subject: Re: Intermittent, Spotty ARS Performance (UNCLASSIFIED) Yeah, I've already considered that. I've already interviewed the people who are the culprits and gotten their story. They tell me they look for specific solutions that they typed in long ago because they can't remember how they fixed it. They just remember a few keywords. So they search against the keywords they remember to pull up the solution. The system in question has a separate Solution field where they are supposed to document the solution so they can avoid this problem, but they don't use it. So it's a case of the, You guys really should use the Solution field, and they're like, OK, we'll do that, and they never do. -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
Re: Intermittent, Spotty ARS Performance (UNCLASSIFIED) (U)
Because the final work log entry is rarely the solution. -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Hennigan, Sandra H CTR OSD-CIO Sent: Tuesday, November 25, 2008 10:39 AM To: arslist@ARSLIST.ORG Subject: Re: Intermittent, Spotty ARS Performance (UNCLASSIFIED) (U) UNCLASSIFIED What about creating workflow to copy the final Work Log entry to the Solution Description field? Sandra Hennigan OSD Enterprise Remedy Administrator Office # 703-601-0789 Apparently, there is nothing that cannot happen today. Mark Twain -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 11:32 AM To: arslist@ARSLIST.ORG Subject: Re: Intermittent, Spotty ARS Performance (UNCLASSIFIED) Yeah, I've already considered that. I've already interviewed the people who are the culprits and gotten their story. They tell me they look for specific solutions that they typed in long ago because they can't remember how they fixed it. They just remember a few keywords. So they search against the keywords they remember to pull up the solution. The system in question has a separate Solution field where they are supposed to document the solution so they can avoid this problem, but they don't use it. So it's a case of the, You guys really should use the Solution field, and they're like, OK, we'll do that, and they never do. -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
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
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
Re: Intermittent, Spotty ARS Performance (UNCLASSIFIED)
I'm curious. What is your architecture again? How many CPU's do you have, what were your settings before/now? -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 4: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
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 AFMC 96 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___ Classification: UNCLASSIFIED Caveats: NONE ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
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 CTR USA 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 AFMC 96 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___ 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
Re: Searching Diaries WAS: Intermittent, Spotty ARS Performance (UNCLASSIFIED)
Classification: UNCLASSIFIED
Caveats: NONE
Norm,
To prevent certain Advanced Query searches, you can drop the Reserved
Field (1005) on the form and use and AL to inspect the value (On
Search). Throw an error if the value contains the diary field name
('DIARYNAME'). This will allow you to exclude certain fields from being
used and even prevent use of LIKE statements.
Christopher Michaud
Remedy System Administrator/Developer
US Army Medical Information Technology Center (USAMITC)
Core Technology Division - Systems Engineering Branch
Office: 210.295.3589
DSN: 421-3589
-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: Monday, November 24, 2008 2:49 PM
To: arslist@ARSLIST.ORG
Subject: Re: Searching Diaries WAS: Intermittent, Spotty ARS Performance
No...can't do that. Thanks for the suggestion, but that won't work.
Users still need to construct advanced searches. I just need to block
them from skirting my AL that blocks them from searching the worklog.
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Gayford, Matthew C.
Sent: Monday, November 24, 2008 2:47 PM
To: arslist@ARSLIST.ORG
Subject: Re: Searching Diaries WAS: Intermittent, Spotty ARS Performance
Just turn off the advanced search option from the Form - Current View -
Properties - Menu Access menu.
-Matt
Matthew C. Gayford
Application Developer Remedy Administrator
University of North Carolina Wilmington
(910) 962-7177
-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: Monday, November 24, 2008 3:39 PM
To: arslist@ARSLIST.ORG
Subject: Re: Searching Diaries WAS: Intermittent, Spotty ARS Performance
How do you block searches done on the Advanced Query Bar?
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Rootuja Ghatge
Sent: Monday, November 24, 2008 2:35 PM
To: arslist@ARSLIST.ORG
Subject: Re: Searching Diaries WAS: Intermittent, Spotty ARS Performance
We prevent our users from searching the work log on production server
via an active link firing on search.
It's a given performance killer. They have to use the reporting server
to search the work log.
HTH,
Rootuja
_
Rootuja Ghatge
Senior Application Developer
CenterBeam, Inc.
30 Rio Robles
San Jose, CA 95134
Direct (408) 750-0718
Fax (408) 750-0559
http://www.centerbeam.com
This email may contain confidential and privileged material for the sole
use of the intended recipient. Any review or distribution by others is
strictly prohibited. If you are not the intended recipient please
contact the sender and delete all copies.
-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: Monday, November 24, 2008 12:19 PM
To: arslist@ARSLIST.ORG
Subject: Searching Diaries WAS: Intermittent, Spotty ARS Performance
OK, I'm pretty confident the problem was being caused by users
constantly searching the worklog diary field on a form with 200,000+
tickets. I was able to reproduce the behavior multiple times by doing a
diary search myself.
So...that leads me to wonder, how do the rest of the ARSListers handle
diary searches?
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Michaud, Christopher W Mr CTR
USA 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 AFMC
96 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
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 ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
