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
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
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
