Compaq ProLiant 1000 Configuration and Tuning of Sybase System 11 for Microsof - Page 16

Configuration and Tuning of Sybase System 11 for Microsoft Windows NT on Compaq Servers Page 13 Dbcc Memusage Dbcc Memusage reports the following very useful information: Y Sybase SQL Server memory allocation at startup Y Memory used by 20 largest objects in the data cache Y Memory used by 12 largest stored procedures, triggers, views, rules and defaults in the procedure cache Run Dbcc Memusage occasionally to determine what is and what is not in the Sybase SQL Server cache. Refer to the Sybase SQL Server System Administration Guide for more detailed information on how to use this feature. Procedure Cache The amount of procedure cache you need for your stored procedures, triggers, views, rules and defaults depends on their number and size. Keep in mind that multiple users accessing the same stored procedure, for example, will cause Sybase SQL Server to store one copy of the same stored procedure for each user. Since reading stored procedures from disk to the procedure cache is costly, you want to keep all needed stored procedures in the procedure cache. In other words, you want to prevent stored procedures not in the procedure cache from pushing out those stored procedures already in cache because your procedure cache is insufficient to hold them all. Use Dbcc Memusage to monitor the 12 largest procedures in the procedure cache. If you have more than 12 stored procedures in the procedure cache, Dbcc Memusage only shows the 12 largest ones, and you will have to get a little creative to find out if you have enough procedure cache. Below is an example. 1. Make a copy of one of your largest stored procedures, call it a different name, and execute it. This step will place this stored procedure in the procedure cache. Use Dbcc Memusage to verify this. 2. Execute the rest of your stored procedures, except for the one created in step 1, several times. 3. Use Dbcc Memusage to determine which 12 largest stored procedures remained in the procedure cache. If you have enough procedure cache, your dummy stored procedure should have remained in the list of top 12. If you don't have sufficient procedure cache, your dummy stored procedure should have aged out and has been pushed out of the procedure cache by other stored procedures. Disk Subsystem Utilization, Monitoring and Optimization This section provides information on parameters that you can set to influence performance from the disk subsystem perspective and tools you have available to monitor the behavior of the system. Recovery Interval Sybase SQL Server uses the recovery interval parameter to calculate how often to perform a checkpoint. Sybase SQL Server internally computes the frequency of the checkpoint based on the recovery interval value and the transaction rate to guarantee that in case of a system crash the database(s) would be recovered within the time specified by the recovery interval parameter. For example, in a heavy transaction processing environment, a recovery interval value of 5 (minutes) means that at the present transaction rate Sybase SQL Server would recover the database within 5 minutes after restarting from a crash. Taking into consideration the present © 1996 Compaq Computer Corporation, All Rights Reserved Doc No 143A/0596