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

Page 12 Configuration and Tuning of Sybase System 11 for Microsoft Windows NT on Compaq Servers Object: Memory Counter: Page Faults/sec This counter monitors total page faults by all running processes. A page fault occurs when a process makes a reference to a virtual memory page that is not in its working set in the memory. You can also monitor Page Faults/sec per individual process, such as the number of page faults per second Sybase SQL Server generates. On a dedicated Sybase SQL Server system, Sybase SQL Server-generated page faults and total page faults per second will often be identical. You should have very little or no paging once the system reaches a steady state; occasional paging prior to steady state is often acceptable. Sybase SQL Server Tuning Parameters Memory The correct amount of memory allocated to Sybase SQL Server varies from one environment to another. As a rule of a thumb, the more objects (tables, indexes, etc.) you can cache, the better performance you will have. If you can't cache them all, attempt to cache the more frequently accessed objects in the following priority clustered indexes, non-clustered indexes and data pages. The Sybase Logical Memory Manager feature in System 11 permits the refinement of which database objects you want to cache, how much space to allocate for the cache, and how to manage the cache. To determine the initial size of a dedicated named cache, use the following procedure. 1. Use sp_spaceused to determine the size of the database object 2. Edit the server.cfg file to establish the dedicated named cache object for the database object [Named Cache:c_tempdb] cache size = 2M cache status = mixed cache cache status = HK ignore cache [2K I/O Buffer Pool] pool size = 2M wash size = 512K 3. Use sp_bindcache to associate the database object to the dedicated named cache 4. Use sp_helpcache to verify the cache binding Please refer to the Sybase SQL Server System Administration Guide for more in depth information on the tuning of the LMM. Dbcc Memusage8 is a very useful tool in monitoring what objects are in the Sybase SQL Server cache. Remember that out of the total memory allocated to Sybase SQL Server, the Sybase SQL Server engine itself will allocate enough memory for itself and its required data structures, and then will divide the remaining memory between the procedure cache and data cache according to the procedure cache percent parameter. Also remember that Sybase SQL Server allocates memory in 2 Kbyte pages. 8 For accurate results, set the database to single user mode before issuing this command. Also it maybe necessary to issue the dbcc traceon (3604) command to redirect the output to your command window instead of the Sybase SQL Server errorlog. © 1996 Compaq Computer Corporation, All Rights Reserved Doc No 143A/0596