Matt,
I can tell you from experience that NOLOCK does work with AR 5.1.2 and SQL
server 2000. We had a similar problem with Remedy locking up from time to
time and we implemented the NOLOCK feature. Once the change was implemented
we noticed NOLOCK in the SQL logs for Remedy.
SQL Server Profiler didn't help us much in diagnosing our problem. We
eventually found it (with some help by Remedy and the 3rd party vendor) by
going through the Remedy SQL and API logs. It may help to search your
Remedy SQL logs for any "DEADLOCK" errors.
As it turned out in our case the 3rd item Chad suggested resolved our
problem. Another application was submitting so many entries resulting in a
table lock on arshcema.
Shawn
Shawn Rosenberry RSP
Senior Application Developer (SAIC Contractor)
Center For Information Technology
National Institutes of Health
-----Original Message-----
From: Watson, Matthew (Melbourne) [mailto:mwatson2@KPMG.COM.AU]
Sent: Monday, October 11, 2004 6:36 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: [ARSLIST] Table Locking Unable to Determine Cause -
Suggestions?
Hi listers,
Does the NOLOCK ar.cfg feature work from version 5 and up? I asked our
support vendor for some more information on this configuration setting,
and the answer I got back was: "Not an option since AR 5.0. The API call
cannot be controlled by external hints anymore, only by the arserver
process".
Yet I'm seeing that other listers are reporting they are using this
feature in v5, so I'm not sure who's right here.
Regards,
Matt
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Chad Hall
Sent: Tuesday, 12 October 2004 12:06 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: Table Locking Unable to Determine Cause - Suggestions?
Here are a few things you can look at that worked for us:
1) Increase Fast and List Threads (it seems like you have plenty of
resources left to handle these).
2) Implement the NOLOCK ar.cfg tag (Select-Query-Hint: NOLOCK) so that
queries will not lock your tables - this is HUGE for heavily used tables
with hundreds of thousands or even millions of records.
3) Make the clustered index on your arschema table be for the schemaid
field instead of the name field (this reduces some contention that can
occur when lots of records are submitted simultaneously causing it to
repeatedly keep the NextID up to date)
These changes worked wonders for us. We had a helpdesk form with over 1
million records that was heavily used. SQL logging and SQL Profiler
showed us which tables were being hit during the lockups, and a
combination of all of these changes fixed our issues. We are on 5.1.2,
Windows 2003, SQL Server.
Chad Hall
Acxiom Corporation
On Thu, 7 Oct 2004 15:03:14 -0400, Lori Conrad
wrote:
>We are running into a situation where several times a week users are
>faced with an hour glass for anywhere from 1 - 3 minutes and are unable
>to work withing Remedy.
>
>I have not been able to determine the cause of the issue but suspect it
>is an external application that is running against the SQL server.
>
>By the time I get to the server there is no information of any value...
>ie table/record locks.
>
>We are running some reporting on the Remedy and SQL servers and I do
>not see any significant increase in memory or CPU usage that would
>cause the delay.
>
>We are thinking of starting to run SQL Server Profiler on the server
>however I'm concerned about putting the additional load on the server
>thus halting production.
>
>Does anyone have any other suggestions that I might try as to how I can
>find the cuase of this issue?
>
>Thanks in advance
>
>
>
>UNSUBSCRIBE or access ARSList Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org ) ARSList is hosted by QMX SUPPORT
SERVICES at www.QMXS.com
UNSUBSCRIBE or access ARSList Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org ) ARSList is hosted by QMX SUPPORT
SERVICES at www.QMXS.com
**********************************************************************
This email is intended only for the use of the individual or entity
named above and may contain information that is confidential and
privileged. If you are not the intended recipient, you are hereby notified
that any dissemination, distribution or copying of this Email is strictly
prohibited. When addressed to our clients, any opinions or advice contained
in this Email are subject to the terms and conditions expressed in the
governing KPMG client engagement letter. If you have received this Email in
error, please notify us immediately by return email or telephone +61 2
93357000 and destroy the original message. Thank You.
**********************************************************************M
UNSUBSCRIBE or access ARSList Archives at http://www.ARSLIST.org (Support:
mailto:support@arslist.org ) ARSList is hosted by QMX SUPPORT SERVICES at
www.QMXS.com
Previous Topic
Index