transactions across databases and ulc

Hypothetical Question for ASE 12.5.x and 15.0.2:
If I am in database "A" and execute a proc in database "B" that does an
insert in table "Bt1" in database "B", does that cause the ULC to flush
because I crossed databases?

Use A
go
exec B..

The reason behind my question is based on the new feature of a second ulc
for tempdb.

--
Cory Sane
[TeamSybase]
Certified Sybase Associate DBA for ASE 15.0

> On 2008-08-27 03:38:28 +1000, "Cory Sane" said:
>
> If I am in database "A" and execute a proc in database "B" that does an
> insert in table "Bt1" in database "B", does that cause the ULC to flush
> because I crossed databases?

Only if you have an open tran and continue it in the next Database.

> The reason behind my question is based on the new feature of a second
> ulc for tempdb.

This was a well-known problem; that each spid has only one ULC; that
the ULC is tied to one Database; that switching dbs or executing updates in a
second Database causes the ULC to be flushed each time the Database context is
[implicitly] changed. Updating a Database plus tempdb was common, and caused
constant switching and thus ULC flushes.

> In 15.0.2 the ULC has been "split" into a user-Database-ULC and a separate
> tempdb-ULC. Â*This parm identifies the size of the latter. Â*It should be
> set to the same size as whatever you get when you run sp_logiosize on
> that tempdb, which for 15.x now defaults to 4k (hopefully in future
> versions the default setting for this parm will be 4k and thus this
> mannual step can be avoided). Â*It is a per-connection or per-session
> unit, the memory required = connections x STLCS, just as memory reqd
> for ULC is connections x ULC size. Â*(15.x is MUCH faster, but nothing
> is free, and it needs more memory to provide that speed.)
> It is one piece of a whole set of optimisations for all I/O, and in
> 
particular, for tempdb. Â*
On the one hand, the "session tempdb log cache" is a great improvement;
you now have two ULCs per session. One the other hand, if the coder is
not conscious of the effect of their code, it may produce a few
surprises.

P&T is an iterative process. Each time an obstacle/bottleneck is
identified, and overcome, the next obstacle/bottleneck becomes
immediately visible. While the second ULC stops flushing due to
switching between user Database and tempdb, for apps that have not heard of
transactions; chained apps; etc. now there is a "new" visible problem:
the user Database ULC keeps growing without getting flushed ! Whereas, with
one ULC, ASE kept flushing it and no-one noticed the effect of poor
code.

Of course STLC does not help you if you are switching between two user dbs.
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2008 Software Gems Pty Ltd
Quality Standards = Zero Maintenance + Zero Surprises
Performance Standards = Predictability + Scaleability

I found that I did not have this problem. I may need to envoke ALS.

--
Cory Sane
[TeamSybase]
Certified Sybase Associate DBA for ASE 15.0
"Cory Sane" wrote in message news:48b43f94$1-at-forums-1-dub...
> Hypothetical Question for ASE 12.5.x and 15.0.2:
> If I am in database "A" and execute a proc in database "B" that does an
> insert in table "Bt1" in database "B", does that cause the ULC to flush
> because I crossed databases?
>
> Use A
> go
> exec B..
>
> The reason behind my question is based on the new feature of a second ulc
> for tempdb.
>
> --
> Cory Sane
> [TeamSybase]
> Certified Sybase Associate DBA for ASE 15.0

On 2008-08-27 10:51:42 +1000, "Cory Sane" said:

> I found that I did not have this problem. I may need to envoke ALS.

I suggest you read up on it. Basically, it is for high end SMP
environments only, where you have a large no of CPUs, and you can
afford to lose/dedicate one to ALS. sp_sysmon stats will indicate
whether you will benefit from ALS or not. And usually you can get more
by tuning resources (caches, pools, etc) than by reducing the cpu count
by one. Obviously not worth implementing ALS until you have at least:
• dedicated log cache correctly sized
• 4k logiosize

Finally I have seen (two assignments) that even in the high-end SMP
servers, they did not get much perf improvement by implementing ALS
because they had poor transaction design, and dynamic SQL, chained,
etc. What I am saying is, if you are trying to overcome the
consequences of poor code, ALS will not help (it definitely improves
perf for tight transactions/code).
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2008 Software Gems Pty Ltd
Quality Standards = Zero Maintenance + Zero Surprises
Performance Standards = Predictability + Scaleability