Review: Sybase Podcast "Art of Performance & Tuning: Tips and Tricks"
Summary
This article reviews a podcast released by Sybase on "The Art of Performance & Tuning: Tips and Tricks".
Introduction
This podcast, released by Sybase in January 2007, is the third podcast produced by the company for ASE. Podcasting joins other recent innovations by the company to disseminate educational material such as providing staff blogs and recorded webinars (web seminars).
Podcast Details
This podcast is the third in performance and tuning (P&T) series. The full title is "ASE Tech Talk #001-03: The Art of Performance & Tuning: Tips and Tricks ", and it has a running time of 18 minutes. The host is Stefan Karlsson and the guest speakers are Jeff Tallman, Steve Bologna and Rob Verschoor, who provide a combined total of 60 years of experience with the product. Aside from this podcast, Rob Verschoor has published books on tips & tricks for ASE and Rep Server - his web site is an excellent resource for the Sybase DBA/Developer.
What's it all about?
Stefan Karlsson asks his guest speakers to provide their favorite tips for P&T (Performance & Tuning).
For those assuming an immediate into the relatively arcane area of MDA tables, Steve gets off to a surprising but welcome start with tips based on master..sysprocesses table - 11.5 and onwards. The tips are on using sysprocesses to determine aspects such as the most used or slowest stored procedure, and isolating to the exact line number of slowly-executing SQL. Steve lists the columns he likes to use, and this is where the disadvantages of a podcast over a webinar come in - if this is completely new to the listener, they may struggle to keep up with of names of the columns. For the novice, here's a sample query from the reviewer to get started:
select suser_name(suid), spid, db_name(dbid) as db_name,object_name(id,dbid) as obj_name, status,stmtnum, linenum,cmd, cpu, physical_io from master..sysprocesses
Rob Verschoor highlights a new feature in version 15, the ability to run minimally logged inserts into an existing table (select ... into existing table). This has been around in proxy tables for a while, but 15 is the first time we've had it for "normal" tables. Anyone running data warehouse batch loads into an ASE database will be interested. Unfortunately its not allowed in stored procedures.
Jeff Tallman speaks about readpast locking to remove contention. He gives some excellent real-world advice to improve performance by up to 400% for applications that use sequential key tables (apps grab the next "id" from a single table). There is a clear explanation of steps to implement a solution, which could be gold for a DBA in these situations.
The host speaker, Stefan, speaks about using RPCs from apps to call stored procedures, bypassing native ASE parsing and providing a shorter roundtrip to improve throughput. This is ct-lib functionality - DBAs should grasp the database developers, get them to switch off Coldplay on their headphones, and listen to this!
Our Verdict
The first two podcasts in this series on P&T were understandably introductory. It would be unfortunate if experienced DBAs didn't return, because this third podcast provides gold nuggets. Sure, some tips may be familiar (good old sysprocesses) and others may not be applicable to the listener's immediate environment, but these latter tips will be filed away in the general "back of the mind" knowledge. To this end, I hope Sybase provides some kind of indexing of the contents of the podcasts, to allow later retrieval - of course, this is another new challenge in the web 2.0 world.
Submitted: 18 Jan 2007
Author: Margaret Cruise O'Brien, MCOB Technology
(c) M.C.O.B. Technology 2007