Skip to Content

Documentation


http://scn.sap.com/docs/DOC-43406

http://scn.sap.com/docs/DOC-36047


Context:

When testing the performance of our most important stored procedures (SP) to migrate from ASE12.5 to ASE15.5, we realized that their performance were worse with the new ASE15.5.

Because of a lack of time and resources (rewriting 4000+ stored procedures is not easy and focusing on the most important SPs is also time-consuming…), we migrated to ASE15.5 in activating the server-wide compatibility mode (compatibility mode ON, statement cache OFF, literal autoparam OFF) to keep the old ASE12.5 way of executing SPs. This mode did not allow us to benefit from all the new ASE15 features (such as enabling the statement cache that improves the query performance and boost the replication to an ASE replica) but at least the ASE performance was as good as before.

After reading the Sybase documentation (that basically says “update your statistics” even for non-indexed columns), running a lot of tests (comparing text query plans with different formatting and graphical query plans in InteractiveSQL), opening Sybase cases, I realized the importance of the remark “the ASE optimizer has been re-written in ASE15” when updating the statistics of all the columns of all the tables in lots of my SPs would not help the optimizer to pick the same index as in ASE12.5 or giving more procedure cache would not improve the performance at all.

To summarize, we have 2 situations:

  • We are lucky and your query plan in ASE15 is almost the same as in ASE12.5 and updating your statistics will help the optimizer to perform as efficiently as before
  • We are not lucky and the optimizer will have a completely different query plan: in that case, you have to review your code and update your statistics

So how to disable the server-wide compatibility mode to use the ASE15 “native mode”?

My current method:

  1. Use MDA tables or application tools to determine the most used and/or the most important SPs => your Top 20 SPs
  2. Use QPTune to identify the longuest queries in your ‘Top 20’ SPs. Pay attention to sub-SPs.
  3. Identify the missing statistics by using QPTune or the option “set option show_missing_stats on”
  4. If missing statistics, update the statistics to see if the performance are better in native mode (out of 11 analyzed SPs it only works once for me)
  5. If no improvement, try to use different optimization level (option “set plan optlevel ase_current” worked for me)
  6. If no improvement, it is time to re-think the code and test it with compatibility mode off. You can start by identifying ways to use less temporary tables. One of the most efficient ways is to use SQL User-Defined Functions (UDF). They are fast and help to simplify the code. Be aware of the fact your SPs won’t be able to run in “full compatibility mode” once you introduce UDFs in your code. Be aware of the UDFs have limitations (you cannot use non-deterministic functions, no delete/insert/update).
  7. Rewriting the code can also involve simplifying the logics of queries, replace slow cursors with other techniques (while loop,…), optimizing the subqueries by testing the flattening (converting the subquery to a join) or materializing (storing the subquery results in a worktable) or review the ones with derived tables from the parent query
  8. Other ideas….
  9. All the SPs that we can not optimize are modified individually to be forced in compatibility mode (set compatibility_mode on)
  10. Take some performance measurement BEFORE
  11. Change the server-wide configuration setting “compatibility mode” to OFF
  12. Take some performance measurement AFTER

I am still working on that project. I keep you posted on my new findings and thoughts.

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Kevin Sherlock

    First, you should probably consider upgrade to 15.7 instead.  15.5 is over 5 years old now.

    Also, i’m not aware of any documentation that states that you should “update all statistics” as you suggest.  you might be confusing this with “update index statistics”, which is a general recommendation common with 15.x environments.  The true level of index maintenance necessary though is not determined by any rule-of-thumb.

    You also didn’t mention “optimization goals”, which by default is “allrows_mix” which includes merge_join methods, and parallel processing.  Also, i would note that UDF’s aren’t necessarily “more efficient” than temp tables.  Lots of dependencies there, and with most of the items listed above.  Best advice would be to review the many many documents already published about this topic.  Here are just a few:

    http://www.sybase.com/files/White_Papers/PlanningASE15Migration-040307.pdf

    http://www.sybase.com/files/White_Papers/ASE15UpgradeChecklist_for_12.x_V3.0.pdf

    http://www.sybase.com/files/White_Papers/SY-Required-SQL-Changes-for-ASE15-v.1-073009-WP.pdf

    These docs also reference other docs (and each other).

    Upgrades are not trivial as you certainly point out.  But, that’s definitely a project where DBA’s earn their money, and there are a number of tools to help you.  Most importantly, the support and comittment of management and decision makers around this application are what you need the most.  An app with 6000+ procedures sounds like something important enough to demand the resources and proper test planning needed to move forward.  Nobody should expect to slam in 10+ years of new DBMS (or any other type) software technology overnight (no matter who the vendor is).

    (0) 
    1. Vincent Rives Post author

      Thank you Kevin for your comments and the documentation. It is very much appreciated.

      You’re absolutely right about optimization goals! I forgot about this one.

      About “update all statistics”, running “update index statistics” was making no difference, so I tried to update all the columns statistics but it was in vain, which makes sense when you know the optimizer may make completely different choices than in ASE12.5.

      As you said it is a massive project that actually started in 2011. At that time, the vendor certified ASE15.5 but recently certified ASE15.7 SP50. So we are going to move on to this version before disabling the “compatibility mode”.

      (0) 
  2. Javier Ernesto Barthe

    Hi Vincent,

    I also used a simillar way in order to set configuration compatibility mode off. Perhaps if you have some tests prepared you can run one in compatmode on and other off in order to compare the logical reads of both tests and check if there is not change in compatmode off and you can also take a look at cpu time and wait time too.

    Once you have this information you will know what procedures take care off. About update statistics point, its quite important to run update index statistics in ASE 15.

    Regards.

    Javier Barthe.

    (0) 
  3. Andrew Melkonyan

    Vincent,

    I’ve built a bunch of tools to help myself when I was working on 12.5 to 15.7 migration.  One of these (Load Generator) was created to capture some of the actual code execution from MDAs and then run it on ASE 12.5 and ASE 15.7 to see both how ASEs respond to it and how the code itself performs.  It is a primitive (and free) version of what the premium WorkloadAlanlyser will do for post ASE 16.0.2 releases.  I found QPTune to be quite inconvenient to use.  I wanted something that will let me enter my code, run it, export statistics in excel format and compare.  Ditto for measuring effect of various optimizer settings on the code or of the code changes – you can run the same proc modifying ASE settings or code itself and then compare execution statistics across various metrics (LIO, TSCANS, &c – per table in the code).  I found it quite helpful.  You can give it a try if you wish…  You can definitely script your tests into perl/bash/&c too but that will start resembling QPTune…

    Another advise – turn on metrics capture on your ASEs, run your load on both versions and extract sysqueryplans/sysquerymetrics – these may be quite easily compared across versions giving you quite a nice impression of which SQL statement misbehave.  The negative side of it is that it treats each SQL as a thing in itself -disjointed from the SP it comes from (there are some traces in MDAs that this was thought to be tacked by ASE engineers but it is still in TBD stage).

    Sysmons may be useful for the overview comparison as well – if you collect these for both early / late version and compare across various metrics – you may already spot things you might want to address.  You’ve got a tool for that there as well…

    I personally loathe keeping the compatibility mode on – unless, of course, you have no choice (=time, =3d party =&c).

    Good luck!

    Location for the Sybase Monitoring Tools Has Moved… | Andrew m.

    (0) 
    1. Vincent Rives Post author

      Thanks Andrew for your very useful comments!

      Your Load Generator tool looks very handy. I could also use to compare difference of performance between 2 of my ASE15.7.

      I totally agree with you. We should try to do everything we can to turn off the compatibility mode to take advantage of the powerful ASE15 option and also because there is also no guarantee that the compatibility mode option will remain in the future ASE versions.

      I actually finished this project last year but my blog post got attention again after I updated the documentation broken links (from Sybase to SAP website) a month ago.

      Vincent

      (0) 
      1. Andrew Melkonyan

        You’re welcome.   I have noticed too late that the original post is… almost 2 years old 🙂 …

        Anyway, as long as you have survived the migration…

        ..I wonder why there is no badge on SCN with “ASE migration survivor” – sounds more tricky than participating in InnnoJam 🙂 🙂 🙂 ….

        Yea, I know.  may lead to bad PR….  Whatever.

        (0) 

Leave a Reply