Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

15 performance

13 posts in General Discussion Last posting was on 2009-11-04 11:26:31.0Z
jbuhl Posted on 2009-10-27 21:00:51.0Z
Sender: 69f0.4ae7262c.1804289383@sybase.com
From: jbuhl
Newsgroups: sybase.public.ase.general
Subject: 15 performance
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ae75f83.7469.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 27 Oct 2009 13:00:51 -0800
X-Trace: forums-1-dub 1256677251 10.22.241.41 (27 Oct 2009 13:00:51 -0800)
X-Original-Trace: 27 Oct 2009 13:00:51 -0800, 10.22.241.41
Lines: 45
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28556
Article PK: 77797

We are attempting to upgrade our 12.5.4 servers to 15.0.3.
ESD#2

Our development/qa servers are Solaris 240s, solaris 10 and
very similar configurations and performance.

I just did an in-place install of 15 and brought up the
server without issue in our development environment.

post upgrade task that were completed:
reorg rebuild all primary tables.
update index statistics on all tables.
Activated statement cache and sized.
increased tempdb
set devices to direct i/o
after initial tests ran sp_monitorconfig and verified no
reuse.

The reports from my development team are that almost all
application operations are slower to much slower. I don't
think I have heard anyone say anything has improved. One
particular application, our Informatica ETL tool is
experiencing major distress with multiple queries creating
execution plans that increase query times 10 fold
(unacceptable).

In the face of unpredictable behavior I have retreated from
abstract query plans as a solution to specific query
problems.

With the shear volume of slowness I am obviously going to
have to first concentrate on server level resolution.

We are now testing compatibility mode and initial reports
are that the small amount of queries that were unacceptably
slow are now working but slower than 12.5 across the board.

I have also tested optimization goal settings with no
dramatic affect.

Hopefully I am overlooking something very obvious.

Any trouble shooting suggestions are welcome.

JB


"Mark A. Parsons" <iron_horse Posted on 2009-10-27 21:32:23.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: 15 performance
References: <4ae75f83.7469.1681692777@sybase.com>
In-Reply-To: <4ae75f83.7469.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 091014-0, 10/14/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ae766e7@forums-1-dub>
Date: 27 Oct 2009 13:32:23 -0800
X-Trace: forums-1-dub 1256679143 10.22.241.152 (27 Oct 2009 13:32:23 -0800)
X-Original-Trace: 27 Oct 2009 13:32:23 -0800, vip152.sybase.com
Lines: 96
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28557
Article PK: 77799

Generally speaking ... do some research on other posts (eg, sybase.public.ase.performance+tuning, ISUG forums) and
Sybase webcasts/documents that address ASE 15 upgrade performance issues and recommendations.

I've helped upgrade 100+ dataservers to ASE 15.x; here's a smattering of comments from my past posts re: ASE 15
upgrade/performance issues:

- non-ANSI standard SQL typically runs worse with ASE 15; solution is to rewrite code to be (more) ANSI-compliant ...
this typically also improves performance on ASE 12.5.x servers; ASE 15's optimizer is less-forgiving of poorly written SQL

- the ASE 15 optimizer is typically much slower than the previous optimizers; this becomes quite apparent for processes
that make heavy use of individual SQL statements (to include repserver/DSI connections into RDBs); to improve
performance there are a handful of options ... use (dynamic/prepared) stored procs, use statement cache (+ literal
autoparam), redesign applications to use more set-based processing (as opposed to cursor-like processing)

- 'enable literal autoparam' must also be configured (to '1') in order for statement cache to be effective; use
sp_sysmon ("Statement Cache" section) to verify if statement cache is in use

- statement cache generates 'temporary' stored procs which in turn requires more procedure cache; us sp_sysmon to keep
any eye on proc cache thrashing; you may need/want to increase proc cache sizing

- statement cache (+ literal autoparam) can suffer from the same symptoms of 'bad' query plans as with stored procs;
this typically arises when SQL SARG values have wide ranging distribution stats which can lead to a single query being
optimized differently based on the SARG values ... net effect is that a re-used query plan (pulled from statement cache)
can lead to poorly performing queries; two possible solutions ... disable statement cache utilization (at the dataserver
or session level) or use different query structures for different SARG values (this requires the SQL developer *REALLY*
knows their data)

- I typically pick an optimization goal that works for 'most' queries and then add code modifications (at session or
query/AQP levels) to address individual queries that compile 'better' under other optimization goals; for a vast
majority of the systems I've helped upgrade we've stuck with allrows_oltp as the dataserver-level config setting, then
used session/query-level commands when we found that allrows_mixed or allrows_dss performed better

- compatibility mode (IMO) isn't worth the effort; you're *NOT* gaining access to the ASE 12.5.x optimizer but rather a
dumbed-down version of the ASE 15 optimizer; compatibility mode disables quite a few ASE 15 features that you may need
now or in the near future ... so at some point you'll need to incur the overhead of doing a *real* ASE 15 upgrade ...
might as well get the upgrade out of the way now (as opposed to putting yourself through 2 sets of upgrade hassles)

- minimize the desire to use new ASE 15 features until you've got the basic upgrade completed and stable; also consider
doing just ASE 15 upgrades *OR* RS 15 upgrades at a time ... don't complicate your life by trying to address ASE 15 and
RS 15 upgrade issues at the same time

- consider upgrading (and thoroughly testing) your development and/or test systems first; consider upgrading
less-important production systems first; consider upgrading PDSs in a replication environment (and leave the RDSs at ASE
12.5.x levels - for WS setups this makes it much easier to 'downgrade' to ASE 12.5 if you run into show-stopper issues
with ASE 15, ie, just perform a RS/WS 'switch active')

- consider bringing in an outside source that has experience with ASE 15 upgrades; said person should have a solid
background in P&T work since they'll need to be able to track down performance issues and then come up with resolutions
to said issues

jbuhl wrote:
> We are attempting to upgrade our 12.5.4 servers to 15.0.3.
> ESD#2
>
> Our development/qa servers are Solaris 240s, solaris 10 and
> very similar configurations and performance.
>
> I just did an in-place install of 15 and brought up the
> server without issue in our development environment.
>
> post upgrade task that were completed:
> reorg rebuild all primary tables.
> update index statistics on all tables.
> Activated statement cache and sized.
> increased tempdb
> set devices to direct i/o
> after initial tests ran sp_monitorconfig and verified no
> reuse.
>
> The reports from my development team are that almost all
> application operations are slower to much slower. I don't
> think I have heard anyone say anything has improved. One
> particular application, our Informatica ETL tool is
> experiencing major distress with multiple queries creating
> execution plans that increase query times 10 fold
> (unacceptable).
>
> In the face of unpredictable behavior I have retreated from
> abstract query plans as a solution to specific query
> problems.
>
> With the shear volume of slowness I am obviously going to
> have to first concentrate on server level resolution.
>
> We are now testing compatibility mode and initial reports
> are that the small amount of queries that were unacceptably
> slow are now working but slower than 12.5 across the board.
>
> I have also tested optimization goal settings with no
> dramatic affect.
>
> Hopefully I am overlooking something very obvious.
>
> Any trouble shooting suggestions are welcome.
>
> JB


<SY> Posted on 2009-10-28 21:27:23.0Z
From: <SY>
Newsgroups: sybase.public.ase.general
References: <4ae75f83.7469.1681692777@sybase.com> <4ae766e7@forums-1-dub>
In-Reply-To: <4ae766e7@forums-1-dub>
Subject: Re: 15 performance
Lines: 2
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 14.0.8089.726
X-MimeOLE: Produced By Microsoft MimeOLE V14.0.8089.726
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ae8b73b@forums-1-dub>
Date: 28 Oct 2009 13:27:23 -0800
X-Trace: forums-1-dub 1256765243 10.22.241.152 (28 Oct 2009 13:27:23 -0800)
X-Original-Trace: 28 Oct 2009 13:27:23 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28563
Article PK: 77805

Just to correct one of Mark's (very useful) points.
compatibility mode IS to all intents and purposes (bar a couple of
unavoidable costing conflicts) the 125x optimizer and indeed the 125x
execution engine.
It is not a dumbed-down version of the 15x optimizer.

I think this confusion may come from partial compatibility mode (aka
basic_optimization) which was implemented prior to compatibility mode.
This can show the words 'compatibility mode' in the diagnostic traces to
cause headaches all round.... :-|
Rob's whitepaper makes reference to partial compatibility which consists of
a 125x AP passed into the 15x optimizer.

"Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message
news:4ae766e7@forums-1-dub...
> Generally speaking ... do some research on other posts (eg,
> sybase.public.ase.performance+tuning, ISUG forums) and Sybase
> webcasts/documents that address ASE 15 upgrade performance issues and
> recommendations.
>
> I've helped upgrade 100+ dataservers to ASE 15.x; here's a smattering of
> comments from my past posts re: ASE 15 upgrade/performance issues:
>
> - non-ANSI standard SQL typically runs worse with ASE 15; solution is to
> rewrite code to be (more) ANSI-compliant ... this typically also improves
> performance on ASE 12.5.x servers; ASE 15's optimizer is less-forgiving of
> poorly written SQL
>
> - the ASE 15 optimizer is typically much slower than the previous
> optimizers; this becomes quite apparent for processes that make heavy use
> of individual SQL statements (to include repserver/DSI connections into
> RDBs); to improve performance there are a handful of options ... use
> (dynamic/prepared) stored procs, use statement cache (+ literal
> autoparam), redesign applications to use more set-based processing (as
> opposed to cursor-like processing)
>
> - 'enable literal autoparam' must also be configured (to '1') in order for
> statement cache to be effective; use sp_sysmon ("Statement Cache" section)
> to verify if statement cache is in use
>
> - statement cache generates 'temporary' stored procs which in turn
> requires more procedure cache; us sp_sysmon to keep any eye on proc cache
> thrashing; you may need/want to increase proc cache sizing
>
> - statement cache (+ literal autoparam) can suffer from the same symptoms
> of 'bad' query plans as with stored procs; this typically arises when SQL
> SARG values have wide ranging distribution stats which can lead to a
> single query being optimized differently based on the SARG values ... net
> effect is that a re-used query plan (pulled from statement cache) can lead
> to poorly performing queries; two possible solutions ... disable statement
> cache utilization (at the dataserver or session level) or use different
> query structures for different SARG values (this requires the SQL
> developer *REALLY* knows their data)
>
> - I typically pick an optimization goal that works for 'most' queries and
> then add code modifications (at session or query/AQP levels) to address
> individual queries that compile 'better' under other optimization goals;
> for a vast majority of the systems I've helped upgrade we've stuck with
> allrows_oltp as the dataserver-level config setting, then used
> session/query-level commands when we found that allrows_mixed or
> allrows_dss performed better
>
> - compatibility mode (IMO) isn't worth the effort; you're *NOT* gaining
> access to the ASE 12.5.x optimizer but rather a dumbed-down version of the
> ASE 15 optimizer; compatibility mode disables quite a few ASE 15 features
> that you may need now or in the near future ... so at some point you'll
> need to incur the overhead of doing a *real* ASE 15 upgrade ... might as
> well get the upgrade out of the way now (as opposed to putting yourself
> through 2 sets of upgrade hassles)
>
> - minimize the desire to use new ASE 15 features until you've got the
> basic upgrade completed and stable; also consider doing just ASE 15
> upgrades *OR* RS 15 upgrades at a time ... don't complicate your life by
> trying to address ASE 15 and RS 15 upgrade issues at the same time
>
> - consider upgrading (and thoroughly testing) your development and/or test
> systems first; consider upgrading less-important production systems first;
> consider upgrading PDSs in a replication environment (and leave the RDSs
> at ASE 12.5.x levels - for WS setups this makes it much easier to
> 'downgrade' to ASE 12.5 if you run into show-stopper issues with ASE 15,
> ie, just perform a RS/WS 'switch active')
>
> - consider bringing in an outside source that has experience with ASE 15
> upgrades; said person should have a solid background in P&T work since
> they'll need to be able to track down performance issues and then come up
> with resolutions to said issues
>
> jbuhl wrote:
>> We are attempting to upgrade our 12.5.4 servers to 15.0.3.
>> ESD#2
>>
>> Our development/qa servers are Solaris 240s, solaris 10 and
>> very similar configurations and performance.
>>
>> I just did an in-place install of 15 and brought up the
>> server without issue in our development environment.
>>
>> post upgrade task that were completed:
>> reorg rebuild all primary tables.
>> update index statistics on all tables.
>> Activated statement cache and sized.
>> increased tempdb
>> set devices to direct i/o
>> after initial tests ran sp_monitorconfig and verified no
>> reuse.
>>
>> The reports from my development team are that almost all
>> application operations are slower to much slower. I don't
>> think I have heard anyone say anything has improved. One
>> particular application, our Informatica ETL tool is
>> experiencing major distress with multiple queries creating
>> execution plans that increase query times 10 fold
>> (unacceptable).
>>
>> In the face of unpredictable behavior I have retreated from
>> abstract query plans as a solution to specific query
>> problems.
>>
>> With the shear volume of slowness I am obviously going to
>> have to first concentrate on server level resolution.
>>
>> We are now testing compatibility mode and initial reports
>> are that the small amount of queries that were unacceptably
>> slow are now working but slower than 12.5 across the board.
>>
>> I have also tested optimization goal settings with no
>> dramatic affect.
>>
>> Hopefully I am overlooking something very obvious.
>>
>> Any trouble shooting suggestions are welcome.
>>
>> JB


Eugene Posted on 2009-10-29 14:35:24.0Z
From: Eugene <ekorolkov@davidsohn.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: 15 performance
References: <4ae75f83.7469.1681692777@sybase.com> <4ae766e7@forums-1-dub> <4ae8b73b@forums-1-dub>
In-Reply-To: <4ae8b73b@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ae9a82c$1@forums-1-dub>
Date: 29 Oct 2009 06:35:24 -0800
X-Trace: forums-1-dub 1256826924 10.22.241.152 (29 Oct 2009 06:35:24 -0800)
X-Original-Trace: 29 Oct 2009 06:35:24 -0800, vip152.sybase.com
Lines: 144
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28567
Article PK: 77809

I agree. It is slower if you just upgrade. Looks like it might be
faster, but you have to rewrite. Nobody has time for it and is guilty now.
allrows_oltp gives you more or less acceptable performance, but also
slower. Compatibility mode produced stack trace some times. (at least
ESD#1 15.0.3). Have case with Sybase about it.

Eugene

SY wrote:
> Just to correct one of Mark's (very useful) points.
> compatibility mode IS to all intents and purposes (bar a couple of
> unavoidable costing conflicts) the 125x optimizer and indeed the 125x
> execution engine.
> It is not a dumbed-down version of the 15x optimizer.
>
> I think this confusion may come from partial compatibility mode (aka
> basic_optimization) which was implemented prior to compatibility mode.
> This can show the words 'compatibility mode' in the diagnostic traces to
> cause headaches all round.... :-|
> Rob's whitepaper makes reference to partial compatibility which consists
> of a 125x AP passed into the 15x optimizer.
>
>
>
> "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in
> message news:4ae766e7@forums-1-dub...
>> Generally speaking ... do some research on other posts (eg,
>> sybase.public.ase.performance+tuning, ISUG forums) and Sybase
>> webcasts/documents that address ASE 15 upgrade performance issues and
>> recommendations.
>>
>> I've helped upgrade 100+ dataservers to ASE 15.x; here's a smattering
>> of comments from my past posts re: ASE 15 upgrade/performance issues:
>>
>> - non-ANSI standard SQL typically runs worse with ASE 15; solution is
>> to rewrite code to be (more) ANSI-compliant ... this typically also
>> improves performance on ASE 12.5.x servers; ASE 15's optimizer is
>> less-forgiving of poorly written SQL
>>
>> - the ASE 15 optimizer is typically much slower than the previous
>> optimizers; this becomes quite apparent for processes that make heavy
>> use of individual SQL statements (to include repserver/DSI connections
>> into RDBs); to improve performance there are a handful of options ...
>> use (dynamic/prepared) stored procs, use statement cache (+ literal
>> autoparam), redesign applications to use more set-based processing (as
>> opposed to cursor-like processing)
>>
>> - 'enable literal autoparam' must also be configured (to '1') in order
>> for statement cache to be effective; use sp_sysmon ("Statement Cache"
>> section) to verify if statement cache is in use
>>
>> - statement cache generates 'temporary' stored procs which in turn
>> requires more procedure cache; us sp_sysmon to keep any eye on proc
>> cache thrashing; you may need/want to increase proc cache sizing
>>
>> - statement cache (+ literal autoparam) can suffer from the same
>> symptoms of 'bad' query plans as with stored procs; this typically
>> arises when SQL SARG values have wide ranging distribution stats which
>> can lead to a single query being optimized differently based on the
>> SARG values ... net effect is that a re-used query plan (pulled from
>> statement cache) can lead to poorly performing queries; two possible
>> solutions ... disable statement cache utilization (at the dataserver
>> or session level) or use different query structures for different SARG
>> values (this requires the SQL developer *REALLY* knows their data)
>>
>> - I typically pick an optimization goal that works for 'most' queries
>> and then add code modifications (at session or query/AQP levels) to
>> address individual queries that compile 'better' under other
>> optimization goals; for a vast majority of the systems I've helped
>> upgrade we've stuck with allrows_oltp as the dataserver-level config
>> setting, then used session/query-level commands when we found that
>> allrows_mixed or allrows_dss performed better
>>
>> - compatibility mode (IMO) isn't worth the effort; you're *NOT*
>> gaining access to the ASE 12.5.x optimizer but rather a dumbed-down
>> version of the ASE 15 optimizer; compatibility mode disables quite a
>> few ASE 15 features that you may need now or in the near future ... so
>> at some point you'll need to incur the overhead of doing a *real* ASE
>> 15 upgrade ... might as well get the upgrade out of the way now (as
>> opposed to putting yourself through 2 sets of upgrade hassles)
>>
>> - minimize the desire to use new ASE 15 features until you've got the
>> basic upgrade completed and stable; also consider doing just ASE 15
>> upgrades *OR* RS 15 upgrades at a time ... don't complicate your life
>> by trying to address ASE 15 and RS 15 upgrade issues at the same time
>>
>> - consider upgrading (and thoroughly testing) your development and/or
>> test systems first; consider upgrading less-important production
>> systems first; consider upgrading PDSs in a replication environment
>> (and leave the RDSs at ASE 12.5.x levels - for WS setups this makes it
>> much easier to 'downgrade' to ASE 12.5 if you run into show-stopper
>> issues with ASE 15, ie, just perform a RS/WS 'switch active')
>>
>> - consider bringing in an outside source that has experience with ASE
>> 15 upgrades; said person should have a solid background in P&T work
>> since they'll need to be able to track down performance issues and
>> then come up with resolutions to said issues
>>
>> jbuhl wrote:
>>> We are attempting to upgrade our 12.5.4 servers to 15.0.3.
>>> ESD#2
>>>
>>> Our development/qa servers are Solaris 240s, solaris 10 and
>>> very similar configurations and performance.
>>>
>>> I just did an in-place install of 15 and brought up the
>>> server without issue in our development environment.
>>>
>>> post upgrade task that were completed:
>>> reorg rebuild all primary tables.
>>> update index statistics on all tables.
>>> Activated statement cache and sized.
>>> increased tempdb
>>> set devices to direct i/o
>>> after initial tests ran sp_monitorconfig and verified no
>>> reuse.
>>>
>>> The reports from my development team are that almost all
>>> application operations are slower to much slower. I don't
>>> think I have heard anyone say anything has improved. One
>>> particular application, our Informatica ETL tool is
>>> experiencing major distress with multiple queries creating
>>> execution plans that increase query times 10 fold
>>> (unacceptable).
>>>
>>> In the face of unpredictable behavior I have retreated from
>>> abstract query plans as a solution to specific query
>>> problems.
>>>
>>> With the shear volume of slowness I am obviously going to
>>> have to first concentrate on server level resolution.
>>>
>>> We are now testing compatibility mode and initial reports
>>> are that the small amount of queries that were unacceptably
>>> slow are now working but slower than 12.5 across the board.
>>>
>>> I have also tested optimization goal settings with no
>>> dramatic affect.
>>>
>>> Hopefully I am overlooking something very obvious.
>>>
>>> Any trouble shooting suggestions are welcome.
>>>
>>> JB
>


Liezl Nel Posted on 2009-11-02 12:22:21.0Z
Sender: 4bc4.4aeebcf2.1804289383@sybase.com
From: Liezl Nel
Newsgroups: sybase.public.ase.general
Subject: Re: 15 performance
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4aeecefd.4fdc.1681692777@sybase.com>
References: <4ae8b73b@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 2 Nov 2009 04:22:21 -0800
X-Trace: forums-1-dub 1257164541 10.22.241.41 (2 Nov 2009 04:22:21 -0800)
X-Original-Trace: 2 Nov 2009 04:22:21 -0800, 10.22.241.41
Lines: 173
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28576
Article PK: 77819

Mark your comments are extremely helpful, thank you!

May I add Two things:

We've also found in some cases,(not in all cases) that Trace
Flag 437 at session level helped with a few of our slow
running queries.
We did not want to set this trace flag server wide, as we
were worried that it may then affect the queries that
absolutely benefitted from the ASE 15 Upgrade.

Trace flag 437 is very specific to the costing for composite
indices and multi-attribute densities, when the optimiser
considers the most economical query plan.
NB: It is NOT a flag that switches ASE to 12.5 Optimiser
behavior!
The optimizer, the overall costing model and the execution
engine is still 15x.

Testing is crucial to a successful upgrade!
ALWAYS TEST!! and if you think you're done, test again!

L

> Just to correct one of Mark's (very useful) points.
> compatibility mode IS to all intents and purposes (bar a
> couple of unavoidable costing conflicts) the 125x
> optimizer and indeed the 125x execution engine.
> It is not a dumbed-down version of the 15x optimizer.
>
> I think this confusion may come from partial compatibility
> mode (aka basic_optimization) which was implemented prior
> to compatibility mode. This can show the words
> 'compatibility mode' in the diagnostic traces to cause
> headaches all round.... :-| Rob's whitepaper makes
> reference to partial compatibility which consists of a
> 125x AP passed into the 15x optimizer.
>
>
>
> "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
> wrote in message news:4ae766e7@forums-1-dub...
> > Generally speaking ... do some research on other posts
> > (eg, sybase.public.ase.performance+tuning, ISUG forums)
> > and Sybase webcasts/documents that address ASE 15
> > upgrade performance issues and recommendations.
> >
> > I've helped upgrade 100+ dataservers to ASE 15.x; here's
> > a smattering of comments from my past posts re: ASE 15
> upgrade/performance issues: >
> > - non-ANSI standard SQL typically runs worse with ASE 15
> > ; solution is to rewrite code to be (more)
> > ANSI-compliant ... this typically also improves
> performance on ASE 12.5.x servers; ASE 15's optimizer is
> > less-forgiving of poorly written SQL
> >
> > - the ASE 15 optimizer is typically much slower than the
> > previous optimizers; this becomes quite apparent for
> > processes that make heavy use of individual SQL
> > statements (to include repserver/DSI connections into
> RDBs); to improve performance there are a handful of
> > options ... use (dynamic/prepared) stored procs, use
> > statement cache (+ literal autoparam), redesign
> > applications to use more set-based processing (as
> opposed to cursor-like processing) >
> > - 'enable literal autoparam' must also be configured (to
> > '1') in order for statement cache to be effective; use
> > sp_sysmon ("Statement Cache" section) to verify if
> statement cache is in use >
> > - statement cache generates 'temporary' stored procs
> > which in turn requires more procedure cache; us
> > sp_sysmon to keep any eye on proc cache thrashing; you
> may need/want to increase proc cache sizing >
> > - statement cache (+ literal autoparam) can suffer from
> > the same symptoms of 'bad' query plans as with stored
> > procs; this typically arises when SQL SARG values have
> > wide ranging distribution stats which can lead to a
> single query being optimized differently based on the SARG
> > values ... net effect is that a re-used query plan
> > (pulled from statement cache) can lead to poorly
> performing queries; two possible solutions ... disable
> > statement cache utilization (at the dataserver or
> > session level) or use different query structures for
> > different SARG values (this requires the SQL developer
> *REALLY* knows their data) >
> > - I typically pick an optimization goal that works for
> > 'most' queries and then add code modifications (at
> > session or query/AQP levels) to address individual
> queries that compile 'better' under other optimization
> > goals; for a vast majority of the systems I've helped
> > upgrade we've stuck with allrows_oltp as the
> > dataserver-level config setting, then used
> session/query-level commands when we found that
> > allrows_mixed or allrows_dss performed better
> >
> > - compatibility mode (IMO) isn't worth the effort;
> > you're *NOT* gaining access to the ASE 12.5.x optimizer
> > but rather a dumbed-down version of the ASE 15
> optimizer; compatibility mode disables quite a few ASE 15
> > features that you may need now or in the near future
> > ... so at some point you'll need to incur the overhead
> > of doing a *real* ASE 15 upgrade ... might as well get
> the upgrade out of the way now (as opposed to putting
> > yourself through 2 sets of upgrade hassles)
> >
> > - minimize the desire to use new ASE 15 features until
> > you've got the basic upgrade completed and stable;
> > also consider doing just ASE 15 upgrades *OR* RS 15
> > upgrades at a time ... don't complicate your life by
> trying to address ASE 15 and RS 15 upgrade issues at the
> same time >
> > - consider upgrading (and thoroughly testing) your
> > development and/or test systems first; consider
> > upgrading less-important production systems first;
> consider upgrading PDSs in a replication environment (and
> > leave the RDSs at ASE 12.5.x levels - for WS setups
> > this makes it much easier to 'downgrade' to ASE 12.5 if
> > you run into show-stopper issues with ASE 15, ie, just
> perform a RS/WS 'switch active') >
> > - consider bringing in an outside source that has
> > experience with ASE 15 upgrades; said person should
> > have a solid background in P&T work since they'll need
> to be able to track down performance issues and then come
> > up with resolutions to said issues
> >
> > jbuhl wrote:
> >> We are attempting to upgrade our 12.5.4 servers to
> 15.0.3. >> ESD#2
> >>
> >> Our development/qa servers are Solaris 240s, solaris 10
> and >> very similar configurations and performance.
> >>
> >> I just did an in-place install of 15 and brought up the
> >> server without issue in our development environment.
> >>
> >> post upgrade task that were completed:
> >> reorg rebuild all primary tables.
> >> update index statistics on all tables.
> >> Activated statement cache and sized.
> >> increased tempdb
> >> set devices to direct i/o
> >> after initial tests ran sp_monitorconfig and verified
> no >> reuse.
> >>
> >> The reports from my development team are that almost
> all >> application operations are slower to much slower.
> I don't >> think I have heard anyone say anything has
> improved. One >> particular application, our Informatica
> ETL tool is >> experiencing major distress with multiple
> queries creating >> execution plans that increase query
> times 10 fold >> (unacceptable).
> >>
> >> In the face of unpredictable behavior I have retreated
> from >> abstract query plans as a solution to specific
> query >> problems.
> >>
> >> With the shear volume of slowness I am obviously going
> to >> have to first concentrate on server level
> resolution. >>
> >> We are now testing compatibility mode and initial
> reports >> are that the small amount of queries that were
> unacceptably >> slow are now working but slower than 12.5
> across the board. >>
> >> I have also tested optimization goal settings with no
> >> dramatic affect.
> >>
> >> Hopefully I am overlooking something very obvious.
> >>
> >> Any trouble shooting suggestions are welcome.
> >>
> >> JB
>


Rob V [ Sybase ] Posted on 2009-11-03 16:12:00.0Z
Reply-To: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.general
References: <4ae75f83.7469.1681692777@sybase.com> <4ae766e7@forums-1-dub>
Subject: Re: 15 performance
Lines: 52
Organization: Sypron BV / TeamSybase / Sybase
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4af05650@forums-1-dub>
Date: 3 Nov 2009 08:12:00 -0800
X-Trace: forums-1-dub 1257264720 10.22.241.152 (3 Nov 2009 08:12:00 -0800)
X-Original-Trace: 3 Nov 2009 08:12:00 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28580
Article PK: 77822

----- Original Message -----
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
Newsgroups: sybase.public.ase.general
Sent: Tuesday, October 27, 2009 22:32
Subject: Re: 15 performance

[...]

> - compatibility mode (IMO) isn't worth the effort; you're *NOT* gaining
> access to the ASE 12.5.x optimizer but rather a dumbed-down version of the
> ASE 15 optimizer; compatibility mode disables quite a few ASE 15 features
> that you may need now or in the near future ... so at some point you'll
> need to incur the overhead of doing a *real* ASE 15 upgrade ... might as
> well get the upgrade out of the way now (as opposed to putting yourself
> through 2 sets of upgrade hassles)

As usual, Mark mentions many good points. This time however, I have to
disagree on the one above.
With compatibility mode enabled, you'll be using the same optimizer logic
(as well as execution logic) as in 12.5.4. There has been great effort to
bring all the parts of the 12.5 logic into the 15.0 framework, and for
queries that are migrated from 12.5 to 15 without any changes (and without
utilizing 15-specific features such as partitioned tables), you should see
the vast majority of query plans to be identical to 12.5. We've seen this
work very well for many customers now.
The 12.5-compatible query processing layer is not 'dumbed down' at all --
though there can be cases where things might work out a little different
still, these would definitely be exceptions.

It should be noted that with compatibility mode enabled, a statement can run
in one of three mode: full compat mode, ASE 15 mode, and also in restricted
compat mode, which is sometimes used when you're using certain ASE 15
features in your queries anyway.

Compat mode was added to allow customers to migrate to ASE 15 more easily,
with less testing effort. Also, it lets you target specific areas of your
system where you could later disable compat mode and tune for optimal ASE 15
performance. The idea being that the proverbial 80% of your queries will
probably not be worth tuning effort sicne they'r enot critical, whereas the
remaining 20% does. WIth compat mode, you can focus on those 20% whereas
without it, you had to take care of the other 80% as well, since migrating
to the 15 query engine could result in different query plans there too.

For more details, check out the whitepaper at
http://www.sybase.com/detail?id=1063556 .


HTH,

Rob V.


"Mark A. Parsons" <iron_horse Posted on 2009-11-03 18:55:12.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: 15 performance
References: <4ae75f83.7469.1681692777@sybase.com> <4ae766e7@forums-1-dub> <4af05650@forums-1-dub>
In-Reply-To: <4af05650@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 091030-0, 10/30/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4af07c90$1@forums-1-dub>
Date: 3 Nov 2009 10:55:12 -0800
X-Trace: forums-1-dub 1257274512 10.22.241.152 (3 Nov 2009 10:55:12 -0800)
X-Original-Trace: 3 Nov 2009 10:55:12 -0800, vip152.sybase.com
Lines: 145
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28587
Article PK: 77829

Looks like I need to break my statement down into 2 parts ... 12.5.4 optimizer (and execution engine) availability in
ASE 15.0.3 ... usefulness of compatibility mode ...

---------------------

12.5.4 optimizer (and execution engine) availability in ASE 15.0.3:

If you know that the codeline for the 12.5.4 optimizer was pulled into 15.0.3 ... fine, I'll retract my comment about
the dumbed-down 15.x optimizer.

How about dumbed-down 12.5.4 optimizer? or perhaps hog-tied 12.5.4 optimizer?

I've read the whitepaper you reference, as well as the ASE 15.0.3 Migration Technology Guide.

What I did not see are any statements of 100% support for the 12.5.4 optimizer (and execution engine).

What I do see are a lot of caveats about how/when the 12.5.4 optimizer *may* (not) be used.

Yes, I understand the inability to access the 12.5.4 optimizer when utilizing ASE 15 features (eg, statement
cache/literal autoparam, semantic partitioning, UDFs, etc).

What's more interesting is how the 12.5.4 optimizer (and/or execution engine) is limited/not-available for certain
features that *are* supported in the ASE 12.5.4 dataserver (eg, parallel processing, referential integrity, accessing
text/image datatypes, encrypted columns, round robin partitions).

Then there's the question of *why* compatibility mode was introduced into ASE 15.0.3.

Yes, I'm aware that many clients [NOTE: *ALL* of the dozen clients I've worked with] have had problems upgrading to use
the 15.x optimizer ... so the idea is to give them a kinda/sorta/almost-like 12.5.4 optimizer capability in ASE 15.0.3
... but what happens going forward?

ASE 12.5.x will be EOL'd shortly. [NOTE: EOL means, in essence, the product will still function, but there will be no
bug fixes and/or feature improvements to the codeline. For bug fixes and/or feature improvements the user will have to
upgrade to a newer version of ASE 15.x.]

If a new bug is found in the 12.5.4 and/or 15.0.3 optimizer (both in the ASE 15.0.3 codeline), will the bug fixes be
pushed back into the 12.5.4 optimizer portion of the ASE 15.0.3 codeline? If so, then why EOL the ASE 12.5.x codeline?
If not, why drag an 'old' piece of software code into the ASE 15.0.3 codeline which is not going to be maintained?

---------------------

Usefulness of compatibility mode:

Both documents (white paper, Migration Technology Guide) make comments about how the client can perform what amounts to
an incremental upgrade to ASE 15.0.3.

Both documents list *many* caveats to the inability to use (restricted) compatibility mode. Pile these additional
'tuning facts' on top of the already large pile of 15.x what-to-look-out-for tuning recommendations ... and each
incremental upgrade step to ASE 15.0.3 becomes even more complicated for the client.

What isn't made clear to the novice (and experienced?) DBA performing these 'incremental upgrades' is that for each
'incremental upgrade' the client will need to conduct a *LOT* of testing to insure a successful upgrade. The level of
effort required for these incremental steps will require at least (if not more) resources, effort, work and risk (to
production systems) than performing a single upgrade from ASE 12.x to ASE 15.0.3.

The comment ...

"the proverbial 80% of your queries will probably not be worth tuning effort sicne they'r enot critical, whereas the
remaining 20% does"

... is a bit misleading in that it doesn't address the fact that the client still has to figure out which of their
queries fall into the 80%/20% categories. This will require that the client expend the same level of effort (as with a
complete ASE 15.0.3 upgrade) to monitor and capture problematic queries.

The documents make mention of several coding steps, configuration settings and/or trace flags that can be implemented in
order to utilize (restricted) compatibility mode in some (but not all) tuning cases. The client that's looking at
performing these 'incremental upgrades' should keep in the back of their mind ... "code changes", "additional testing",
"additional complexity" ... the same things they need to consider when performing a complete upgrade to ASE 15.0.3.

A 'complete' upgrade to 15.0.3 (typically) requires the client to perform an increased level of testing that they have
not had to do in the past re: ASE upgrades. There are plenty of documents, white papers, and newsgroup/forum posts that
recommend the client test, Test, TEST ... and then TEST some more.

NOTE: I also recommend that the client have a full-blown, workable, downgrade/rollback plan in place before upgrading
to ASE 15.x. *PLAN* on having problems on the date you take ASE 15.0.3 live in production. [Sure, this should be a
no-brainer, but I've seen several clients that have become slack in this area due to the relative ease of past ASE
upgrades.]

For many clients each set of testing typically means pulling together resources (hardware, networks, DBAs, developers,
business folks, 3rd-party players, etc).

Incremental upgrades via (restricted) compatibility mode will require a client to pull together all of these resources
for *EACH* incremental upgrade. Do said clients have these kind of resources to pour into what amounts to 'just' an
upgrade of some software (as opposed to the level of resources required when developing new applications/products, or
bringing a new RDBMS online) ?

IMO compatibility mode is a waste of time and effort ... either continue running on ASE 12.5.x ... or put the resources
and effort into performing a complete upgrade to ASE 15.

Rob V [ Sybase ] wrote:
> ----- Original Message -----
> From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
> Newsgroups: sybase.public.ase.general
> Sent: Tuesday, October 27, 2009 22:32
> Subject: Re: 15 performance
>
> [...]
>> - compatibility mode (IMO) isn't worth the effort; you're *NOT* gaining
>> access to the ASE 12.5.x optimizer but rather a dumbed-down version of the
>> ASE 15 optimizer; compatibility mode disables quite a few ASE 15 features
>> that you may need now or in the near future ... so at some point you'll
>> need to incur the overhead of doing a *real* ASE 15 upgrade ... might as
>> well get the upgrade out of the way now (as opposed to putting yourself
>> through 2 sets of upgrade hassles)
>
>
> As usual, Mark mentions many good points. This time however, I have to
> disagree on the one above.
> With compatibility mode enabled, you'll be using the same optimizer logic
> (as well as execution logic) as in 12.5.4. There has been great effort to
> bring all the parts of the 12.5 logic into the 15.0 framework, and for
> queries that are migrated from 12.5 to 15 without any changes (and without
> utilizing 15-specific features such as partitioned tables), you should see
> the vast majority of query plans to be identical to 12.5. We've seen this
> work very well for many customers now.
> The 12.5-compatible query processing layer is not 'dumbed down' at all --
> though there can be cases where things might work out a little different
> still, these would definitely be exceptions.
>
> It should be noted that with compatibility mode enabled, a statement can run
> in one of three mode: full compat mode, ASE 15 mode, and also in restricted
> compat mode, which is sometimes used when you're using certain ASE 15
> features in your queries anyway.
>
> Compat mode was added to allow customers to migrate to ASE 15 more easily,
> with less testing effort. Also, it lets you target specific areas of your
> system where you could later disable compat mode and tune for optimal ASE 15
> performance. The idea being that the proverbial 80% of your queries will
> probably not be worth tuning effort sicne they'r enot critical, whereas the
> remaining 20% does. WIth compat mode, you can focus on those 20% whereas
> without it, you had to take care of the other 80% as well, since migrating
> to the 15 query engine could result in different query plans there too.
>
> For more details, check out the whitepaper at
> http://www.sybase.com/detail?id=1063556 .
>
>
> HTH,
>
> Rob V.
>
>


Rob V [ Sybase ] Posted on 2009-11-04 11:26:31.0Z
Reply-To: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.general
References: <4ae75f83.7469.1681692777@sybase.com> <4ae766e7@forums-1-dub> <4af05650@forums-1-dub> <4af07c90$1@forums-1-dub>
Subject: Re: 15 performance
Lines: 217
Organization: Sypron BV / TeamSybase / Sybase
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4af164e7@forums-1-dub>
Date: 4 Nov 2009 03:26:31 -0800
X-Trace: forums-1-dub 1257333991 10.22.241.152 (4 Nov 2009 03:26:31 -0800)
X-Original-Trace: 4 Nov 2009 03:26:31 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28595
Article PK: 77836

I get the feeling there's a misunderstsanding here somewhere... If you
migrate from 12.5 to 15 with compatibility mode enabled, and without
changing anything in your SQL (more on that below), then all the stuff about
optimization goals and most of the other 15.0-specific QP aspects just don't
apply: 99% (or so) of your queries will be processed in 12.5 mode and the
same rules apply as did in 12.5.
The other two modes only apply when you start changing SQL to use ASE 15
constructs or when doing things like using semantic partitions, computed
columns etc. When you don't, then these modes are not really relevant.
I agree that testing is always a necessity (not testing is equivalent to
accepting that failure doesn't matter to you). The whole point of
compatibility mode is that the testing should be pretty much the same as the
testing that was done when moving from, say, 12.0 to 12.5, which might be
much less intense than doing the full testing required for running in ASE 15
mode.

I agree that to make the most of ASE 15, compat mode won't give you the best
performance result. But it was never intended to do so: the tradeoff here is
migrating to 15 with compatibility mode while keeping query plans identical,
vs getting best performance by doing 15-specific tuning without
compatibility mode. For some customers, the former appeared to be a lot more
important than the latter, which is why compat mode was added. Of course,
you may be in the second category and wish to take full advantage of ASE 15,
in which case compat mode won't be much use.

As for changing your SQL, there are a number of things that need to be
changed. The good news is that these rarely occur in business applications
but typically only in things like DBA tools. See
http://www.sybase.com/detail?id=1063534 for details.

HTH,

Rob V.

"Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message
news:4af07c90$1@forums-1-dub...
> Looks like I need to break my statement down into 2 parts ... 12.5.4
> optimizer (and execution engine) availability in ASE 15.0.3 ... usefulness
> of compatibility mode ...
>
> ---------------------
>
> 12.5.4 optimizer (and execution engine) availability in ASE 15.0.3:
>
> If you know that the codeline for the 12.5.4 optimizer was pulled into
> 15.0.3 ... fine, I'll retract my comment about the dumbed-down 15.x
> optimizer.
>
> How about dumbed-down 12.5.4 optimizer? or perhaps hog-tied 12.5.4
> optimizer?
>
> I've read the whitepaper you reference, as well as the ASE 15.0.3
> Migration Technology Guide.
>
> What I did not see are any statements of 100% support for the 12.5.4
> optimizer (and execution engine).
>
> What I do see are a lot of caveats about how/when the 12.5.4 optimizer
> *may* (not) be used.
>
> Yes, I understand the inability to access the 12.5.4 optimizer when
> utilizing ASE 15 features (eg, statement cache/literal autoparam, semantic
> partitioning, UDFs, etc).
>
> What's more interesting is how the 12.5.4 optimizer (and/or execution
> engine) is limited/not-available for certain features that *are* supported
> in the ASE 12.5.4 dataserver (eg, parallel processing, referential
> integrity, accessing text/image datatypes, encrypted columns, round robin
> partitions).
>
> Then there's the question of *why* compatibility mode was introduced into
> ASE 15.0.3.
>
> Yes, I'm aware that many clients [NOTE: *ALL* of the dozen clients I've
> worked with] have had problems upgrading to use the 15.x optimizer ... so
> the idea is to give them a kinda/sorta/almost-like 12.5.4 optimizer
> capability in ASE 15.0.3 ... but what happens going forward?
>
> ASE 12.5.x will be EOL'd shortly. [NOTE: EOL means, in essence, the
> product will still function, but there will be no bug fixes and/or feature
> improvements to the codeline. For bug fixes and/or feature improvements
> the user will have to upgrade to a newer version of ASE 15.x.]
>
> If a new bug is found in the 12.5.4 and/or 15.0.3 optimizer (both in the
> ASE 15.0.3 codeline), will the bug fixes be pushed back into the 12.5.4
> optimizer portion of the ASE 15.0.3 codeline? If so, then why EOL the ASE
> 12.5.x codeline? If not, why drag an 'old' piece of software code into the
> ASE 15.0.3 codeline which is not going to be maintained?
>
> ---------------------
>
> Usefulness of compatibility mode:
>
> Both documents (white paper, Migration Technology Guide) make comments
> about how the client can perform what amounts to an incremental upgrade to
> ASE 15.0.3.
>
> Both documents list *many* caveats to the inability to use (restricted)
> compatibility mode. Pile these additional 'tuning facts' on top of the
> already large pile of 15.x what-to-look-out-for tuning recommendations ...
> and each incremental upgrade step to ASE 15.0.3 becomes even more
> complicated for the client.
>
> What isn't made clear to the novice (and experienced?) DBA performing
> these 'incremental upgrades' is that for each 'incremental upgrade' the
> client will need to conduct a *LOT* of testing to insure a successful
> upgrade. The level of effort required for these incremental steps will
> require at least (if not more) resources, effort, work and risk (to
> production systems) than performing a single upgrade from ASE 12.x to ASE
> 15.0.3.
>
> The comment ...
>
> "the proverbial 80% of your queries will probably not be worth tuning
> effort sicne they'r enot critical, whereas the remaining 20% does"
>
> ... is a bit misleading in that it doesn't address the fact that the
> client still has to figure out which of their queries fall into the
> 80%/20% categories. This will require that the client expend the same
> level of effort (as with a complete ASE 15.0.3 upgrade) to monitor and
> capture problematic queries.
>
> The documents make mention of several coding steps, configuration settings
> and/or trace flags that can be implemented in order to utilize
> (restricted) compatibility mode in some (but not all) tuning cases. The
> client that's looking at performing these 'incremental upgrades' should
> keep in the back of their mind ... "code changes", "additional testing",
> "additional complexity" ... the same things they need to consider when
> performing a complete upgrade to ASE 15.0.3.
>
> A 'complete' upgrade to 15.0.3 (typically) requires the client to perform
> an increased level of testing that they have not had to do in the past re:
> ASE upgrades. There are plenty of documents, white papers, and
> newsgroup/forum posts that recommend the client test, Test, TEST ... and
> then TEST some more.
>
> NOTE: I also recommend that the client have a full-blown, workable,
> downgrade/rollback plan in place before upgrading to ASE 15.x. *PLAN* on
> having problems on the date you take ASE 15.0.3 live in production.
> [Sure, this should be a no-brainer, but I've seen several clients that
> have become slack in this area due to the relative ease of past ASE
> upgrades.]
>
> For many clients each set of testing typically means pulling together
> resources (hardware, networks, DBAs, developers, business folks, 3rd-party
> players, etc).
>
> Incremental upgrades via (restricted) compatibility mode will require a
> client to pull together all of these resources for *EACH* incremental
> upgrade. Do said clients have these kind of resources to pour into what
> amounts to 'just' an upgrade of some software (as opposed to the level of
> resources required when developing new applications/products, or bringing
> a new RDBMS online) ?
>
> IMO compatibility mode is a waste of time and effort ... either continue
> running on ASE 12.5.x ... or put the resources and effort into performing
> a complete upgrade to ASE 15.
>
>
>
>
> Rob V [ Sybase ] wrote:
>> ----- Original Message -----
>> From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
>> Newsgroups: sybase.public.ase.general
>> Sent: Tuesday, October 27, 2009 22:32
>> Subject: Re: 15 performance
>>
>> [...]
>>> - compatibility mode (IMO) isn't worth the effort; you're *NOT* gaining
>>> access to the ASE 12.5.x optimizer but rather a dumbed-down version of
>>> the ASE 15 optimizer; compatibility mode disables quite a few ASE 15
>>> features that you may need now or in the near future ... so at some
>>> point you'll need to incur the overhead of doing a *real* ASE 15 upgrade
>>> ... might as well get the upgrade out of the way now (as opposed to
>>> putting yourself through 2 sets of upgrade hassles)
>>
>>
>> As usual, Mark mentions many good points. This time however, I have to
>> disagree on the one above.
>> With compatibility mode enabled, you'll be using the same optimizer logic
>> (as well as execution logic) as in 12.5.4. There has been great effort to
>> bring all the parts of the 12.5 logic into the 15.0 framework, and for
>> queries that are migrated from 12.5 to 15 without any changes (and
>> without utilizing 15-specific features such as partitioned tables), you
>> should see the vast majority of query plans to be identical to 12.5.
>> We've seen this work very well for many customers now.
>> The 12.5-compatible query processing layer is not 'dumbed down' at all --
>> though there can be cases where things might work out a little different
>> still, these would definitely be exceptions.
>>
>> It should be noted that with compatibility mode enabled, a statement can
>> run in one of three mode: full compat mode, ASE 15 mode, and also in
>> restricted compat mode, which is sometimes used when you're using certain
>> ASE 15 features in your queries anyway.
>>
>> Compat mode was added to allow customers to migrate to ASE 15 more
>> easily, with less testing effort. Also, it lets you target specific areas
>> of your system where you could later disable compat mode and tune for
>> optimal ASE 15 performance. The idea being that the proverbial 80% of
>> your queries will probably not be worth tuning effort sicne they'r enot
>> critical, whereas the remaining 20% does. WIth compat mode, you can focus
>> on those 20% whereas without it, you had to take care of the other 80% as
>> well, since migrating to the 15 query engine could result in different
>> query plans there too.
>>
>> For more details, check out the whitepaper at
>> http://www.sybase.com/detail?id=1063556 .
>>
>>
>> HTH,
>>
>> Rob V.


Cory Sane [TeamSybase] Posted on 2009-10-28 02:00:36.0Z
From: "Cory Sane [TeamSybase]" <cory!=sane>
Newsgroups: sybase.public.ase.general
References: <4ae75f83.7469.1681692777@sybase.com>
In-Reply-To: <4ae75f83.7469.1681692777@sybase.com>
Subject: Re: 15 performance
Lines: 58
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18005
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ae7a5c4@forums-1-dub>
Date: 27 Oct 2009 18:00:36 -0800
X-Trace: forums-1-dub 1256695236 10.22.241.152 (27 Oct 2009 18:00:36 -0800)
X-Original-Trace: 27 Oct 2009 18:00:36 -0800, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28558
Article PK: 77800

You have mentioned all of the right things to start with...
Mark has also given good ideas.
Go back and rebuild all tables.
I ended up running update index stats on a few tables a few times a day.
Do sampling on monSysWaits across 1,5,10,15minutes to see what is happening.
Check a few showplans...See if they are the same.
How much memory is available to this ase instance?

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

"jbuhl" wrote in message news:4ae75f83.7469.1681692777@sybase.com...
> We are attempting to upgrade our 12.5.4 servers to 15.0.3.
> ESD#2
>
> Our development/qa servers are Solaris 240s, solaris 10 and
> very similar configurations and performance.
>
> I just did an in-place install of 15 and brought up the
> server without issue in our development environment.
>
> post upgrade task that were completed:
> reorg rebuild all primary tables.
> update index statistics on all tables.
> Activated statement cache and sized.
> increased tempdb
> set devices to direct i/o
> after initial tests ran sp_monitorconfig and verified no
> reuse.
>
> The reports from my development team are that almost all
> application operations are slower to much slower. I don't
> think I have heard anyone say anything has improved. One
> particular application, our Informatica ETL tool is
> experiencing major distress with multiple queries creating
> execution plans that increase query times 10 fold
> (unacceptable).
>
> In the face of unpredictable behavior I have retreated from
> abstract query plans as a solution to specific query
> problems.
>
> With the shear volume of slowness I am obviously going to
> have to first concentrate on server level resolution.
>
> We are now testing compatibility mode and initial reports
> are that the small amount of queries that were unacceptably
> slow are now working but slower than 12.5 across the board.
>
> I have also tested optimization goal settings with no
> dramatic affect.
>
> Hopefully I am overlooking something very obvious.
>
> Any trouble shooting suggestions are welcome.
>
> JB


jbuhl Posted on 2009-10-28 17:06:37.0Z
Sender: 220d.4ae873cf.1804289383@sybase.com
From: jbuhl
Newsgroups: sybase.public.ase.general
Subject: Re: 15 performance
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ae87a1d.2309.1681692777@sybase.com>
References: <4ae7a5c4@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 28 Oct 2009 09:06:37 -0800
X-Trace: forums-1-dub 1256749597 10.22.241.41 (28 Oct 2009 09:06:37 -0800)
X-Original-Trace: 28 Oct 2009 09:06:37 -0800, 10.22.241.41
Lines: 200
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28561
Article PK: 77802

I am going back now and using delete stats and then
updateing index stats

There is about 7GB allocated to ASE.

I shipped off several samples of monprocessyswaits samples
to Sybase TS and they said everything looked normal.

They also looked at sysmon report and said that looked good.

Cache hits look good
not waits

One thing I have notice is that there is very little
parallel queries being done. Here is a sysmon sample during
a particular busy time. Our 12.5 version relied on parallel
queries much more.

Kernel Utilization
------------------

Your Runnable Process Search Count is set to 2000
and I/O Polling Process Count is set to 10

Engine Busy Utilization CPU Busy I/O Busy
Idle
------------------------ -------- --------
--------
Engine 0 60.8 % 2.1 %
37.2 %
Engine 1 51.3 % 0.5 %
48.2 %
------------------------ -------- --------
--------
Summary Total 112.1 % 2.6 %
85.3 %
Average 56.0 % 1.3 %
42.7 %

CPU Yields by Engine per sec per xact
count % of total
------------------------- ------------ ------------
---------- ----------
Engine 0 30.6 55.6
3669 56.4 %
Engine 1 23.6 42.9
2831 43.6 %
------------------------- ------------ ------------
----------
Total CPU Yields 54.2 98.5
6500

Network Checks
Non-Blocking 17763.2 32296.7
2131584 99.7 %
Blocking 54.2 98.5
6500 0.3 %
------------------------- ------------ ------------
----------
Total Network I/O Checks 17817.4 32395.2
2138084
Avg Net I/Os per Check n/a n/a
0.00358 n/a

Disk I/O Checks
Total Disk I/O Checks 17994.6 32717.5
2159352 n/a
Checks Returning I/O 6338.1 11523.8
760571 35.2 %
Avg Disk I/Os Returned n/a n/a
0.00237 n/a


===============================================================================

Worker Process Management
-------------------------
per sec per xact
count % of total
------------ ------------
---------- ----------
Worker Process Requests
Total Requests 0.0 0.0
0 n/a

Worker Process Usage
Total Used 0.0 0.0
0 n/a
Max Ever Used During Sample 0.0 0.0
0 n/a

Memory Requests for Worker Processes
Total Requests 0.0 0.0
0 n/a

Tuning Recommendations for Worker Processes
-------------------------------------------
- Consider decreasing the 'number of worker processes'
configuration parameter.


===============================================================================

Parallel Query Management
-------------------------

Parallel Query Usage per sec per xact
count % of total
------------------------- ------------ ------------
---------- ----------
Total Parallel Queries 0.0 0.0
0 n/a

Merge Lock Requests per sec per xact
count % of total
------------------------- ------------ ------------
---------- ----------
Total # of Requests 0.0 0.0
0 n/a

Sort Buffer Waits per sec per xact
count % of total
------------------------- ------------ ------------
---------- ----------
Total # of Waits 0.0 0.0
0 n/a

===============================================================================

Task Management per sec per xact
count % of total
--------------------------- ------------ ------------
---------- ----------

Connections Opened 0.0 0.0
2 n/a

> You have mentioned all of the right things to start
> with... Mark has also given good ideas.
> Go back and rebuild all tables.
> I ended up running update index stats on a few tables a
> few times a day. Do sampling on monSysWaits across
> 1,5,10,15minutes to see what is happening. Check a few
> showplans...See if they are the same. How much memory is
> available to this ase instance?
>
> --
> Cory Sane
> [TeamSybase]
> Certified Sybase Associate DBA for ASE 15.0
> "jbuhl" wrote in message
> > news:4ae75f83.7469.1681692777@sybase.com... We are
> > attempting to upgrade our 12.5.4 servers to 15.0.3.
> > ESD#2
> > Our development/qa servers are Solaris 240s, solaris 10
> > and very similar configurations and performance.
> >
> > I just did an in-place install of 15 and brought up the
> > server without issue in our development environment.
> >
> > post upgrade task that were completed:
> > reorg rebuild all primary tables.
> > update index statistics on all tables.
> > Activated statement cache and sized.
> > increased tempdb
> > set devices to direct i/o
> > after initial tests ran sp_monitorconfig and verified no
> > reuse.
> >
> > The reports from my development team are that almost all
> > application operations are slower to much slower. I
> > don't think I have heard anyone say anything has
> > improved. One particular application, our Informatica
> > ETL tool is experiencing major distress with multiple
> > queries creating execution plans that increase query
> > times 10 fold (unacceptable).
> >
> > In the face of unpredictable behavior I have retreated
> > from abstract query plans as a solution to specific
> > query problems.
> >
> > With the shear volume of slowness I am obviously going
> > to have to first concentrate on server level resolution.
> >
> > We are now testing compatibility mode and initial
> > reports are that the small amount of queries that were
> > unacceptably slow are now working but slower than 12.5
> > across the board.
> > I have also tested optimization goal settings with no
> > dramatic affect.
> >
> > Hopefully I am overlooking something very obvious.
> >
> > Any trouble shooting suggestions are welcome.
> >
> > JB


tartampion Posted on 2009-10-28 19:32:45.0Z
Sender: 6c21.4ae73353.1804289383@sybase.com
From: Tartampion
Newsgroups: sybase.public.ase.general
Subject: Re: 15 performance
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ae89c5d.28ab.1681692777@sybase.com>
References: <4ae87a1d.2309.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 28 Oct 2009 11:32:45 -0800
X-Trace: forums-1-dub 1256758365 10.22.241.41 (28 Oct 2009 11:32:45 -0800)
X-Original-Trace: 28 Oct 2009 11:32:45 -0800, 10.22.241.41
Lines: 234
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28562
Article PK: 77803

I have noticed the same thing as you did, I have compared
the resources used by queries under 12.5.4 and 15.03 latest
EDS.
My conclusion is that ASE 15 uses between 7 to 1.2 times as
much resources as does 12.5.4(configured mixed strategy). I
would like to insist that we have done all the post upgrade
tasks as needed.
I would like to thank Mark for his very clear and useful
comments, I shall use his comments in improving the fate of
my queries.

> I am going back now and using delete stats and then
> updateing index stats
>
> There is about 7GB allocated to ASE.
>
> I shipped off several samples of monprocessyswaits samples
> to Sybase TS and they said everything looked normal.
>
> They also looked at sysmon report and said that looked
> good.
>
> Cache hits look good
> not waits
>
> One thing I have notice is that there is very little
> parallel queries being done. Here is a sysmon sample
> during a particular busy time. Our 12.5 version relied on
> parallel queries much more.
>
> Kernel Utilization
> ------------------
>
> Your Runnable Process Search Count is set to 2000
> and I/O Polling Process Count is set to 10
>
> Engine Busy Utilization CPU Busy I/O Busy
> Idle
> ------------------------ -------- --------
> --------
> Engine 0 60.8 % 2.1 %
> 37.2 %
> Engine 1 51.3 % 0.5 %
> 48.2 %
> ------------------------ -------- --------
> --------
> Summary Total 112.1 % 2.6 %
> 85.3 %
> Average 56.0 % 1.3 %
> 42.7 %
>
> CPU Yields by Engine per sec per xact
>
> count % of total
> ------------------------- ------------ ------------
> ---------- ----------
> Engine 0 30.6 55.6
>
> 3669 56.4 %
> Engine 1 23.6 42.9
>
> 2831 43.6 %
> ------------------------- ------------ ------------
> ----------
> Total CPU Yields 54.2 98.5
>
> 6500
>
> Network Checks
> Non-Blocking 17763.2 32296.7
> 2131584 99.7 %
> Blocking 54.2 98.5
>
> 6500 0.3 %
> ------------------------- ------------ ------------
> ----------
> Total Network I/O Checks 17817.4 32395.2
> 2138084
> Avg Net I/Os per Check n/a n/a
> 0.00358 n/a
>
> Disk I/O Checks
> Total Disk I/O Checks 17994.6 32717.5
> 2159352 n/a
> Checks Returning I/O 6338.1 11523.8
> 760571 35.2 %
> Avg Disk I/Os Returned n/a n/a
> 0.00237 n/a
>
>
> ==========================================================
> =====================
>
> Worker Process Management
> -------------------------
> per sec per xact
>
> count % of total
> ------------ ------------
> ---------- ----------
> Worker Process Requests
> Total Requests 0.0 0.0
>
> 0 n/a
>
> Worker Process Usage
> Total Used 0.0 0.0
>
> 0 n/a
> Max Ever Used During Sample 0.0 0.0
>
> 0 n/a
>
> Memory Requests for Worker Processes
> Total Requests 0.0 0.0
>
> 0 n/a
>
> Tuning Recommendations for Worker Processes
> -------------------------------------------
> - Consider decreasing the 'number of worker processes'
> configuration parameter.
>
>
> ==========================================================
> =====================
>
> Parallel Query Management
> -------------------------
>
> Parallel Query Usage per sec per xact
>
> count % of total
> ------------------------- ------------ ------------
> ---------- ----------
> Total Parallel Queries 0.0 0.0
>
> 0 n/a
>
> Merge Lock Requests per sec per xact
>
> count % of total
> ------------------------- ------------ ------------
> ---------- ----------
> Total # of Requests 0.0 0.0
>
> 0 n/a
>
> Sort Buffer Waits per sec per xact
>
> count % of total
> ------------------------- ------------ ------------
> ---------- ----------
> Total # of Waits 0.0 0.0
>
> 0 n/a
>
> ==========================================================
> =====================
>
> Task Management per sec per xact
>
> count % of total
> --------------------------- ------------ ------------
> ---------- ----------
>
> Connections Opened 0.0 0.0
>
> 2 n/a
>
>
>
>
>
> > You have mentioned all of the right things to start
> > with... Mark has also given good ideas.
> > Go back and rebuild all tables.
> > I ended up running update index stats on a few tables a
> > few times a day. Do sampling on monSysWaits across
> > 1,5,10,15minutes to see what is happening. Check a few
> > showplans...See if they are the same. How much memory is
> > available to this ase instance?
> >
> > --
> > Cory Sane
> > [TeamSybase]
> > Certified Sybase Associate DBA for ASE 15.0
> > "jbuhl" wrote in message
> > > news:4ae75f83.7469.1681692777@sybase.com... We are
> > > attempting to upgrade our 12.5.4 servers to 15.0.3.
> > > ESD#2
> > > Our development/qa servers are Solaris 240s, solaris
> > > 10 and very similar configurations and performance.
> > >
> > > I just did an in-place install of 15 and brought up
> > > the server without issue in our development
> > environment. >
> > > post upgrade task that were completed:
> > > reorg rebuild all primary tables.
> > > update index statistics on all tables.
> > > Activated statement cache and sized.
> > > increased tempdb
> > > set devices to direct i/o
> > > after initial tests ran sp_monitorconfig and verified
> > > no reuse.
> > >
> > > The reports from my development team are that almost
> > > all application operations are slower to much slower.
> > > I don't think I have heard anyone say anything has
> > > improved. One particular application, our Informatica
> > > ETL tool is experiencing major distress with multiple
> > > queries creating execution plans that increase query
> > > times 10 fold (unacceptable).
> > >
> > > In the face of unpredictable behavior I have retreated
> > > from abstract query plans as a solution to specific
> > > query problems.
> > >
> > > With the shear volume of slowness I am obviously going
> > > to have to first concentrate on server level
> > resolution. >
> > > We are now testing compatibility mode and initial
> > > reports are that the small amount of queries that were
> > > unacceptably slow are now working but slower than 12.5
> > > across the board.
> > > I have also tested optimization goal settings with no
> > > dramatic affect.
> > >
> > > Hopefully I am overlooking something very obvious.
> > >
> > > Any trouble shooting suggestions are welcome.
> > >
> > > JB


jbuhl Posted on 2009-10-30 15:38:54.0Z
Sender: 102f.4aeb03a9.1804289383@sybase.com
From: jbuhl
Newsgroups: sybase.public.ase.general
Subject: Re: 15 performance
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4aeb088e.10c0.1681692777@sybase.com>
References: <4ae75f83.7469.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 30 Oct 2009 07:38:54 -0800
X-Trace: forums-1-dub 1256917134 10.22.241.41 (30 Oct 2009 07:38:54 -0800)
X-Original-Trace: 30 Oct 2009 07:38:54 -0800, 10.22.241.41
Lines: 61
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28571
Article PK: 77813

Acceptable slowness has been achieved.

I increased "max repartition degree" and "number of sort
buffers" and developers and testing personnel instantly
started reporting improved performance. Even the ETL
process improved to tolerable slowness; which is orders of
magnitude better than what is was before.

Hard for me to believe the propaganda coming out of Sybase
saying the most things improve and there is only a small %
of queries that run poorly.

I am happy though as our migration is moving forward.

> We are attempting to upgrade our 12.5.4 servers to 15.0.3.
> ESD#2
>
> Our development/qa servers are Solaris 240s, solaris 10
> and very similar configurations and performance.
>
> I just did an in-place install of 15 and brought up the
> server without issue in our development environment.

> post upgrade task that were completed:
> reorg rebuild all primary tables.
> update index statistics on all tables.
> Activated statement cache and sized.
> increased tempdb
> set devices to direct i/o
> after initial tests ran sp_monitorconfig and verified no
> reuse.
>
> The reports from my development team are that almost all
> application operations are slower to much slower. I don't
> think I have heard anyone say anything has improved. One
> particular application, our Informatica ETL tool is
> experiencing major distress with multiple queries creating
> execution plans that increase query times 10 fold
> (unacceptable).
>
> In the face of unpredictable behavior I have retreated
> from abstract query plans as a solution to specific query
> problems.
>
> With the shear volume of slowness I am obviously going to
> have to first concentrate on server level resolution.
>
> We are now testing compatibility mode and initial reports
> are that the small amount of queries that were
> unacceptably slow are now working but slower than 12.5
> across the board.
>
> I have also tested optimization goal settings with no
> dramatic affect.
>
> Hopefully I am overlooking something very obvious.
>
> Any trouble shooting suggestions are welcome.
>
> JB


Michael Peppler [Team Sybase] Posted on 2009-10-31 06:53:38.0Z
From: "Michael Peppler [Team Sybase]" <mpeppler@peppler.org>
Organization: Peppler Consulting SARL
Subject: Re: 15 performance
User-Agent: Pan/0.14.2 (This is not a psychotic episode. It's a cleansing moment of clarity.)
Message-ID: <pan.2009.10.31.06.53.35.744371@peppler.org>
Newsgroups: sybase.public.ase.general
References: <4ae75f83.7469.1681692777@sybase.com> <4aeb088e.10c0.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 30 Oct 2009 22:53:38 -0800
X-Trace: forums-1-dub 1256972018 10.22.241.152 (30 Oct 2009 22:53:38 -0800)
X-Original-Trace: 30 Oct 2009 22:53:38 -0800, vip152.sybase.com
Lines: 73
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28573
Article PK: 77815

Well - my own expericence is that things improve, for the most part.

The real proof in the pudding will be in a couple of weeks when we move
our largest production system from 12.5.4 to 15.0.3 (after about a year of
testing!)

Michael

On Fri, 30 Oct 2009 07:38:54 -0800, jbuhl wrote:

> Acceptable slowness has been achieved.
>
> I increased "max repartition degree" and "number of sort
> buffers" and developers and testing personnel instantly
> started reporting improved performance. Even the ETL
> process improved to tolerable slowness; which is orders of
> magnitude better than what is was before.
>
> Hard for me to believe the propaganda coming out of Sybase
> saying the most things improve and there is only a small %
> of queries that run poorly.
>
> I am happy though as our migration is moving forward.
>
>
>> We are attempting to upgrade our 12.5.4 servers to 15.0.3.
>> ESD#2
>>
>> Our development/qa servers are Solaris 240s, solaris 10
>> and very similar configurations and performance.
>>
>> I just did an in-place install of 15 and brought up the
>> server without issue in our development environment.
>
>> post upgrade task that were completed:
>> reorg rebuild all primary tables.
>> update index statistics on all tables.
>> Activated statement cache and sized.
>> increased tempdb
>> set devices to direct i/o
>> after initial tests ran sp_monitorconfig and verified no
>> reuse.
>>
>> The reports from my development team are that almost all
>> application operations are slower to much slower. I don't
>> think I have heard anyone say anything has improved. One
>> particular application, our Informatica ETL tool is
>> experiencing major distress with multiple queries creating
>> execution plans that increase query times 10 fold
>> (unacceptable).
>>
>> In the face of unpredictable behavior I have retreated
>> from abstract query plans as a solution to specific query
>> problems.
>>
>> With the shear volume of slowness I am obviously going to
>> have to first concentrate on server level resolution.
>>
>> We are now testing compatibility mode and initial reports
>> are that the small amount of queries that were
>> unacceptably slow are now working but slower than 12.5
>> across the board.
>>
>> I have also tested optimization goal settings with no
>> dramatic affect.
>>
>> Hopefully I am overlooking something very obvious.
>>
>> Any trouble shooting suggestions are welcome.
>>
>> JB