Sybase ASE
All versions
Last updated: 24 December 2009
ASE Quiz Questions 2009
 
This page contains some quiz-type ASE questions to test your ASE knowledge.
More ASE quiz questions: 2013 - 2012 - 2011 - 2010 - 2009 - 2008 - 2007 - 2006 - 2005 - 2004 - 2003 - 2002 - 2001
For a randomly selected quiz question, click here. >


December 2009
As of version 15.0.2, ASE supports SQL User-Defined Functions (UDFs).
If you'd create an 'elapsed_time' resource limit of 10 minutes, the idea is that any T-SQL statement taking longer than 10 minutes to execute, will be aborted by the ASE Resource Governor (assuming you'd configure the limit for aborting).
While taking a fresh look at the 'elapsed_time' resource limit, with a I ran into recently, by complete coincidence -or rather, by sloppy SQL coding on my part- was the following waitfor statement:
waitfor delay '01:00:00'  -- i.e. wait for 1 hour
When you execute this waitfor while the 10-minute resource limit mentioned above is set up, after how much time do you expect the resource limit to kick in?
  • (a) after 0 seconds
  • (b) after 10 minutes
  • (c) after 1 hour
What's your guess?

Go here for the answer.

October 2009
Here's some unexpected ASE behaviour -for me, at least- that I ran into recently.
This is about the relatively little-known feature of 'resource limits', specifically about the 'elapsed_time' limit.
If you'd create an 'elapsed_time' resource limit of 10 minutes, the idea is that any T-SQL statement taking longer than 10 minutes to execute, will be aborted by the ASE Resource Governor (assuming you'd configure the limit for aborting).
While taking a fresh look at the 'elapsed_time' resource limit, with a I ran into recently, by complete coincidence -or rather, by sloppy SQL coding on my part- was the following waitfor statement:
waitfor delay '01:00:00'  -- i.e. wait for 1 hour
When you execute this waitfor while the 10-minute resource limit mentioned above is set up, after how much time do you expect the resource limit to kick in?
  • (a) after 0 seconds
  • (b) after 10 minutes
  • (c) after 1 hour
What's your guess?

Go here for the answer.

September 2009
When you want to get an idea of the locks being held in your ASE server, you run the system procedure sp_lock.
However, the results show only the object IDs for which locks are currently being held. Unless you're one of those guys who knows the object IDs of all your tables by heart (I'm definitely not in that league myself), you'd really like to see the table names reported instead.
What's the simplest way of achieving that?

Go here for the answer.

August 2009
You're an ASE DBA, and an opinionated (as well as upset) end-user calls you saying there must be an open transaction in the ASE server, blocking his application.
In a plain-vanilla ASE installation, what's the least number of keystrokes to find out if there is indeed an open transaction, and if so, how long it's been open, and what it may be doing?

Go here for the answer.

July 2009
You're probably familiar with the special significance of the "sp_" prefix in stored procedure names: when executing such a procedure, if it does not exist in the session's current database, it will be searched for in the sybsystemprocs database (and, as a remnant of older days, if it doesn't exist there either, it will be searched for in the master database).

Can this be applied beyond stored procedures?

Go here for the answer.

June 2009
Version 15.0.2 of ASE comes with the new feature of 'application tracing'. Understanding this feature is a must for every DBA, because it provides a simple mechanism to figure out what your client applications are actually doing: apptracing lets you capture the SQL submitted to the ASE server by a specific client connection, and writes it into a file.
Basically, it works as follows. Say there's an application connected to your ASE server with session number (spid #) 54, and you want to see what SQL it is actually executing. You run the following SQL:
  set tracefile '/tmp/spid54.trace.out' for 54
  set show_sqltext on
... and from here on, any SQL executed by the application on session 54 will be captured into the file /tmp/spid54.trace.out. Session 54 itself will not be affected in any way -- so this is a nice way of snooping on other sessions (yes, you need sa_role for this).

However, there's a downside: you need to wait until the application has connected to ASE before you can start application tracing for the session with the SQL shown above. This means you'll miss any SQL executed immediately after the connection is made, and for applications that very quickly disconnect again, you won't be able to use apptracing at all.

How can you overcome this limitation?

Go here for the answer.

May 2009
Back in November 2003, I posted a quiz question about finding the ASE configuration parameters that have been set to non-default values. Before proceeding, you may want to check out that question and the answer, which was indeed the best way of doing it in those days (the alternative was to go through the .cfg file and find those lines which were not set to 'DEFAULT').
Finding only the non-default config settings matters, since this is generally only a subset of the 300+ configuration parameters, and as a DBA you typically want to know the stuff that is not set to the default.

Some 5.5 years later, there's now a much better way of figuring out those non-default configuration parameters. How?

Go here for the answer.

February 2009
When you're tuning individual queries for optimal performance, you're aiming at identifying the right combination of required indexes, statistics and SQL constructs so that the ASE query optimizer will generate the query plan that appears to execute fastest.
While that sounds pretty good in theory, in practice you can find yourself in that situation where simply forcing a particular index (with (index your_index_name) behind the table in the from-clause), or join order (with set forceplan on) results in an obviously superior plan, yet you cannot get the darned optimizer to figure that out for itself (i.e. without forcing anything). Being pragmatic, it is often most cost-effective to simply use that forcing: performance problem solved, on to the next problem...

While the short-term economics of this approach are undeniably favourable (is it really worth spending another day trying to find a solution *without* forcing that index?), the trouble with using query plan forcings is that they can turn against you later. The ASE query optimizer may well have been enhanced at some point, yielding a better plan for that query, if only you hadn't twisted the optimizer's arm by forcing part of the query plan.
This is the reason for the Sybase recommendation that all those forcings should be re-evaluated 'periodically', but anyway when upgrading to the next major ASE version (see here for example: 7th bullet from bottom). However, this recommendation is all too often ignored since re-evaluating all those forcings is simply too much hassle.

Yet, the need to re-evaluate your forced indexes and join orders has in fact never been greater than when upgrading to ASE 15, where many classic do's and dont's have changed, and indeed, those forcings may very well deny your queries the opportunity of running much faster with ASE 15's new algorithms like hash joins and hash-based sorting (to name just two out of many enhancements). This is where that short-term solution from a few years ago may be costing you potential performance gains in ASE 15.

The question: what is the fastest way to test the effect of removing all forcings from your SQL code in ASE 15?

Go here for the answer.


More ASE quiz questions: 2013 - 2012 - 2011 - 2010 - 2009 - 2008 - 2007 - 2006 - 2005 - 2004 - 2003 - 2002 - 2001
For a randomly selected quiz question, click here.


 
 This document is located at www.sypron.nl/quiz2009.html