SAP ASE <– Sybase ASE <–- Sybase SQL Server - Optimization

SAP ASE – Sybase ASE – Sybase SQL Server  - Evolution

Adaptive Server Enterprise (Sybase ASE) is a relational database management system, originally designed for Unix platforms in 1987 under the name Sybase SQL Server was renamed Sybase ASE for it’s 11.5 version when microsoft owns SQL Server, then renamed again as SAP ASE for its 16.0 version, when SAP acquired Sybase inc.

Sybase ASE runs (portable hence everywhere) on Linux and other Unix -based operating systems, Windows NT and Windows 2000, and Mac OS.

Prior to 1994, Sybase SQL Server evolved along the same lines as Microsoft SQL Server. Then Microsoft bought a copy of the Sybase SQL server source code and began engineering its product along a different line. A couple of years later, Sybase renamed its product ASE (to distinguish it from the Microsoft product) and released ASE Version 11.5.

BTW, ASE is mainly used for OLTP and IQ is for DSS, but most environments on ASE are generally with mixed loads and occasionally DSS loads. There would be specific days/times, when the regular way of Query optimization is not capable to perform the Best (likely Running large Month-End, Quarterly or Yearly Reports) we need to hint/configure ASE to think differently and it's possible with OPTIMIZATION GOALs :)

Optimization goals are a convenient way to match user query demands with the best optimization techniques, ensuring optimal use of the optimizer’s time and resources.

A central concept of Adaptive Server 15.0's query processing engine is the “optimization goal”, which provides an indication of the nature of the query being optimized. The Adaptive Server query optimizer determines how best to optimize a query based on optimization goals.

For example, a typical OLTP (online transaction processing) query and a typical DSS (decision-support system) query result in very different query plans due to the different data access patterns used by these queries. OLTP queries generally affect only one or a few rows and join only a few well-indexed tables. However, DSS queries typically affect many rows, return a few rows, and may join many tables.Because of their different access patterns, OLTP queries often run most efficiently using a classic “nested-loop join”, whereas DSS queries are more likely to run faster with a “hash join”. If you indicate that a query is for OLTP or DSS purposes, the optimizer uses that information to generate a query plan that may save time, memory, and CPU usage.

Optimization Goals

Adaptive Server 15.0.x+ provides three optimization goals, ordered from “narrow” to “wide,” which correspond to the number of options and strategies that they allow the optimizer to consider:

1. allrows_oltp – is best for OLTP queries. allrows_oltp offers the narrowest selection of join methods: the query optimizer considers only nested-loop joins.
2. allrows_mix – is the default after upgrading to Adaptive Server 15.0. allrows_mix allows the optimizer to consider merge joins as well as parallel plans (if the Adaptive Server is configured for parallelism).
3. allrows_dss – is best for DSS queries. allrows_dss offers the widest selection of join methods. The optimizer considers hash joins, as well as nested-loop joins, merge joins, and parallel plans.

SAP ASE allows users to configure the optimization goal, which you can specify at three tiers: server level, session level, and query level.

The server-level optimization goal is overridden at the session level, which is overridden at the query level.

You can define the optimization goal at the server-, session- or individual- query level:

·  Server-wide default:

   sp_configure 'optimization goal', 0, 'allrows_dss'

·  Session-level setting (overrides server-wide setting):

   set plan optgoal allrows_dss

·  Query-level setting (overrides server-wide and session-level settings):

   select * from T1, T2 where T1.a = T2.b  plan '(use optgoal allrows_dss)'

 We can also use a login trigger to set the session-level optimization goal for specific logins based on the kind of transaction/Query.

optimizer level

optimizer level determines the level of optimization the query processor uses.

• ase_current – enables all optimizer changes through the current release.
• ase_default – disables all optimizer changes since version 1503 ESD #1.
• ase1503esd2– enables all optimizer changes through version 15.0.3 ESD #2.
• ase1503esd3 – enables all optimizer changes through version 15.0.3 ESD #3.

 Compatibility Mode

Adaptive Server version 15.0 includes substantive changes to the query processor. For most customers, the new query processor provides a faster and more efficient environment. However, you may have tuned your server and applications based on the more restricted query processor from Adaptive Server version 12.5.4 and earlier and find the benefits of the version 15.0 query processor unsuitable in some situations. In that case, use the compatibility mode to upgrade to Adaptive Server 15.0 from version 12.5.x but retain performance characteristics similar to version 12.5.x. When you enable compatibility mode, Adaptive Server 15.0 uses a query engine similar to the one used in version 12.5.4, and provides an alternative optimization and execution strategy.

Compatibility Mode is an optimizer-related feature, and it provides compatibility just like applications we run in Windows 7/10 etc with option Trouble-shoot compatibility — to be specific, with the behaviour of the optimizer in ASE 12.5.x. This means that, with Compatibility Mode enabled, queries will be optimized according to the optimizer logic as in ASE 12.5.x, and get query plans that are identical or at least very similar to those in 12.5.x which will be the quickest fix for performance issues after upgrading to ASE 15.x+ versions from ASE 12.5.x and more specifically existing Queries or procedures are with abstract plan hints.

Enabling compatibility mode

On Adaptive Server 15.0.3 ESD #1 and later, you can enable compatibility mode at the session or server-wide level:

·         Session level – use set compatibility_mode on | off to enable or disable compatibility mode for the current session.

·         Server-wide – use sp_configure 'enable compatibility mode', 1 | 0 to enable or disable compatibility mode for the server.

Setting compatibility mode at the session level takes precedence over the server level.

Last but not the least, Choosing the best/apt Optimization goal setting significantly improves the Performance of the entire Sybase Server and Environment very quickly and easily. 

Very fortunately, We can set these parameters dynamically and session level too for any specific timelined Jobs or specific Queries/procedures..