We have Large Result Logging turned on. While it does show us some people
who could use some education it has never given us information (form/user)
regarding a query that hangs the system. For some reason these hanging
queries do not seem to be logged by this feature.
We use the below MS SQL query to find the offending process and query but
since it is at the SQL level it doesn't tell us who issued the query. I
would love to be able to correlated this with the thread log. These hangs
happens so rarely now it doesn't warrant keeping SQL logging on at all
times.
---------------------------------------------------------------------
DROP TABLE #Processes
-- Field ID 240000007 = Description and Field ID 240000008 = Worklog
SELECT
s.spid, BlockingSPID = s.blocked, DatabaseName =
DB_NAME(s.dbid),
s.program_name, s.loginame, ObjectName = OBJECT_NAME(objectid,
s.dbid), Definition = CAST(text AS VARCHAR(MAX))
INTO #Processes
FROM sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE
s.spid > 50;
WITH Blocking(SPID, BlockingSPID, BlockingStatement, RowNo, LevelRow)
AS
(
SELECT
s.SPID, s.BlockingSPID, s.Definition,
ROW_NUMBER() OVER(ORDER BY s.SPID),
0 AS LevelRow
FROM
#Processes s
JOIN #Processes s1 ON s.SPID = s1.BlockingSPID
WHERE
s.BlockingSPID = 0
UNION ALL
SELECT
r.SPID, r.BlockingSPID, r.Definition,
d.RowNo,
d.LevelRow + 1
FROM
#Processes r
JOIN Blocking d ON r.BlockingSPID = d.SPID
WHERE
r.BlockingSPID > 0
)
SELECT * FROM Blocking
ORDER BY RowNo, LevelRow
-- DROP TABLE #Processes
---------------------------------------------------------------------
Jason
On Tue, Sep 18, 2012 at 12:22 PM, ravi rai <[email protected]> wrote:
> **
> Joe,
> This feature is available
> Add following in Ar.cfg
>
> *Large-Result-Logging-Threshold: 70000
> *
> 70000 is the threshold limit
>
> enable thread log
>
>
> result will be like
> <THRD> <1 > /* Mon Jul 09 2012 16:05:38.2310 */ Thread Id 39696
> (GLEWF) large result buffer allocation - /Length: *840183*/Entries:
> 3000/Client Ver: >=10/RPC ID: 11393/User: <UserName>/Form: <Form name>/
>
> Ravi
>
> ------------------------------
> Date: Tue, 18 Sep 2012 14:05:57 -0400
> From: [email protected]
>
> Subject: Re: Querys that kill my system
> To: [email protected]
>
>
> **
>
> So this feature really isn't there? You nearly had me there!
>
> It’s a great feature if it was there though.. Maybe a perfect candidate
> for some sort of an enhancement request..
>
> Joe
>
> *From:* John Sundberg <[email protected]>
> *Sent:* Monday, September 17, 2012 10:04 PM
> *Newsgroups:* public.remedy.arsystem.general
> *To:* [email protected]
> *Subject:* Re: Querys that kill my system
>
> ** It is a little hard to say what "version" it was....
>
> Because in 2012 -- BMC renamed it all to BMC Atrium ... threw in the
> kitchen sink -- which included the Remedy product and the ITSM Suite + some
> discovery software + a CMDB...
>
> But in 2014 Oracle broke it all up -- and peeled off the ITSM software
> into something they called "Oracle Service Support" .... (And the concept
> of a development admin tool went away) -- it is purely configuration and
> consulting to configure the product...
> (Traditional Remedy customer are unhappy - new customers don't know better
> - think they are eating a juicy steak - turns out it is pink slime)
>
> RemedyForce was taken over by Business.com (aka Salesforce.com).
>
> RemedyOnDemand - still exists -- but is part of "Oracle Scalable
> Enterprise" - which everybody calls "Oh say ... can you see" -- because
> nobody can actually see it working... kind of a weird industry joke...
> (Still not sure about it -- I think 2021 humor goes over my head)
>
> Same timeframe -- Oracle renamed Remedy to "Oracle Enable" ... and it
> became really the "adhoc" business "back-office" apps generator....
>
> SRM was abandoned by Oracle -- really BMC killed it before Oracle got it
> (circa 2011) - BMC just never told the customers.
>
> Of course Kinetic Data stayed independent and front-ends both Oracle
> Service Support and the Enable framework - keeping customers happy -
> regardless of the shenanigans behind the scenes...
>
> BTW - the MN Vikings still are without a Superbowl in 2021 :(
>
> Obama gets reelected.
> Republican gets elected 2016 -- does a fair job - but chooses not to rerun.
> Hillary gets 2020.
>
> And that is all I know...
>
> -John
>
> On Mon, Sep 17, 2012 at 8:15 PM, Shellman, David <[email protected]>wrote:
>
> **
> John,
>
> Do you know what version this introduced?
>
> Dave
>
> On Sep 17, 2012, at 8:43 PM, "John Sundberg" <
> [email protected]> wrote:
>
> ** No - they won't work. :(
>
> Sorry...
>
> -John
>
>
> On Mon, Sep 17, 2012 at 6:05 PM, Chris Kelly <[email protected]> wrote:
>
> ** These are great suggestions But I an in 2012 and I need a solution
> today....
> As you can tell I know nothing about remedy. So will adding the two lines
> work?
> Any help is great, my future self says thanks also....
> Chris
> On Sep 17, 2012 4:26 PM, "Joe Martin D'Souza" <[email protected]> wrote:
>
> **
>
> Sweet... don’t think I knew this..
>
> New feature??
>
> Joe
>
> *From:* John Sundberg <[email protected]>
> *Sent:* Monday, September 17, 2012 4:56 PM
> *Newsgroups:* public.remedy.arsystem.general
> *To:* [email protected]
> *Subject:* Re: Querys that kill my system
>
> **
>
> You could go into ar.cfg
>
> add the 2 lines :
>
> ars_track_long_queries: true
> ars_track_long_queries_min_length: 5000 # This is milliseconds - so all
> greater than 5 seconds
>
>
> Then -- restart server
>
> Look for table called: "ARS_TRACK_QUERIES" (I think that is the name)…
>
> Look in there -- it will tell
> who
> when
> what
> how long it ran
> how many entries returned
> what ip address
> what client type and version
>
>
> Pretty sweet feature… (wish I would have thought of it originally)
>
>
> -John
>
>
>
>
> On Mon, Sep 17, 2012 at 10:21 AM, Chris Kelly <[email protected]> wrote:
>
> **
> Thanks so very much. I am not a Remedy Admin so My admin says the
> Unqualified searches is disabled.
>
> I will work on getting the fields indexed asap.
>
> The thing is we don't know who is running the queries... is there a way
> on the remedy system that we could look at to see who is running this and
> stop it when it is going on?
>
> Chris
>
> On Mon, Sep 17, 2012 at 9:09 AM, Maddala, Venkat
> <[email protected]>wrote:
>
> **
>
> Hi Chris,****
>
> Here are the few things that you can do ****
> **1) **Disable Unqualified searches on server****
> **2) **Index the most commonly used fields in search****
> **3) **Limit the free form searches by adding custom workflow****
>
> ****
>
> BTW is this for custom applications? Or OOTB?****
>
> ****
>
> HTH****
>
> Venkat Maddala ****
>
> http://RemediesForRemedy.com <http://remediesforremedy.com/>****
>
> ****
>
> ****
>
> ****
>
> *From:* Action Request System discussion list(ARSList) [mailto:
> [email protected]] *On Behalf Of *Chris Kelly
> *Sent:* Monday, September 17, 2012 10:51 AM
> *To:* [email protected]
> *Subject:* Querys that kill my system****
>
> ****
>
> ** ****
>
> Hello all.****
>
> ****
>
> I have a question for the group.****
>
> ****
>
> Our ARsystem 7.1 and what is going on is when someone runs a intensive
> query my system comes to a standstill. what are a few things i would do
> to stop this? or set it so if queries get approved before running????****
>
> ****
>
> ****
>
> Please help ...****
>
> ****
>
> Chris ****
>
> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ ****
> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_
>
>
> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_
>
>
>
>
> --
>
> *John Sundberg*
> Kinetic Data, Inc.
> "Your Business. Your Process."
> *WWRUG10 Best Customer Service/Support Award*
> *WWRUG09 Innovator of the Year Award*
> *
> *
> 651-556-0930 I [email protected]
> www.kineticdata.com I community.kineticdata.com
>
>
>
> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_
> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_
>
> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_
>
>
>
>
> --
>
> *John Sundberg*
> Kinetic Data, Inc.
> "Your Business. Your Process."
> *WWRUG10 Best Customer Service/Support Award*
> *WWRUG09 Innovator of the Year Award*
> *
> *
> 651-556-0930 I [email protected]
> www.kineticdata.com I community.kineticdata.com
>
>
>
> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_
>
> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_
>
>
>
>
> --
>
> *John Sundberg*
> Kinetic Data, Inc.
> "Your Business. Your Process."
> *WWRUG10 Best Customer Service/Support Award*
> *WWRUG09 Innovator of the Year Award*
> *
> *
> 651-556-0930 I [email protected]
> www.kineticdata.com I community.kineticdata.com
>
>
>
> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_
> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_
> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_
>
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
