Archive-name: databases/sybase-faq/part10 URL: http://www.isug.com/Sybase_FAQ Version: 1.7 Maintainer: David Owen Last-modified: 2003/01/17 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 <pbname> 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 ------------------------------------------------------------------------------- Platform Specific Issues - Solaris General Troubleshooting ASE FAQ -- - David Alex Lamb, one of the *.answers moderators news-answers-request@mit.edu
Закладки на сайте Проследить за страницей |
Created 1996-2024 by Maxim Chirkov Добавить, Поддержать, Вебмастеру |