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

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

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

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

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