ftp.pasteur.org/FAQ/

home *** CD-ROM | disk | FTP | other *** search

/ ftp.pasteur.org/FAQ/ / ftp-pasteur-org-FAQ.zip / FAQ / databases / sybase-faq / part9 < prev

Wrap

Internet Message Format | 2004-04-21 | 22.6 KB

Path: senator-bedfellow.mit.edu!dreaderd!not-for-mail Message-ID: <databases/sybase-faq/part9_1082468590@rtfm.mit.edu> Supersedes: <databases/sybase-faq/part9_1074677126@rtfm.mit.edu> Expires: 2 Aug 2004 13:43:10 GMT References: <databases/sybase-faq/part1_1082468590@rtfm.mit.edu> X-Last-Updated: 2003/03/02 From: dowen@midsomer.org (David Owen) Newsgroups: comp.databases.sybase,comp.answers,news.answers Subject: Sybase FAQ: 9/19 - ASE Admin (6 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: 531 NNTP-Posting-Host: penguin-lust.mit.edu X-Trace: 1082468707 senator-bedfellow.mit.edu 567 18.181.0.29 Xref: senator-bedfellow.mit.edu comp.databases.sybase:106207 comp.answers:56953 news.answers:270293 Archive-name: databases/sybase-faq/part9 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.7: How much memory to configure? ------------------------------------------------------------------------------- System 10 and below. Overview At some point you'll wonder if your ASE has been configured with sufficient memory. We hope that it's not during some crisis but that's probably when it'll happen. The most important thing in setting up memory for a ASE is that it has to be large enough to accommodate: * concurrent user connections * active procedures * and concurrent open databases. By not setting the ASE up correctly it will affect the performance of it. A delicate balance needs to be struck where your ASE is large enough to accommodate the users but not too large where it adversely affects the CPU Server (such as causing swapping). Assumptions made of the reader: * The reader has some experience administering ASEs. * All queries have been tuned and that there are no unnecessary table scans. Preface As the ASE starts up, it pre-allocates its structures to support the configuration. The memory that remains after the pre-allocation phase is the available cache. The available cache is partitioned into two pieces: 1. buffer cache - data pages to be sent to a user connection or flushed to disk. 2. procedure cache - where query plans live. The idea is to determine if the buffer cache and the procedure cache are of adequate size. As a DBA you can use dbcc memusage to ascertain this. The information provided from a dbcc memusage, daunting at first, but taken in sections, is easy to understand and provides the DBA with the vital information that is necessary to determine if more memory is required and where it is required. If the procedure cache is too small, user connections will get sporadic 701's: There is insufficient system memory to run this query. If the buffer cache is too small, response time may be poor or spiky. The following text describes how to interpret the output of dbcc memusage and to correlate this back to the fundamental question: Does my ASE have enough memory? Definitions Before delving into the world of dbcc memusage some definitions to get us through. Buffer Cache (also referred to as the Data Cache) Area of memory where ASE stores the most recently used data pages and index pages in 2K page units. If ASE finds a data page or index page in the buffer cache, it doesn't need to perform a physical I/O (it is reported as a logical I/O). If a user connection selects data from a database, the ASE loads the 2K data page(s) here and then hands the information off to the user connection. If a user connection updates data, these pages are altered, and then they are flushed out to disk by the ASE. This is a bit simplistic but it'll do. Read on for more info though. The cache is maintained as a doubly linked list. The head of the list is where the most recently used pages are placed. Naturally towards the tail of the chain are the least recently used pages. If a page is requested and it is found on the chain, it is moved back to the front of the chain and the information is relayed, thus saving a physical I/ O. But wait! this recycling is not done forever. When a checkpoint occurs any dirty pages are flushed. Also, the parameter cbufwashsize determines how many times a page containing data can be recycled before it has to be flushed out to disk. For OAM and index pages the following parameters apply coamtrips and cindextrips respectively. Procedure Cache Area of memory where ASE stores the most recently used query plans of stored procedures and triggers. This procedure cache is also used by the Server when a procedure is being created and when a query is being compiled. Just like the buffer cache, if SQL Server finds a procedure or a compilation already in this cache, it doesn't need to read it from the disk. The size of procedure cache is determined by the percentage of remaining memory configured for this Server parameter after ASE memory needs are met. Available Cache When the ASE starts up it pre-allocates its data structures to support the current configuration. For example, based on the number of user connections, additional netmem, open databases and so forth the dataserver pre-allocates how much memory it requires to support these configured items. What remains after the pre-allocation is the available cache. The available cache is divided into buffer cache and procedure cache. The sp_configure "procedure cache" parameter determines the percentage breakdown. A value of 20 would read as follows: 20% of the available cache is dedicated to the procedure cache and 80% is dedicated to the buffer cache. Your pal: dbcc memusage dbcc memusage takes a snapshot of your ASE's current memory usage and reports this vital information back to you. The information returned provides information regarding the use of your procedure cache and how much of the buffer cache you are currently using. An important piece of information is the size of the largest query plan. We'll talk about that more below. It is best to run dbcc memusage after your ASE has reached a working set. For example, at the end of the day or during lunch time. Running dbcc memusage will freeze the dataserver while it does its work. The more memory you have configured for the ASE the longer it'll take. Our experience is that for a ASE with 300MB it'll take about four minutes to execute. During this time, nothing else will execute: no user queries, no sp_who's... In order to run dbcc memusage you must have sa privileges. Here's a sample execution for discussion purposes: 1> /* send the output to the screen instead of errorlog */ 2> dbcc traceon(3604) 3> go 1> dbcc memusage 2> go Memory Usage: Meg. 2K Blks Bytes Configured Memory:300.0000 153600 314572800 Code size: 2.6375 1351 2765600 Kernel Structures: 77.6262 39745 81396975 Server Structures: 54.4032 27855 57045920 Page Cache:129.5992 66355 135894640 Proc Buffers: 1.1571 593 1213340 Proc Headers: 25.0840 12843 26302464 Number of page buffers: 63856 Number of proc buffers: 15964 Buffer Cache, Top 20: DB Id Object Id Index Id 2K Buffers 6 927446498 0 9424 6 507969006 0 7799 6 959446612 0 7563 6 116351649 0 7428 6 2135014687 5 2972 6 607445358 0 2780 6 507969006 2 2334 6 2135014687 0 2047 6 506589013 0 1766 6 1022066847 0 1160 6 116351649 255 987 6 927446498 8 897 6 927446498 10 733 6 959446612 7 722 6 506589013 1 687 6 971918604 0 686 6 116351649 6 387 Procedure Cache, Top 20: Database Id: 6 Object Id: 1652357121 Object Name: lp_cm_case_list Version: 1 Uid: 1 Type: stored procedure Number of trees: 0 Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages Number of plans: 16 Size of plans: 0.323364 Mb, 339072.000000 bytes, 176 pages ---- Database Id: 6 Object Id: 1668357178 Object Name: lp_cm_subcase_list Version: 1 Uid: 1 Type: stored procedure Number of trees: 0 Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages Number of plans: 10 Size of plans: 0.202827 Mb, 212680.000000 bytes, 110 pages ---- Database Id: 6 Object Id: 132351706 Object Name: csp_get_case Version: 1 Uid: 1 Type: stored procedure Number of trees: 0 Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages Number of plans: 9 Size of plans: 0.149792 Mb, 157068.000000 bytes, 81 pages ---- Database Id: 6 Object Id: 1858261845 Object Name: lp_get_last_caller_new Version: 1 Uid: 1 Type: stored procedure Number of trees: 0 Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages Number of plans: 2 Size of plans: 0.054710 Mb, 57368.000000 bytes, 30 pages ... 1> /* redirect output back to the errorlog */ 2> dbcc traceoff(3604) 3> go Dissecting memusage output The output may appear overwhelming but it's actually pretty easy to parse. Let's look at each section. Memory Usage This section provides a breakdown of the memory configured for the ASE. Memory Usage: Meg. 2K Blks Bytes Configured Memory:300.0000 153600 314572800 Code size: 2.6375 1351 2765600 Kernel Structures: 77.6262 39745 81396975 Server Structures: 54.4032 27855 57045920 Page Cache:129.5992 66355 135894640 Proc Buffers: 1.1571 593 1213340 Proc Headers: 25.0840 12843 26302464 Number of page buffers: 63856 Number of proc buffers: 15964 The Configured Memory does not equal the sum of the individual components. It does in the sybooks example but in practice it doesn't always. This is not critical and it is simply being noted here. The Kernel Structures and Server structures are of mild interest. They can be used to cross-check that the pre-allocation is what you believe it to be. The salient line items are Number of page buffers and Number of proc buffers. The Number of proc buffers translates directly to the number of 2K pages available for the procedure cache. The Number of page buffers is the number of 2K pages available for the buffer cache. As a side note and not trying to muddle things, these last two pieces of information can also be obtained from the errorlog: ... Number of buffers in buffer cache: 63856. ... Number of proc buffers allocated: 15964. In our example, we have 15,964 2K pages (~32MB) for the procedure cache and 63,856 2K pages (~126MB) for the buffer cache. Buffer Cache The buffer cache contains the data pages that the ASE will be either flushing to disk or transmitting to a user connection. If this area is too small, the ASE must flush 2K pages sooner than might be necessary to satisfy a user connection's request. For example, in most database applications there are small edit tables that are used frequently by the application. These tables will populate the buffer cache and normally will remain resident during the entire life of the ASE. This is good because a user connection may request validation and the ASE will find the data page(s) resident in memory. If however there is insufficient memory configured, then these small tables will be flushed out of the buffer cache in order to satisfy another query. The next time a validation is requested, the tables will have to be re-read from disk in order to satisfy the request. Your performance will degrade. Memory access is easily an order of magnitude faster than performing a physical I/O. In this example we know from the previous section that we have 63,856 2K pages (or buffers) available in the buffer cache. The question to answer is, "do we have sufficient buffer cache configured?" The following is the output of the dbcc memusage regarding the buffer cache: Buffer Cache, Top 20: DB Id Object Id Index Id 2K Buffers 6 927446498 0 9424 6 507969006 0 7799 6 959446612 0 7563 6 116351649 0 7428 6 2135014687 5 2972 6 607445358 0 2780 6 507969006 2 2334 6 2135014687 0 2047 6 506589013 0 1766 6 1022066847 0 1160 6 116351649 255 987 6 927446498 8 897 6 927446498 10 733 6 959446612 7 722 6 506589013 1 687 6 971918604 0 686 6 116351649 6 387 Index Legend +-----------------------------+ | | | |-------+---------------------| | Value | Definition | |-------+---------------------| | 0 | Table data | |-------+---------------------| | 1 | Clustered index | |-------+---------------------| | 2-250 | Nonclustered | | | indexes | |-------+---------------------| | 255 | Text pages | +-----------------------------+ * To translate the DB Id use select db_name(#) to map back to the database name. * To translate the Object Id, use the respective database and use the select object_name(#) command. It's obvious that the first 10 items take up the largest portion of the buffer cache. Sum these values and compare the result to the amount of buffer cache configured. Summing the 10 items nets a result of 45,263 2K data pages. Comparing that to the number of pages configured, 63,856, we see that this ASE has sufficient memory configured. When do I need more Buffer Cache? I follow the following rules of thumb to determine when I need more buffer cache: * If the sum of all the entries reported is equal to the number of pages configured and all entries are relatively the same size. Crank it up. * Note the natural groupings that occur in the example. If the difference between any of the groups is greater than an order of magnitude I'd be suspicious. But only if the sum of the larger groups is very close to the number of pages configured. Procedure Cache If the procedure cache is not of sufficient size you may get sporadic 701 errors: There is insufficient system memory to run this query. In order to calculate the correct procedure cache one needs to apply the following formula (found in ASE Troubleshooting Guide - Chapter 2, Procedure Cache Sizing): proc cache size = max(# of concurrent users) * (size of the largest plan) * 1.25 The flaw with the above formula is that if 10% of the users are executing the largest plan, then you'll overshoot. If you have distinct classes of connections whose largest plans are mutually exclusive then you need to account for that: ttl proc cache = proc cache size * x% + proc cache size * y% ... The max(# of concurrent users) is not the number of user connections configured but rather the actual number of connections during the peak period. To compute the size of the largest [query] plan take the results from the dbcc memusage's, Procedure Cache section and apply the following formula: query plan size = [size of plans in bytes] / [number of plans] We can compute the size of the query plan for lp_cm_case_list by using the output of the dbcc memusage: ... Database Id: 6 Object Id: 1652357121 Object Name: lp_cm_case_list Version: 1 Uid: 1 Type: stored procedure Number of trees: 0 Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages Number of plans: 16 Size of plans: 0.323364 Mb, 339072.000000 bytes, 176 pages ---- ... Entering the respective numbers, the query plan size for lp_cm_case_list is 21K: query plan size = 339072 / 16 query plan size = 21192 bytes or 21K The formula would be applied to all objects found in the procedure cache and the largest value would be plugged into the procedure cache size formula: Query Plan Sizes +--------------------------------+ | | | |------------------------+-------| | | Query | | Object | Plan | | | Size | |------------------------+-------| | lp_cm_case_list | 21K | |------------------------+-------| | lp_cm_subcase_list | 21K | |------------------------+-------| | csp_get_case | 19K | |------------------------+-------| | lp_get_last_caller_new | 28K | +--------------------------------+ The size of the largest [query] plan is 28K. Entering these values into the formula: proc cache size = max(# of concurrent users) * (size of the largest plan) * 1.25 proc cache size = 491 connections * 28K * 1.25 proc cache size = 17,185 2K pages required Our example ASE has 15,964 2K pages configured but 17,185 2K pages are required. This ASE can benefit by having more procedure cache configured. This can be done one of two ways: 1. If you have some headroom in your buffer cache, then sp_configure "procedure cache" to increase the ratio of procedure cache to buffer cache or procedure cache = [ proposed procedure cache ] / ( [ current procedure cache ] + [ current buffer cache ] ) The new procedure cache would be 22%: procedure cache = 17,185 / ( 15,964 + 63,856 ) procedure cache = .2152 or 22% 2. If the buffer cache cannot be shrunken, then sp_configure "memory" to increase the total memory: mem size = ([ proposed procedure cache ]) / ([ current procedure cache ] / [ current configured memory ]) The new memory size would be 165,399 2K pages, assuming that the procedure cache is unchanged: mem size = 17,185 / ( 15,964 / 153,600 ) mem size = 165,399 2K pages Back to top ------------------------------------------------------------------------------- 1.5.8: Why should I use stored procedures? ------------------------------------------------------------------------------- There are many advantages to using stored procedures (unfortunately they do not handle the text/image types): * Security - you can revoke access to the base tables and only allow users to access and manipulate the data via the stored procedures. * Performance - stored procedures are parsed and a query plan is compiled. This information is stored in the system tables and it only has to be done once. * Network - if you have users who are on a WAN (slow connection) having stored procedures will improve throughput because less bytes need to flow down the wire from the client to ASE. * Tuning - if you have all your SQL code housed in the database, then it's easy to tune the stored procedure without affecting the clients (unless of course the parameter change). * Modularity - during application development, the application designer can concentrate on the front-end and the DB designer can concentrate on the ASE. * Network latency - a client on a LAN may seem slower if it is sending large numbers of separate requests to a database server, bundling them into one procedure call may improve responsiveness. Also, servers handling large numbers of small requests can spend a surprising amount of CPU time performing network IO. * Minimise blocks and deadlocks - it is a lot easier to handle a deadlock if the entire transaction is performed in one database request, also locks will be held for a shorter time, improving concurrency and potentially reducing the number of deadlocks. Further, it is easier to ensure that all tables are accessed in a consistent order if code is stored centrally rather than dispersed among a number of apps. Back to top -------------------------------------------------------------------------------