Finding frequent small updates

ASE 15.5

I recently found a simple update that was happening so
frequently that replication could not keep up with it
(filling queues). I only found it due to the rep server
filling up. It was a simple update but it happens so often
it is disrupting replication and placing overhead on the
server.

How do I find other such updates? Since it was an update the
table is not growing much, it is not a long running tran,
and it does not stay connected for long. I have hundreds of
connections coming from many apps and I would like to
determine if any other apps are causing such issues, this
one was a status update with frequency set too high in the
app. It was always updating the same record (changing the
date/time/counter).

Thx,
rick_806
0
rick_806
12/21/2010 2:02:19 PM
sybase.ase.administration 7058 articles. 2 followers. Follow

4 Replies
554 Views

Similar Articles

[PageSpeed] 42

As a starting point, check monOpenObjectActivity
for tables that had a large number of updates, a large
number of operations, and a ratio of updates/operations
relatively close to 1.

-bret


On 12/21/2010 7:02 AM, rick_806 wrote:
> ASE 15.5
>
> I recently found a simple update that was happening so
> frequently that replication could not keep up with it
> (filling queues). I only found it due to the rep server
> filling up. It was a simple update but it happens so often
> it is disrupting replication and placing overhead on the
> server.
>
> How do I find other such updates? Since it was an update the
> table is not growing much, it is not a long running tran,
> and it does not stay connected for long. I have hundreds of
> connections coming from many apps and I would like to
> determine if any other apps are causing such issues, this
> one was a status update with frequency set too high in the
> app. It was always updating the same record (changing the
> date/time/counter).
>
> Thx,
> rick_806

0
Bret
12/22/2010 12:16:25 AM
> ASE 15.5
>
> I recently found a simple update that was happening so
> frequently that replication could not keep up with it
> (filling queues). I only found it due to the rep server
> filling up. It was a simple update but it happens so often
> it is disrupting replication and placing overhead on the
> server.
>
> How do I find other such updates? Since it was an update
> the table is not growing much, it is not a long running
> tran, and it does not stay connected for long. I have
> hundreds of connections coming from many apps and I would
> like to determine if any other apps are causing such
> issues, this one was a status update with frequency set
> too high in the app. It was always updating the same
> record (changing the date/time/counter).
>
> Thx,
> rick_806

one of the crude way would be to take snapshot of
monSysSQLText, it should list the *multiple* same update
commands (i recall if this is a single batch 'aka go
context' running in loop with same spid then u will see 1
entry, else you would see this in monSysSQLText). I had
found similar stuff with a PB app when we had network issue
between specific time frames - PB app was flooding updates
and inserts into application audit log even when the
application was idle (just launched and no activity).
0
jobless
12/22/2010 4:28:38 PM
> ASE 15.5
>>
>> I recently found a simple update that was happening so
>> frequently that replication could not keep up with it
>> (filling queues). I only found it due to the rep server
>> filling up. It was a simple update but it happens so often
>> it is disrupting replication and placing overhead on the
>> server.
>>
>> How do I find other such updates? Since it was an update
>> the table is not growing much, it is not a long running
>> tran, and it does not stay connected for long. I have
>> hundreds of connections coming from many apps and I would
>> like to determine if any other apps are causing such
>> issues, this one was a status update with frequency set
>> too high in the app. It was always updating the same
>> record (changing the date/time/counter).
>>

few thoughts..

how about using repserver to do what you want ?

-When your repserver queue grows, you can  suspend repserver DSI temporarily 
and dump repserver queue to find out if there are many update statements for 
any single table
alternatively,  you can log transaction in repserver exception log using 
sysadmin log_first_tran to find out the transaction

- If you find that repserver can not keep up with data which is coming in 
from of such small updates you can try using dynamic sql with repserver 
which will eliminate overhead of preparing statement at replicate side each 
time it is executed. You can use dymainc sql with repserver 15.x


-HTH
Manish Negandhi
[TeamSybase]


0
Manish
12/24/2010 7:56:12 AM
If one of the following applies in your case ...

a - update statement is inside a stored proc
b - update statement is part of a prepared statement
c - statement cache/literal autoparam are enabled

.... you should be able to use the MDA tables to track down high volume 'duplicate' queries.

Each of these scenarios force the dataserver to populate the ProcedureID column in the monSysStatement table.  For 
scenarios 'b' and 'c' the ProcedureID relates to the lightweight procedure (think 'temporary proc') created by the 
dataserver.

You'll need to setup a process to constantly sample the monSysSQLText, monSysStatement, and monCachedStatement tables 
(and perhaps monCachedProcedures).  Periodic snapshots of monProcessLookup should also be performed.

You'll need to adjust the size of the monSysSQLText and monSysStatement tables, as well as your sampling interval, to 
insure you capture most/all of the data flowing through these tables.

A periodic aggregate of monSysStatement records [select ProcedureID, count(*) where ProcedureID != 0] should show you 
the queries (at a proc level) that are being executed a high volume of times.

For scenario 'a', use object_name(ProcedureID,DBID) to get the proc name, then LineNumber to find the query within the proc.

For scenario 'c', DBID should be the dbid of a temporary database, and a join of ProcedureID with 
monCachedProcedures.ObjectID should show an ObjectName like 'ss<ObjectID>_%ss' (this is the temp proc's name).  Using 
the ProcedureID column, the associated monSysStatement.SPID/KPID/BatchID values can be joined to 
monSysSQLText.SPID/KPID/BatchID to find the associated SQL text.

For scenario 'b' it may not be possible to find the actual SQL text.  [Prepared statements are handled differently by 
the dataserver and typically don't show up in the MDA tables.  For this scenario you may be forced to use something like 
a SQL sniffer - RIBO? - to capture the SQL text.  Alternatively, capture the SQL text after the fact ... once it's into 
a Repserver queue, or after it's hit the MDA tables in the RDS - assuming of course that your DSI is not using prepared 
statements.]

For scenario 'c' (and perhaps 'b', too?), you can replace the aggregate query against monSysStatement with a simple 
query of the monCachedStatement.UseCount column.  UseCount represents the number of times the proc (ie, prepared/cached 
statement) was executed.  The SSQLID/DBID columns can be joined with monSysStatement.ProcedureID/DBID, which in turn can 
be used to join SPID/KPID/BatchID with monSysSQLText to find the actual SQL text (though probably not for scenario 'b' 
at this point).

Obviously (?) at any point along the way you can join a SPID/KPID value with the samplings from monProcessLookup to 
obtain details on the client/application that generated the SQL text.

[If you've gotten this far and the above comments are somewhat confusing - and I could've inadvertently mis-typed 
something - fear not ... if you get on a quiet dataserver (so that your MDA pipe tables aren't constantly filling up), 
run some test queries, and take periodic samplings of the aforementioned MDA tables, it shouldn't be too hard to see how 
the pieces fit together.]

-----------

If the above scenarios don't apply in your case (eg, statement cache/literal autoparam are disabled and queries are 
submitted via dynamic SQL), you'll probably need to look at some sort of brute force search of monSysSQLText to find 
duplicate/look-alike queries.  Alternatively, consider the following ...

-----------

Because of the limits of capturing prepared statement SQL text in the PDS, and the fact that heavy usage of the MDA pipe 
tables may cause unwanted performance degradation in your PDS, you may want to concentrate your monitoring efforts in 
the RDS.

Assuming the DSI is not using prepared statements, and that you've got statement cache/literal autoparam enabled in the 
RDS ...

You should be able to use monCachedStatement.UseCount (or counts of monSysStatement.ProcedureID) to find high volume 
query invocations, then use monSysStatement.SPID/KPID/BatchID to join with monSysSQLText to find the SQL text you're 
looking for.

The downside to doing this monitoring on the RDS is that you don't have any way (from the RDS) to track the query(s) 
back to the source user/application ... but you could use the RDS SQL query text findings to configure a more 
specific/targeted monitoring of the PDS.monSysSQLText table, which in turn could be joined with PDS.monProcessLookup (by 
SPID/KPID) to gather client/application details.

-----------

Off on a tangent re: replication queues filling up ... and keeping in mind that you haven't provided any details about 
your environment so some (all?) of the following may be old news ...

If your RDS is an ASE 15.x dataserver, have you a) enabled statement cache and literal autoparam and b) sized statement 
cache appropriately?

Because the ASE 15.x optimizer is a good bit slower than the ASE 12.x optimizer, it's not uncommon to see replication 
throughput hit by a 3x to 10x performance degradation due to a ASE 15.x RDS being too slow at compiling the huge volume 
of SQL statements submitted by a DSI connection.  The degraded compilation phase (in the RDS) can, in turn, cause the 
repserver queues to backup/fill-up.

The quick-fix solution which usually helps considerably is to enable statement cache and literal autoparam on the RDS. 
While enabling these options in an RDS is usually a no-brainer, these options can cause performance issues with 
non-repserver applications that submit SQL text directly into the RDS ... in which case the DBA may want to look at 
methods for selectively enabling/disabling statement cache for various connections into the RDS ... ymmv.




rick_806 wrote:
> ASE 15.5
> 
> I recently found a simple update that was happening so
> frequently that replication could not keep up with it
> (filling queues). I only found it due to the rep server
> filling up. It was a simple update but it happens so often
> it is disrupting replication and placing overhead on the
> server.
> 
> How do I find other such updates? Since it was an update the
> table is not growing much, it is not a long running tran,
> and it does not stay connected for long. I have hundreds of
> connections coming from many apps and I would like to
> determine if any other apps are causing such issues, this
> one was a status update with frequency set too high in the
> app. It was always updating the same record (changing the
> date/time/counter).
> 
> Thx,
> rick_806
0
Mark
12/24/2010 4:18:46 PM
Reply:

Similar Artilces:

updates, updates, updates
Name: hans jensen Email: hansjensen44_athotmaildotcom Product: Firefox Summary: updates, updates, updates Comments: why is it everytime i go online firefox has new updates, 3 -4 times every day, its never ending..........whats all the problems??? please email me and let me know, ty,.........Hans Jensen Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.2) Gecko/20070219 Firefox/2.0.0.2 ...

updates, updates, updates
Holy Crap, Batman! You'd think I was running Windows XP and it's patch Tuesday. So far this week - new kernel (2.6.27.10), 2 new browsers (Opera 9.63 and Firefox 3.05) and a new plugin (Flash 10.0.156.3). I'm about ready to try one od the BSD's! vince wrote: > Holy Crap, Batman! You'd think I was running Windows XP and it's patch > Tuesday. > > So far this week - new kernel (2.6.27.10), 2 new browsers (Opera 9.63 > and Firefox 3.05) and a new plugin (Flash 10.0.156.3). I'm about ready > to try one od the BSD's! I take ...

Where do i find out what update manager updated?
I just allowed update manager to install something but the update manager doesn't tell you whats actually tell you what is being downloaded-just that it needs the root password to do something. -- accessdeniedno ------------------------------------------------------------------------ /var/log/zypp file there 'history' -- Box: openSUSE 11.2 | (KDE4.4.4) | AMD 64 X2 5200+ | nVidia 8500GT | 4GB RAM | M2N4-SLI Lap #1: openSUSE 11.2 | Pentium Duo T4300 | KDE 4.4.4 | Intel M4 Graphics | Lenovo G550 | 3GB RAM Lap#2: 11.3RC1 KDE4.4.3 | Celeron 550 | Intel...

Updates, updates and more updates
Name: pirhan Email: pirhan_chanathotmaildotcom Product: Firefox Summary: Updates, updates and more updates Comments: Hello, Normally I use Safari, but my iBook is out of commission for a while. So, I am forced to use a PC to which I would much rather use Firefox than Internet Exploder. However, as much I enjoy not using a Microsoft product, and use a product that works decently, I am severely annoyed about the constant updates I am put through. In the last two days, I had to wait for Firefox to install updates before I could browse and now, I'm alerted that there'...

updates, updates, updates, NO MORE!
Name: Andreas Email: unbekanntatjokerdotms Product: Firefox Summary: updates, updates, updates, NO MORE! Comments: Hi ! if you want to develop a fine webbrowser, maybe you nee a new management. To release a new buggy version every two weeks is not very professional. Test your software before the release and let the user download a stable browser. this update-orgy since 2.0.0 used me to use Opera instead of FF. We haved use FF in our Company, but we can't deploy a new version every week to +200 Windows-clients, so in our company we've decided to return to IE, ...

Updates Updates Updates
I was a big fan of Firefox.. Now, I don't even like to use it. Seems like every time I start it up I have to update some addin OR the browser itself. And unfortunately the update nag messages are always presented when I start a browsing session. I find this annoying because, typically, when I open a web browser all I want to do is go to a web page. Not update the browser. I can agree that staying current with the latest version could give the best, fastest, safest web experience. But that point becomes mute when I start off every other browsing session with a new update. And mos...

How To Find Updation MADE Or NOt In Sql Update ?
Hi friends i am new to VB.NET I have some problem in sql update, i am using two tables to update   FOR EXAMPLE   UPDATE    AAA  SET AMT = 1000 UPDATE  BBB    SET AMT = 1000 MY PROBLEM IS WHEN THE TABLE AAA SUCCESSFULLY UPDATE THEN ONLY I UPDATE TABLE BBB IF TABLE AAA NOT UPDATED THEN I DO NOT UPDATE TABLE BBB HOW CAN I DO THIS    PLS SOLVE THIS     Hi, you can use transactions for this. Take a look at http://msdn.microsoft.com/en-us/library/ms188929.aspx for example or take a look at http://sea...

Difference between ASE and ASE Small Business
Can anyone tell me the general differences between ASE and ASE Small Business? I'm assuming there are some functionality differences, but have been unable to locate that information (a comparison list, etc.) on sybase.com. There are really 3 versions: ASE Enterprise, SBE, and Developer. A Developer license allows 25 users as of 12.5.2 ESD1. From 12.5.1 through 12.5.2 GA, the limit was 5. Only 1 CPU is permitted, which effectively means no parallelism. However, almost all of the licensed features are enabled. Only EFTS and DBXRAY are not. An SBE license allows 256 users and 4...

Finding column updated in Update Event Trigger
Hi All, Can I find out which column is updated in Update event (not in Update column event) for a trigger in table? ASA 9.0.2 (2451) Yes you can IF UPDATING( <column_name> ) works in WSQL. A similar TSQL compatible UPDATE( ) function exists too, and there is also historical support for a UPDATE() function for use with WSQL . SEE: ASA SQL Reference , SQL Language Elements , Search conditions, "trigger-operation:" and: ASA SQL Reference , SQL Language Elements , Search conditions , "Trigger operation...

Updates updates updates #2
Name: Product: Firefox Summary: Updates updates updates Comments: I love Firefox, but it seems EVERY day there is some sort of updates to download before it will open. You need to figure out a way Firefox can open when asked to do so and update in the background and apply the updates on the next restart. Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.3) Gecko/20090824 Firefox/3.5.3 (.NET CLR 3.5.30729) From URL: http://hendrix.mozilla.org/ Note to readers: Hendrix gives no expectation of a response to this feedback but if you wish to provide on...

Update Update
Name: John Oswell Email: serviceatwinntecdotcom Product: Firefox Summary: Update Update - endless updates Comments: I think it's great you update the product but I'm starting to think this product is more prone to attack and instability by the number of updates i get inflicted with. Quite honestly on the Windows platform you're going to need to convince me that the Firefox browser is "better" than IE8 - my default browser (ff right now) I'm probably going to revert - endless intrusive updates for Firefox is getting very tiring. At least MS (I'm ...

updates,updates, updates,etc.
Name: Product: Firefox Summary: updates,updates, updates,etc. Comments: I am so fed up with all these gd updates I COULD SCREAM! Why can't you get it right and stay with it for a while. Between you guys and MSFT you drive me nuts. Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.1) Gecko/2008070208 Firefox/3.0.1 From URL: http://hendrix.mozilla.org/ Note to readers: Hendrix gives no expectation of a response to this feedback but if you wish to provide one you must BCC (not CC) the sender for them to see it. ...

Updates Updates Updates #3
Name: Product: Firefox Summary: Updates Updates Updates Comments: Seems like every time I open the program there is a wait period while some new arcane update gets installed. I have enjoyed and advocated firefox for a good while, but the update thing is really starting to bug me. Doubtless I could simply change the settings to accept the updates or not notify me or something similar - but I CAN'T BE ASSED. I'd have to consider this for more than 30 seconds and as we've already seen I CAN'T BE ASSED. Please make this less annoying in return for which I promis...

Your frequent updates
Name: Susan King Email: pkings2001atyahoodotcom Product: Firefox Summary: Your frequent updates Comments: While I realize you are frequently updating Firefox for security and other reasons, it has become so disruptive and inconvenient that I rarely use it any more. This is particularly true at work when I have to immediately research something and don't have the time to wait for the latest Firefox upgrade. This is not a wise business procedure. I don't have this problem with Internet Explorer. Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; ...

Web resources about - Finding frequent small updates - sybase.ase.administration

NEXUS (frequent traveler program) - Wikipedia, the free encyclopedia
NEXUS (formerly frequent traveler program and currently part of Trusted Traveler Program) is a joint Canada – United States program designed ...

Easy Learning Russian - Translate & Learn - 60+ Languages, Quiz, frequent words lists, vocabulary on ...
Get Easy Learning Russian - Translate & Learn - 60+ Languages, Quiz, frequent words lists, vocabulary on the App Store. See screenshots and ratings, ...

Tablets are the frequent flyer's tool of choice
The laptop's days as the flyer's weapon of choice look just about over.

Qantas to shelve frequent flyer float
National carrier set to officially rule out partial float of Loyalty division: report.

Most ACT Assembly members not declaring or using frequent flyer points
Most Assembly members are not declaring frequent flyer points and fewer still are using the points to offset the cost of flying

Optus To Outline Frequent Flyer Plans Tomorrow
Optus promised back in May that it would let its customers earn points for Qantas Frequent Flyer through their phone expenditure; those plans ...

Frequent flyer points: The best and worst reward cards
REWARDS card users could be on a road to nowhere, according to the latest analysis of credit card loyalty programs by comparison website mozo.com.au. ...

Ambos get frequent callers to hang up
The ambulance service is targeting ''repeat callers'' after fewer than 500 people managed to call more than 10,000 ambulances in one year, at ...

Woolies junks frequent flyer points for discount 'dollars' in new scheme
Discount dollars are up for grabs instead of freebie flights after a major overhaul of Woolworths costly customer loyalty scheme

Qantas frequent flyer points on offer for staying active
Qantas has unveiled plans to target 2 to 3 per cent of the $19 billion health insurance market with a nib product that rewards members for being ...

Resources last updated: 12/16/2015 4:40:57 AM