Path: senator-bedfellow.mit.edu!dreaderd!not-for-mail Message-ID: Supersedes: Expires: 2 Aug 2004 13:43:10 GMT References: X-Last-Updated: 2003/03/02 From: dowen@midsomer.org (David Owen) Newsgroups: comp.databases.sybase,comp.answers,news.answers Subject: Sybase FAQ: 10/19 - ASE Admin (7 of 7) Reply-To: dowen@midsomer.org (David Owen) Followup-To: comp.databases.sybase Distribution: world Organization: Midsomer Consultants Inc. Approved: news-answers-request@MIT.EDU Keywords: FAQ, DATABASES, SYBASE, ASA, ASE, REP Originator: faqserv@penguin-lust.MIT.EDU Date: 20 Apr 2004 13:45:07 GMT Lines: 291 NNTP-Posting-Host: penguin-lust.mit.edu X-Trace: 1082468707 senator-bedfellow.mit.edu 568 18.181.0.29 Xref: senator-bedfellow.mit.edu comp.databases.sybase:106208 comp.answers:56954 news.answers:270294 Archive-name: databases/sybase-faq/part10 URL: http://www.isug.com/Sybase_FAQ Version: 1.7 Maintainer: David Owen Last-modified: 2003/03/02 Posting-Frequency: posted every 3rd month A how-to-find-the-FAQ article is posted on the intervening months. 1.5.9: You and showplan output ------------------------------------------------------------------------------- As recently pointed out in the Sybase-L list, the showplan information that was here is terribly out of date. It was written back when the output from ASE and MS SQL Server were identical. (To see just how differenet they have become, have a look at the O'Reilly book "Transact-SQL Programming". It does a line for line comparison.) The write up in the Performance and Tuning Guide is excellent, and this section was doing nothing but causing problems. If you do have a need for the original document, then it can be found here, but it will no longer be considered part of the official FAQ. Back to top ------------------------------------------------------------------------------- 1.5.10: Poor man's sp_sysmon ------------------------------------------------------------------------------- This is needed for System 10 and Sybase 4.9.2 where there is no sp_sysmon command available. Fine tune the waitfor for your application. You may need TS Role -- see Q3.1. use master go dbcc traceon(3604) dbcc monitor ("clear", "all", "on") waitfor delay "00:01:00" dbcc monitor ("sample", "all", "on") dbcc monitor ("select", "all", "on") dbcc traceon(8399) select field_name, group_name, value from sysmonitors dbcc traceoff(8399) go dbcc traceoff(3604) go Back to top ------------------------------------------------------------------------------- 1.5.11: View MRU-LRU procedure cache chain ------------------------------------------------------------------------------- dbcc procbuf gives a listing of the current contents of the procedure cache. By repeating the process at intervals it is possible to watch procedures moving down the MRU-LRU chain, and so to see how long procedures remain in cache. The neat thing about this approach is that you can size your cache according to what is actually happening, rather than relying on estimates based on assumptions that may not hold on your site. To run it: dbcc traceon(3604) go dbcc procbuf go If you use sqsh it's a bit easier to grok the output: dbcc traceon(3604); dbcc procbuf;|fgrep See Q1.5.7 regarding procedure cache sizing. Back to top ------------------------------------------------------------------------------- 1.5.12: Improving Text/Image Type Performance ------------------------------------------------------------------------------- If you know that you are going to be using a text/insert column immediately, insert the row setting the column to a non-null value. There's a noticeable performance gain. Unfortunately, text and image datatypes cannot be passed as parameters to stored procedures. The address of the text or image location must be created and returned where it is then manipulated by the calling code. This means that transactions involving both text and image fields and stored procedures are not atomic. However, the datatypes can still be declared as not null in the table definition. Given this example - create table key_n_text ( key int not null, notes text not null ) This stored procedure can be used - create procedure sp_insert_key_n_text @key int, @textptr varbinary(16) output as /* ** Generate a valid text pointer for WRITETEXT by inserting an ** empty string in the text field. */ insert key_n_text ( key, notes ) values ( @key, "" ) select @textptr = textptr(notes) from key_n_text where key = @key return 0 go The return parameter is then used by the calling code to update the text field, via the dbwritetext() function if using DB-Library for example. Back to top ------------------------------------------------------------------------------- Server Monitoring General Troubleshooting ASE FAQ Server Monitoring 1.6.1 What is Monitor Server and how do I configure it? 1.6.2 OK, that was easy, how do I configure a client? Platform Specific Issues - Solaris Performance and Tuning ASE FAQ ------------------------------------------------------------------------------- 1.6.1: How do I configure Monitor Server? ------------------------------------------------------------------------------- Monitor Server is a separate server from the normal dataserver. Its purpose, as the name suggests, is to monitor ASE. It uses internal counters to determine what is happening. On its own, it does not actually do a lot. You need to hook up a client of some sort in order to be able to view the results. Configuration is easy. The Sybase documentation is very good on this one for either Unix or NT. Rather than repeat myself, go to the Sybase web site and check out the Monitor Server User's Guide. Obviously the link should take you to the HTML edition of the book. There is also a PDF available. Look for "monbook.pdf". If Sybase has skipped to ASE 99.9 and this link no longer works, then you will have to go search the Sybase home pages. Back to top ------------------------------------------------------------------------------- 1.6.2: OK, that was easy, how do I configure a client? ------------------------------------------------------------------------------- I see that you like a challenge! Syase offer a Java client to view the output from Monitor Server. It is accessible either standalone or via the Win32 edition of Sybase Central. Standalone on NT/2000 I could not find anything about setting up the clients in the standard documentation set. However, there is a small paper on it here (towards the bottom). It does miss out a couple of important details, but is helpful for all that. I did not try too hard to get the 11.9.2 version running, since the 12.5 version will monitor 11.9 servers. I do not have a boxed release of ASE 12.5 for NT, just the developers release. This does not come with all of the necessary files. In order to run the Monitor Client, you will need the PC Client CD that came with the boxed release. If all you have is the developer's edition, you might be stuck. It would be worth getting in touch with Sybase to see if they could ship you one. There is probably a charge! You will need to install the client software. If you have a release of ASE already installed and running you might want to install this into a separate area. I am not sure what files it includes and versions etc, but if you have the space I recommend saving yourself some hassle. If you have an older edition of ASE installed, the installation will ask if you want to overwrite two files, mclib.dll and mchelp.dll, both of which should reside in your winnt/system32 directory. It is important that you accept both of the overwrites. The older versions of these files do not seem to work. Once installed, you will also need to spend some time playing with environment variables. I have got 3 editions of ASE all running successfully on the one machine (see Q1.3.9). I chose to have one user for each ASE instance, each with their own local environment variables pointing to the relevant installation for them, plus a generic account for my main user that I configured to use the software installed from the client CD. I adjusted the variables so that each user had their own set of variables and all of the installations worked OK. Next, you need a copy of Java 1.1.8 installed. The client CD has a copy of JDK 1.1.8 in the "ASEP_Win32" directory. This is the one to go for, as I am sure that it was the one that the Monitor Client was built with. I did try a version from Sun's Java archive, but it failed. Next, set up the JAVA_HOME environment variable. If you installed the JDK into its default location, that will be C:\jdk1.1.8. Check to ensure that your CLASSPATH is defined as (assuming that you installed the client into C:\Sybase_Client): C:\Sybase_Client\ASEP_Win32\monclass.zip;C:\Sybase_Client\ASEP_Win32\3pclass.zip;%JAVA_HOME%\lib\rt.jar You may want to check that the files mclib.dll and mchelp.dll exist in your winnt/system32 directory if you were not asked to replace them earlier. You may also want to check that the defauly Java command is correct with java -version. It should return java version "1.1.8" You should now be able to fire up the main window with: java sybase.monclt.mcgui.procact.ProcActApp 12.5 sa "sa_password" en 0 sccsen.hlp (The paper says that you should use "jre" and not "java". That gives me a cosistent "Class not found...". I do not know why.) You should be presented with a screen like this, which will fill with process information after 10 seconds. Choose "File->Monitors >" to choose a monitoring graph. Here are a couple of screenshots from various monitors: * Performance Summary * Performance Trends... * Process Current SQL Statement * Network Activity Obviously, all of this can be set from the command line or via a batch script. Shove the following into a file called mon.bat and invoke using mon ASE_SERVER MON_SERVER PASSWORD SET JAVA_HOME=C:\JDK1.1.8 SET PATH=%JAVA_HOME%\bin;%PATH% SET CLASSPATH=C:\SYBASE_CLIENT\ASEP_Win32\monclass.zip;C:\SYBASE_CLIENT\ASEP_Win32\3pclass.zip java sybase.monclt.mcgui.procact.ProcActApp %1 12.5 %2 sa "%3" en 0 scssen.hlp Obviously, you will need to replace "C:\SYBASE_CLIENT" with the correct string pointing to your Sybase ASE installation. Via Sybase Central on NT/2000 You will need to have installed the version of the Java Development Kit that comes with your CD, as per standalone installation. Next, create a shortcut to the file %SYBASE%\Sybase Central 3.2\win32\scview.exe. This is the Win 32 version of Sybase Central. Next, edit the shortcut's properties (right click on the shortcut and select "Properties"). Now, edit the "Start In" field to be "C: \jdk1.1.8\bin", assuming that you installed the JDK into its default location. Now, assuming that both the ASE and Monitor servers are running, start up this version of Sybase Central. Unlike the Java edition, all of the Servers from the SQL.INI file are displayed at startup. Right click on the ASE server you wish to monitor and select "Properties". This brings up a triple tabbed screen. Select the "Monitor Server" tab and use the drop down to select the appropriate monitor server. Now, connect to the ASE server and you will see another level in the options tree called "Monitors". Click on it and you should see a complete list of the monitors you can choose from. Double clicking on one should display it. The output is exactly the same as for standalone operation. Back to top ------------------------------------------------------------------------------- Platform Specific Issues - Solaris Performance and Tuning ASE FAQ