Sybase FAQ: 9/16 - section 8

From: news_reader@mew.corp.sgi.com (Pablo Sanchez) Newsgroups: comp.databases.sybase,comp.answers,news.answers Subject: Sybase FAQ: 9/16 - section 8 Supersedes: <FAQ.section_8_868292146@sgi.com> Followup-To: comp.databases.sybase Date: 1 Aug 1997 06:02:07 GMT Organization: Silicon Graphics, Inc. Nederland, CO. USA Message-ID: <FAQ.section_8_870415202@sgi.com> References: <FAQ.section_7_870415202@sgi.com> Reply-To: pablo@sgi.com Summary: Info about SQL Server, bcp, isql and other goodies Posting-Frequency: monthly Archive-name: databases/sybase-faq/part9 URL: http://reality.sgi.com/pablo/Sybase_FAQ Q8.1: SYBASE SQL SERVER PERFORMANCE AND TUNING _________________________________________________________________ All Components Affect Response Time & Throughput We often think that high performance is defined as a fast data server, but the picture is not that simple. Performance is determined by all these factors: * The client application itself: + How efficiently is it written? + We will return to this later, when we look at application tuning. * The client-side library: + What facilities does it make available to the application? + How easy are they to use? * The network: + How efficiently is it used by the client/server connection? * The DBMS: + How effectively can it use the hardware? + What facilities does it supply to help build efficient fast applications? * The size of the database: + How long does it take to dump the database? + How long to recreate it after a media failure? Unlike some products which aim at performance on paper, Sybase aims at solving the multi-dimensional problem of delivering high performance for real applications. _OBJECTIVES_ To gain an overview of important considerations and alternatives for the design, development, and implementation of high performance systems in the Sybase client/server environment. The issues we will address are: * Client Application and API Issues * Physical Database Design Issues * Networking Issues * Operating System Configuration Issues * Hardware Configuration Issues * SQL Server Configuration Issues _Client Application and Physical Database Design design decisions will account for over 80% of your system's "tuneable" performance so ... plan your project resources accordingly ! _ It is highly recommended that every project include individuals who have taken Sybase Education's Performance and Tuning course. This 5-day course provides the hands-on experience essential for success. Client Application Issues * Tuning Transact-SQL Queries * Locking and Concurrency * ANSI Changes Affecting Concurrency * Application Deadlocking * Optimizing Cursors in v10 * Special Issues for Batch Applications * Asynchronous Queries * Generating Sequential Numbers * Other Application Issues Tuning Transact-SQL Queries * Learn the Strengths and Weaknesses of the Optimizer * One of the largest factors determining performance is TSQL! Test not only for efficient plans but also semantic correctness. * Optimizer will cost every permutation of accesses for queries involving 4 tables or less. Joins of more than 4 tables are "planned" 4-tables at a time (as listed in the FROM clause) so not all permutations are evaluated. You can influence the plans for these large joins by the order of tables in the FROM clause. * Avoid the following, if possible: + What are SARGS? This is short for search arguments. A search argument is essentially a constant value such as: o "My company name" o 3448 but not: o 344 + 88 o like "%what you want%" + Mathematical Manipulation of SARGs SELECT name FROM employee WHERE salary * 12 > 100000 + Use of Incompatible Datatypes Between Column and its _SARG_ Float &Int, Char &Varchar, Binary & Varbinary are Incompatible; Int &Intn (allow nulls) OK + Use of multiple "OR" Statements - especially on different columns in same table. If any portion of the OR clause requires a table scan, it will! OR Strategy requires additional cost of creating and sorting a work table. + Not using the leading portion of the index (unless the query is completely covered) + Substituting "OR" with "IN (value1, value2, ... valueN) Optimizer automatically converts this to an "OR" + Use of Non-Equal Expressions (!=) in WHERE Clause. * Use Tools to Evaluate and Tune Important/Problem Queries + Use the "set showplan on" command to see the plan chosen as "most efficient" by optimizer. Run all queries through during development and testing to ensure accurate access model and known performance. Information comes through the Error Handler of a DB-Library application. + Use the "dbcc traceon(3604, 302, 310)" command to see each alternative plan evaluated by the optimizer. Generally, this is only necessary to understand why the optimizer won't give you the plan you want or need (or think you need)! + Use the "set statistics io on" command to see the number of logical and physical i/o's for a query. Scrutinize those queries with high logical i/o's. + Use the "set statistics time on" command to see the amount of time (elapsed, execution, parse and compile) a query takes to run. + If the optimizer turns out to be a "pessimizer", use the "set forceplan on" command to change join order to be the order of the tables in the FROM clause. + If the optimizer refuses to select the proper index for a table, you can force it by adding the index id in parentheses after the table name in the FROM clause. SELECT * FROM orders(2), order_detail(1) WHERE ... _This may cause portability issues should index id's vary/change by site ! _ Locking and Concurrency * The Optimizer Decides on Lock Type and Granularity * Decisions on lock type (share, exclusive, or update) and granularity (page or table) are made during optimization so make sure your updates and deletes don't scan the table ! * Exclusive Locks are Only Released Upon Commit or Rollback * Lock Contention can have a large impact on both throughput and response time if not considered both in the application and database design ! * Keep transactions as small and short as possible to minimize blocking. Consider alternatives to "mass" updates and deletes such as a v10.0 cursor in a stored procedure which frequently commits. * Never include any "user interaction" in the middle of transactions. * Shared Locks Generally Released After Page is Read * Share locks "roll" through result set for concurrency. Only "HOLDLOCK" or "Isolation Level 3" retain share locks until commit or rollback. Remember also that HOLDLOCK is for read-consistency. It doesn't block other readers ! * Use optimistic locking techniques such as timestamps and the tsequal() function to check for updates to a row since it was read (rather than holdlock) ANSI Changes Affecting Concurrency * Chained Transactions Risk Concurrency if Behavior not Understood * Sybase defaults each DML statement to its own transaction if not specified ; * ANSI automatically begins a transaction with any SELECT, FETCH, OPEN, INSERT, UPDATE, or DELETE statement ; * If Chained Transaction must be used, extreme care must be taken to ensure locks aren't left held by applications unaware they are within a transaction! This is especially crucial if running at Level 3 Isolation * Lock at the Level of Isolation Required by the Query * Read Consistency is NOT a requirement of every query. * Choose level 3 only when the business model requires it * Running at Level 1 but selectively applying HOLDLOCKs as needed is safest * If you must run at Level 3, use the NOHOLDLOCK clause when you can ! * Beware of (and test) ANSI-compliant third-party applications for concurrency Application Deadlocking Prior to SQL Server 10 cursors, many developers simulated cursors by using two or more connections (dbproc's) and divided the processing between them. Often, this meant one connection had a SELECT open while "positioned" UPDATEs and DELETEs were issued on the other connection. The approach inevitably leads to the following problem: 1. Connection A holds a share lock on page X (remember "Rows Pending" on SQL Server leave a share lock on the "current" page). 2. Connection B requests an exclusive lock on the same page X and waits... 3. The APPLICATION waits for connection B to succeed before invoking whatever logic will remove the share lock (perhaps dbnextrow). Of course, that never happens ... Since Connection A never requests a lock which Connection B holds, this is NOT a true server-side deadlock. It's really an "application" deadlock ! Design Alternatives 1. Buffer additional rows in the client that are "nonupdateable". This forces the shared lock onto a page on which the application will not request an exclusive lock. 2. Re-code these modules with CT-Library cursors (aka. server-side cursors). These cursors avoid this problem by disassociating command structures from connection structures. 3. Re-code these modules with DB-Library cursors (aka. client-side cursors). These cursors avoid this problem through buffering techniques and re-issuing of SELECTs. Because of the re-issuing of SELECTs, these cursors are not recommended for high transaction sites ! Optimizing Cursors with v10.0 * Always Declare Cursor's Intent (i.e. Read Only or Updateable) * Allows for greater control over concurrency implications * If not specified, SQL Server will decide for you and usually choose updateable * Updateable cursors use UPDATE locks preventing other U or X locks * Updateable cursors that include indexed columns in the update list may table scan * SET Number of Rows for each FETCH * Allows for greater Network Optimization over ANSI's 1- row fetch * Rows fetched via Open Client cursors are transparently buffered in the client: FETCH -> Open Client < N rows Buffers * Keep Cursor Open on a Commit / Rollback * ANSI closes cursors with each COMMIT causing either poor throughput (by making the server re-materialize the result set) or poor concurrency (by holding locks) * Open Multiple Cursors on a Single Connection * Reduces resource consumption on both client and Server * Eliminates risk of a client-side deadlocks with itself Special Issues for Batch Applications SQL Server was not designed as a batch subsystem! It was designed as an RBDMS for large multi-user applications. Designers of batch-oriented applications should consider the following design alternatives to maximize performance : Design Alternatives : * Minimize Client/Server Interaction Whenever Possible * Don't turn SQL Server into a "file system" by issuing single table / single row requests when, in actuality, set logic applies. * Maximize TDS packet size for efficient Interprocess Communication (v10 only) * New SQL Server 10.0 cursors declared and processed entirely within stored procedures and triggers offer significant performance gains in batch processing. * Investigate Opportunities to Parallelize Processing * Breaking up single processes into multiple, concurrently executing, connections (where possible) will outperform single streamed processes everytime. * Make Use of TEMPDB for Intermediate Storage of Useful Data Asynchronous Queries Many, if not most, applications and 3rd Party tools are coded to send queries with the DB-Library call dbsqlexec( ) which is a synchronous call ! It sends a query and then waits for a response from SQL Server that the query has completed ! Designing your applications for asynchronous queries provides many benefits: 1. A "Cooperative" multi-tasking application design under Windows will allow users to run other Windows applications while your long queries are processed ! 2. Provides design opportunities to parallize work across multiple SQL Server connections. Implementation Choices: * System 10 Client Library Applications: * True asynchronous behaviour is built into the entire library. Through the appropriate use of call-backs, asynchronous behavior is the normal processing paradigm. * Windows DB-Library Applications (not true async but polling for data): * Use dbsqlsend(), dbsqlok(), and dbdataready() in conjunction with some additional code in WinMain() to pass control to a background process. Code samples which outline two different Windows programming approaches (a PeekMessage loop and a Windows Timer approach) are available in the Microsoft Software Library on Compuserve (GO MSL). Look for _SQLBKGD.ZIP_ * Non-PC DB-Library Applications (not true async but polling for data): * Use dbsqlsend(), dbsqlok(), and dbpoll() to utilize non-blocking functions. Generating Sequential Numbers Many applications use unique sequentially increasing numbers, often as primary keys. While there are good benefits to this approach, generating these keys can be a serious contention point if not careful. For a complete discussion of the alternatives, download Malcolm Colton's White Paper on Sequential Keys from the SQL Server Library of our OpenLine forum on Compuserve. The two best alternatives are outlined below. 1. "Primary Key" Table Storing Last Key Assigned + Minimize contention by either using a seperate "PK" table for each user table or padding out each row to a page. Make sure updates are "in-place". + Don't include the "PK" table's update in the same transaction as the INSERT. It will serialize the transactions. _BEGIN TRAN_ UPDATE pk_table SET nextkey = nextkey + 1 [WHERE table_name = @tbl_name] _COMMIT TRAN_ /* Now retrieve the information */ SELECT nextkey FROM pk_table WHERE table_name = @tbl_name] + "Gap-less" sequences require additional logic to store and retrieve rejected values 2. IDENTITY Columns (v10.0 only) + Last key assigned for each table is stored in memory and automatically included in all INSERTs (BCP too). This should be the method of choice for performance. + Choose a large enough numeric or else all inserts will stop once the max is hit. + Potential rollbacks in long transactions may cause gaps in the sequence ! Other Application Issues * Transaction Logging Can Bottleneck Some High Transaction Environments * Committing a Transaction Must Initiate a Physical Write for Recoverability * Implementing multiple statements as a transaction can assist in these environment by minimizing the number of log writes (log is flushed to disk on commits). * Utilizing the Client Machine's Processing Power Balances Load * Client/Server doesn't dictate that everything be done on Server! * Consider moving "presentation" related tasks such as string or mathematical manipulations, sorting, or, in some cases, even aggregating to the client. * Populating of "Temporary" Tables Should Use "SELECT _INTO"_ - balance this with dynamic creation of temporary tables in an OLTP environment. Dynamic creation may cause blocks in your tempdb. * "SELECT INTO" operations are not logged and thus are significantly faster than there INSERT with a nested SELECT counterparts. * Consider Porting Applications to Client Library Over Time * True Asynchronous Behavior Throughout Library * Array Binding for SELECTs * Dynamic SQL * Support for ClientLib-initiated callback functions * Support for Server-side Cursors * Shared Structures with Server Library (Open Server 10) Physical Database Design Issues * Normalized -vs- Denormalized Design * Index Selection * Promote "Updates-in-Place" Design * Promote Parallel I/O Opportunities Normalized -vs- Denormalized * Always Start with a Completely Normalized Database * Denormalization should be an optimization taken as a result of a performance problem * Benefits of a normalized database include : 1. Accelerates searching, sorting, and index creation since tables are narrower 2. Allows more clustered indexes and hence more flexibility in tuning queries, since there are more tables ; 3. Accelerates index searching since indexes tend to be narrower and perhaps shorter ; 4. Allows better use of segments to control physical placement of tables ; 5. Fewer indexes per table, helping UPDATE, INSERT, and DELETE performance ; 6. Fewer NULLs and less redundant data, increasing compactness of the database ; 7. Accelerates trigger execution by minimizing the extra integrity work of maintaining redundant data. 8. Joins are Generally Very Fast Provided Proper Indexes are Available 9. Normal caching and cindextrips parameter (discussed in Server section) means each join will do on average only 1-2 physical I/Os. 10. Cost of a logical I/O (get page from cache) only 1-2 milliseconds. There Are Some Good Reasons to Denormalize 1. All queries require access to the "full" set of joined data. 2. Majority of applications scan entire tables doing joins. 3. Computational complexity of derived columns require storage for SELECTs 4. Others ... Index Selection * Without a clustered index, all INSERTs and "out-of-place" UPDATEs go to the last page. The lock contention in high transaction environments would be prohibitive. This is also true for INSERTs to a clustered index on a monotonically increasing key. * High INSERT environments should always cluster on a key which provides the most "randomness" (to minimize lock / device contention) that is usable in many queries. Note this is generally not your primary key ! * Prime candidates for clustered index (in addition to the above) include : + Columns Accessed by a Range + Columns Used with Order By, Group By, or Joins * Indexes Help SELECTs and Hurt INSERTs * Too many indexes can significantly hurt performance of INSERTs and "out-of-place" UPDATEs. * Prime candidates for nonclustered indexes include : + Columns Used in Queries Requiring Index Coverage + Columns Used to Access Less than 20% (rule of thumb) of the Data. * Unique indexes should be defined as UNIQUE to help the optimizer * Minimize index page splits with Fillfactor (helps concurrency and minimizes deadlocks) * Keep the Size of the Key as Small as Possible * Accelerates index scans and tree traversals * Use small datatypes whenever possible . Numerics should also be used whenever possible as they compare faster than strings. Promote "Update-in-Place" Design * "Update-in-Place" Faster by Orders of Magnitude * Performance gain dependent on number of indexes. Recent benchmark (160 byte rows, 1 clustered index and 2 nonclustered) showed 800% difference! * Alternative ("Out-of-Place" Update) implemented as a physical DELETE followed by a physical INSERT. These tactics result in: 1. Increased Lock Contention 2. Increased Chance of Deadlock 3. Decreased Response Time and Throughput * Currently (System 10 and below), Rules for "Update-in-Place" Behavior Include : 1. Columns updated can not be variable length or allow nulls 2. Columns updated can not be part of an index used to locate the row to update 3. No update trigger on table being updated (because the inserted and deleted tables used in triggers get their data from the log) In v4.9.x and below, only one row may be affected and the optimizer must know this in advance by choosing a UNIQUE index. System 10 eliminated this limitation. Promote Parallel I/O Opportunities * For I/O-bound Multi-User Systems, Use A lot of Logical and Physical Devices * Plan balanced separation of objects across logical and physical devices. * Increased number of physical devices (including controllers) ensures physical bandwidth * Increased number of logical Sybase devices ensures minimal contention for internal resources. Look at SQL Monitor's Device I/O Hit Rate for clues. Also watch out for the 128 device limit per database. * Create Database (in v10) starts parallel I/O on up to 6 devices at a time concurrently. If taken advantage of, expect an 800% performance gain. A 2Gb TPC-B database that took 4.5 hours under 4.9.1 to create now takes 26 minutes if created on 6 independent devices ! * Use Sybase Segments to Ensure Control of Placement This is the only way to guarantee logical seperation of objects on devices to reduce contention for internal resources. * Dedicate a seperate physical device and controller to the transaction log in tempdb too. * optimize TEMPDB Also if Heavily Accessed * increased number of logical Sybase devices ensures minimal contention for internal resources. * systems requiring increased log throughput today must partition database into separate databases Breaking up one logical database into multiple smaller databases increases the number number of transaction logs working in parallel. Networking Issues * Choice of Transport Stacks * Variable Sized TDS Packets * TCP/IP Packet Batching Choice of Transport Stacks for PCs * Choose a Stack that Supports "Attention Signals" (aka. "Out of Band Data") * Provides for the most efficient mechanism to cancel queries. * Essential for sites providing ad-hoc query access to large databases. * Without "Attention Signal" capabilities (or the urgent flag in the connection string), the DB-Library functions DBCANQUERY ( ) and DBCANCEL ( ) will cause SQL Server to send all rows back to the Client DB-Library as quickly as possible so as to complete the query. This can be very expensive if the result set is large and, from the user's perspective, causes the application to appear as though it has hung. * With "Attention Signal" capabilities, Net-Library is able to send an out-of-sequence packet requesting the SQL Server to physically throw away any remaining results providing for instantaneous response. * Currently, the following network vendors and associated protocols support the an "Attention Signal" capable implementation: 1. NetManage NEWT 2. FTP TCP 3. Named Pipes (10860) - Do not use urgent parameter with this Netlib 4. Novell LAN Workplace v4.1 0 Patch required from Novell 5. Novell SPX - Implemented internally through an "In-Band" packet 6. Wollongong Pathway 7. Microsoft TCP - Patch required from Microsoft Variable-sized TDS Packets Pre-v4.6 TDS Does Not Optimize Network Performance Current SQL Server TDS packet size limited to 512 bytes while network frame sizes are significantly larger (1508 bytes on Ethernet and 4120 bytes on Token Ring). The specific protocol may have other limitations! For example: * IPX is limited to 576 bytes in a routed network. * SPX requires acknowledgement of every packet before it will send another. A recent benchmark measured a 300% performance hit over TCP in "large" data transfers (small transfers showed no difference). * Open Client Apps can "Request" a Larger Packet Shown to have significant performance improvement on "large" data transfers such as BCP, Text / Image Handling, and Large Result Sets. + clients: o isql -Usa -Annnnn o bcp -Usa -Annnnn o ct_con_props (connection, CS_SET, CS_PACKETSIZE, &packetsize, sizeof(packetsize), NULL) + An "SA" must Configure each Servers' Defaults Properly o sp_configure "default packet size", nnnnn - Sets default packet size per client connection (defaults to 512) o sp_configure "maximum packet size", nnnnn - Sets maximum TDS packet size per client connection (defaults to 512) o sp_configure "additional netmem", nnnnn - Additional memory for large packets taken from separate pool. This memory does not come from the sp_configure memory setting. Optimal value = ((# connections using large packets large packetsize * 3) + an additional 1-2% of the above calculation for overhead) Each connection using large packets has 3 network buffers: one to read; one to write; and one overflow. # Default network memory - Default-sized packets come from this memory pool. # Additional Network memory - Big packets come this memory pool. If not enough memory is available in this pool, the server will give a smaller packet size, down to the default TCP/IP Packet Batching * TCP Networking Layer Defaults to "Packet Batching" * This means that TCP/IP will batch small logical packets into one larger physical packet by briefly delaying packets in an effort to fill the physical network frames (Ethernet, Token-Ring) with as much data as possible. * Designed to improve performance in terminal emulation environments where there are mostly only keystrokes being sent across the network. * Some Environments Benefit from Disabling Packet Batching * Applies mainly to socket-based networks (BSD) although we have seen some TLI networks such as NCR's benefit. * Applications sending very small result sets or statuses from sprocs will usually benefit. Benchmark with your own application to be sure. * This makes SQL Server open all connections with the TCP_NODELAY option. Packets will be sent regardless of size. * To disable packet batching, in pre-Sys 11, start SQL Server with the 1610 Trace Flag. $SYBASE/dataserver -T1610 -d /usr/u/sybase/master.dat ... Your errorlog will indicate the use of this option with the message: SQL Server booted with TCP_NODELAY enabled. Operating System Issues * Never Let SQL Server Page Fault * It is better to configure SQL Server with less memory and do more physical database I/O than to page fault. OS page faults are synchronous and stop the entire dataserver engine until the page fault completes. Since database I/O's are asynchronous, other user tasks can continue! * Use Process Affinitying in SMP Environments, if Supported * Affinitying dataserver engines to specific CPUs minimizes overhead associated with moving process information (registers, etc) between CPUs. Most implementations will preference other tasks onto other CPUs as well allowing even more CPU time for dataserver engines. * Watch out for OS's which are not fully symmetric. Affinitying dataserver engines onto CPUs that are heavily used by the OS can seriously degrade performance. Benchmark with your application to find optimal binding. * Increase priority of dataserver engines, if supported * Give SQL Server the opportunity to do more work. If SQL Server has nothing to do, it will voluntarily yield the CPU. * Watch out for OS's which externalize their async drivers. They need to run too! * Use of OS Monitors to Verify Resource Usage * The OS CPU monitors only "know" that an instruction is being executed. With SQL Server's own threading and scheduling, it can routinely be 90% idle when the OS thinks its 90% busy. SQL Monitor shows real CPU usage. * Look into high disk I/O wait time or I/O queue lengths. These indicate physical saturation points in the I/O subsystem or poor data distribution. * Disk Utilization above 50% may be subject to queuing effects which often manifest themselves as uneven response times. * Look into high system call counts which may be symptomatic of problems. * Look into high context switch counts which may also be symptomatic of problems. * Optimize your kernel for SQL Server (minimal OS file buffering, adequate network buffers, appropriate KEEPALIVE values, etc). * Use OS Monitors and SQL Monitor to Determine Bottlenecks * Most likely "Non-Application" contention points include: Resource Where to Look --------- -------------- CPU Performance SQL Monitor - CPU and Trends Physical I/O Subsystem OS Monitoring tools - iostat, sar... Transaction Log SQL Monitor - Device I/O and Device Hit Rate on Log Device SQL Server Network Polling SQL Monitor - Network and Benchmark Baselines Memory SQL Monitor - Data and Cache Utilization * Use of Vendor-support Striping such as LVM and RAID * These technologies provide a very simple and effective mechanism of load balancing I/O across physical devices and channels. * Use them provided they support asynchronous I/O and reliable writes. * These approaches do not eliminate the need for Sybase segments to ensure minimal contention for internal resources. * Non-read-only environments should expect performance degradations when using RAID levels other than level 0. These levels all include fault tolerance where each write requires additional reads to calculate a "parity" as well as the extra write of the parity data. Hardware Configuration Issues * Number of CPUs * Use information from SQL Monitor to assess SQL Server's CPU usage. * In SMP environments, dedicate at least one CPU for the OS. * Advantages and scaling of VSA is application-dependent. VSA was architected with large multi-user systems in mind. * I/O Subsystem Configuration * Look into high Disk I/O Wait Times or I/O Queue Lengths. These may indicate physical I/O saturation points or poor data distribution. * Disk Utilization above 50% may be subject to queuing effects which often manifest themselves as uneven response times. * Logical Volume configurations can impact performance of operations such as create database, create index, and bcp. To optimize for these operations, create Logical Volumes such that they start on different channels / disks to ensure I/O is spread across channels. * Discuss device and controller throughput with hardware vendors to ensure channel throughput high enough to drive all devices at maximum rating. General SQL Server Tuning * Changing Values with sp_configure or buildmaster _It is imperative that you only use sp_configure to change those parameters that it currently maintains because the process of reconfiguring actually recalculates a number of other buildmaster parameters. Using the Buildmaster utility to change a parameter "managed" by sp_configure may result in a mis-configured server and cause adverse performance or even worse ... _ * Sizing Procedure Cache + SQL Server maintains an MRU-LRU chain of stored procedure query plans. As users execute sprocs, SQL Server looks in cache for a query plan to use. However, stored procedure query plans are currently not re-entrant! If a query plan is available, it is placed on the MRU and execution begins. If no plan is in memory, or if all copies are in use, a new copy is read from the sysprocedures table. It is then optimized and put on the MRU for execution. + Use dbcc memusage to evaluate the size and number of each sproc currently in cache. Use SQL Monitor's cache statistics to get your average cache hit ratio. Ideally during production, one would hope to see a high hit ratio to minimize the procedure reads from disk. Use this information in conjuction with your desired hit ratio to calculate the amount of memory needed. * Memory + Tuning memory is more a price/performance issue than anything else ! The more memory you have available, the greater than probability of minimizing physical I/O. This is an important goal though. Not only does physical I/O take significantly longer, but threads doing physical I/O must go through the scheduler once the I/O completes. This means that work on behalf of the thread may not actually continue to execute for quite a while ! + There are no longer (as of v4.8) any inherent limitations in SQL Server which cause a point of diminishing returns on memory size. + Calculate Memory based on the following algorithm : Total Memory = Dataserver Executable Size (in bytes) + Static Overhead of 1 Mb + User Connections x 40,960 bytes + Open Databases x 644 bytes + Locks x 32 bytes + Devices x 45,056 bytes + Procedure Cache + Data Cache * Recovery Interval + As users change data in SQL Server, only the transaction log is written to disk right away for recoverability. "Dirty" data and index pages are kept in cache and written to disk at a later time. This provides two major benefits: 1. Many transactions may change a page yet only one physical write is done 2. SQL Server can schedule the physical writes "when appropriate" + SQL Server must eventually write these "dirty" pages to disk. + A checkpoint process wakes up periodically and "walks" the cache chain looking for dirty pages to write to disk + The recovery interval controls how often checkpoint writes dirty pages. * Tuning Recovery Interval + A low value may cause unnecessary physical I/O lowering throughput of the system. Automatic recovery is generally much faster during boot-up. + A high value minimizes unnecessary physical I/O and helps throughput of the system. Automatic recovery may take substantial time during boot-up. Audit Performance Tuning for v10.0 * Potentially as Write Intensive as Logging * Isolate Audit I/O from other components. * Since auditing nearly always involves sequential writes, RAID Level 0 disk striping or other byte-level striping technology should provide the best performance (theoretically). * Size Audit Queue Carefully * Audit records generated by clients are stored in an in memory audit queue until they can be processed. * Tune the queue's size with sp_configure "audit queue size", nnnn (in rows). * Sizing this queue too small will seriously impact performance since all user processes who generate audit activity will sleep if the queue fills up. * Size Audit Database Carefully * Each audit row could require up to 416 bytes depending on what is audited. * Sizing this database too small will seriously impact performance since all user processes who generate audit activity will sleep if the database fills up. _________________________________________________________________ Q8.2: TEMP TABLES AND OLTP _________________________________________________________________ Our shop would like to inform folks of a potential problem when using _temporary tables in an OLTP environment._ Using temporary tables dynamically in a OLTP production environment may result in blocking (single-threading) as the number of transactions using the temporary tables increases. Does it affect my application? This warning only applies for SQL, that is being invoked frequently in an OLTP production environment, where the use of _"select into..." or "create table #temp"_ is common. Application using temp tables may experience blocking problems as the number of transactions increases. This warning does not apply to SQL that may be in a report or that is not used frequently. _Frequently_ is defined as several times per second. Why? Why? Why? Our shop was working with an application owner to chase down a problem they were having during peak periods. The problem they were having was severe blocking in tempdb. What was witnessed by the DBA group was that as the number of transactions increased on this particular application, the number of blocks in tempdb also increased. We ran some independent tests to simulate a heavily loaded server and discovered that the data pages in contention were in tempdb's _syscolumns'_ table. This actually makes sense because during table creation entries are added to this table, regardless if it's a temporary or permanent table. We ran another simulation where we created the tables before the stored procedure used it and the blocks went away. We then performed an additional test to determine what impact creating temporary tables dynamically would have on the server and discovered that there is a 33% performance gain by creating the tables once rather than re-creating them. Your mileage may vary. How do I fix this? To make things better, do the 90's thing -- _reduce and reuse your temp tables._ During one application connection/session, aim to create the temp tables only once. Let's look at the lifespan of a temp table. If temp tables are created in a batch within a connection, then all future batches and stored procs will have access to such temp tables until they're dropped; this is the reduce and reuse strategy we recommend. However, if temp tables are created in a stored proc, then the database will drop the temp tables when the stored proc ends, and this means repeated and multiple temp table creations; you want to avoid this. Recode your stored procedures so that they assume that the temporary tables already exist, and then alter your application so that it creates the temporary tables at start-up -- once and not every time the stored procedure is invoked. That's it! Pretty simple eh? Summary The upshot is that you can realize roughly a 33% performance gain and not experience the blocking which is difficult to quantify due to the specificity of each application. Basically, you cannot lose. Solution in pseudo-code If you have an application that creates the same temp table many times within one connection, here's how to convert it to reduce and reuse temp table creations. Raymond Lew has supplied a detailed example for trying this. Old open connection loop until time to go exec procedure vavoom_often /* vavoom_often creates and uses #gocart for every call */ /* eg: select * into #gocart from gocart */ go . . . loop-end close connection New open connection /* Create the temporary table outside of the sproc */ select * into #gocart from gocart where 1 =2 ; go loop until time to go exec procedure vavoom_often /* vavoom_often reuses #gocart which */ /* was created before exec of vavoom_often */ /* - First statement may be a truncate table #gocart */ /* - Execute _with recompile_ */ /* if your table will have more than 10 data pages */ /* as the optimizer will assume 10 data pages for temp tables */ go . . . loop-end close connection Note that it is necessary to call out the code to create the table and it becomes a pain in the butt because the create-table statement will have to be replicated in any stored proc and in the initialization part of the application - this can be a maintenance nuisance. This can be solved by using any macro package such as _m4_ or _cpp_. or by using and adapting the scripts from Raymond Lew. _________________________________________________________________ From: Raymond Lew At our company, we try to keep the database and the application loosely coupled to allow independent changes at the frontend or the backend as long as the interface stays the same. Embedding temp table definitions in the frontend would make this more difficult. To get away from having to embed the temp table definitions in the frontend code, we are storing the temp table definitions in the database. The frontend programs retrieve the definitions and declare the tables dynamically at the beginning of each session. This allows for the change of backend procedures without changes in the frontend when the API does not change. Enclosed below are three scripts. The first is an isql script to create the tables to hold the definitions. The second is a shell script to set up a sample procedure named vavoom. The third is shell script to demonstrate the structure of application code. I would like to thank Charles Forget and Gordon Rees for their assistance on these scripts. --start of setup------------------------------------------------------ /* Raymond Lew - 1996-02-20 */ /* This isql script will set up the following tables: gocart - sample table app_temp_defn - where temp table definitions are stored app_temp_defn_group - a logical grouping of temp table definitions for an application function */ /******************************/ /* gocart table - sample table*/ /******************************/ drop table gocart go create table gocart ( cartname char(10) null ,cartcolor char(30) null ) go create unique clustered index gocart1 on gocart (cartname) go insert into gocart values ('go1','blue ') insert into gocart values ('go2','pink ') insert into gocart values ('go3','green ') insert into gocart values ('go4','red ') go /****************************************************************/ /* app_temp_defn - definition of temp tables with their indexes */ /****************************************************************/ drop table app_temp_defn go create table app_temp_defn ( /* note: temp tables are unique only in first 13 chars */ objectname char(20) not null ,seq_no smallint not null ,defntext char(255) not null ) go create unique clustered index app_temp_defn1 on app_temp_defn (objectname,seq_no) go insert into app_temp_defn values ('#gocart',1,'select * into #gocart') insert into app_temp_defn values ('#gocart',2,' from gocart where 1=2 ') go insert into app_temp_defn values ('#gocartindex',1, "create unique index gocartindex on #gocart (cartname) ") go insert into app_temp_defn values ('#gocart1',1, 'select * into #gocart1 from gocart where 1=2') go /***********************************************************************/ /* app_temp_defn_group - groupings of temp definitions by applications */ /***********************************************************************/ drop table app_temp_defn_group go create table app_temp_defn_group ( appname char(8) not null ,objectname char(20) not null ) go create unique clustered index app_temp_defn_group1 on app_temp_defn_group (appname,objectname) go insert into app_temp_defn_group values('abc','#gocart') insert into app_temp_defn_group values('abc','#gocartindex') go /***********************************************************/ /* get_temp_defn - proc for getting the temp defn by group */ /***********************************************************/ drop procedure get_temp_defn go create procedure get_temp_defn ( @appname char(8) ) as if @appname = '' select defntext from app_temp_defn order by objectname, seq_no else select defntext from app_temp_defn a , app_temp_defn_group b where a.objectname = b.objectname and b.appname = @appname order by a.objectname, a.seq_no return go /* let's try some tests */ exec get_temp_defn '' go exec get_temp_defn 'abc' go --end of setup -------------------------------------------------- --- start of make.vavoom -------------------------------------------- #!/bin/sh # Raymond Lew - 1996-02-20 # # bourne shell script for creating stored procedures using # app_temp_defn table # # demo procedure vavoom created here # # note: you have to change the passwords, id and etc. for your site # note: you might have to some inline changes to make this work # check out the notes within the body # get the table defn's into a text file # # note: next line :you will need to end the line immediately after eot \ isql -Ukryten -Pjollyguy -Sstarbug -w255 <lt eot \ | grep -v '\-\-\-\-' | grep -v 'defntext ' | grep -v ' affected' >tabletext exec get_temp_defn '' go eot # note: prev line :you will need to have a newline immediately after eot # go mess around in vi vi tabletext # # create the proc vavoom after running the temp defn's into db # isql -Ukryten -Pjollyguy -Sstarbug -e <lt eot |more `cat tabletext` go drop procedure vavoom go create procedure vavoom ( @color char(10) ) as truncate table #gocart1 /* who knows what lurks in temp tables */ if @color = '' insert #gocart1 select * from gocart else insert #gocart1 select * from gocart where cartcolor=@color select @color '@color', * from #gocart1 return go exec vavoom '' go exec vavoom 'blue' go eot # note: prev line :you will need to have a newline immediately after eot exit # end of unix script --- end of make.vavoom -------------------------------------------- --- start of defntest.sh ------------------------------------------- #!/bin/sh # Raymond Lew 1996-02-01 # # test script: demonstrate with a bourne shell how an application # would use the temp table definitions stored in the database # # note: you must run setup and make.vavoom first # # note: you have to change the passwords, id and etc. for your site # note: you might have to some inline changes to make this work # check out the notes within the body # get the table defn's into a text file # # note: next line :you will need to end the line immediately after eot \ isql -Ukryten -Pjollyguy -Sstarbug -w255 <lt eot \ | grep -v '\-\-\-\-' | grep -v 'defntext ' | grep -v ' affected' >tabletext exec get_temp_defn '' go eot # note: prev line :you will need to have a newline immediately after eot # go mess around in vi vi tabletext isql -Ukryten -Pjollyguy -Sstarbug -e <lt eot | more `cat tabletext` go exec vavoom '' go exec vavoom 'blue' go eot # note: prev line :you will need to have a newline immediately after eot exit # end of unix script --- end of defntest.sh ------------------------------------------- That's all, folks. Have Fun _________________________________________________________________ Q8.3: DIFFERENCES BETWEEN _CLUSTERED_ AND _NON-CLUSTERED_ _________________________________________________________________ Preface I'd like to talk about the difference between a clustered and a non-clustered index. The two are _very_ different and it's very important to understand the difference between the two to in order to know when and how to use each. I've pondered hard to find the best analogy that I could think of and I've come up with ... the phone book. Yes, a phone book. Imagine that each page in our phone book is equivalent to a Sybase 2K data page. Every time we read a page from our phone book it is equivalent to one disk I/O. Since we are imagining, let's also imagine that our mythical SQL Server (that runs against the phone book) has only enough data cache to buffer 200 phone pages. When our data cache gets full we have to flush an old page out so we can read in a new one. Fasten your seat belts, because here we go... Clustered Index A phone book lists everyone by last name. We have an _A_ section, we have a _B_ section and so forth. Within each section my phone book is clever enough to list the starting and ending names for the given page. The phone book is clustered by last name. create clustered index on phone_book (last_name) It's fast to perform the following queries on the phone book: * Find the address of those whose last name is _Cisar_. * Find the address of those whose last name is between _Even_ and _Fa_ Searches that don't work well: * Find the address of those whose phone number is _440-1300_. * Find the address of those whose prefix is _440_ In order to determine the answer to the two above we'd have to search the entire phone book. We can call that a table scan. Non-Clustered Index To help us solve the problem above we can build a non-clustered index. create nonclustered index on phone_book (phone_number) Our non-clustered index will be built and maintained by our Mythical SQL Server as follows: 1. Create a data structure that will house a _phone_number_ and information where the _phone_number_ exists in the phone book: page number and the row within the page. The phone numbers will be kept in ascending order. 2. Scan the _entire_ phone book and add an entry to our data structure above for _each_ phone number found. 3. For each phone number found, note along side it the page number that it was located _and_ which row it was in. any time we insert, update or delete new numbers, our M-SQL Server will maintain this secondary data structure. It's such a nice Server. Now when we ask the question: Find the address of those whose phone number is _440-1300_ we don't look at the phone book directly but go to our new data structure and it tells us which page and row within the page the above phone number can be found. Neat eh? Draw backs? Well, yes. Because we _probably_ still can't answer the question: Find the address of those whose prefix is _440_ This is because of the data structure being used to implement non-clustered indexes. The structure is a list of ordered values (phone numbers) which point to the actual data in the phone book. This indirectness can lead to trouble when a range or a match query is issued. The structure may look like this: ------------------------------------ |Phone Number | Page Number/Row | ==================================== | 440-0000 | 300/23 | | 440-0001 | 973/45 | | 440-0002 | 23/2 | | ... | | | 440-0030 | 973/45 | | 440-0031 | 553/23 | | ... | | ------------------------------------ As one can see, certain phone numbers may map to the same page. This makes sense, but we need to consider one of our constraints: our Server only has room for 200 phone pages. What may happen is that we re-read the same phone page many times. This isn't a problem if the phone page is in memory. We have limited memory, however, and we may have to flush our memory to make room for other phone pages. So the re-reading may actually be a disk I/O. The Server needs to decide when it's best to do a table scan versus using the non-clustered index to satisfy mini-range type of queries. The way it decides this is by applying a heuristic based on the information maintained when an _update statistics_ is performed. In summary, non-clustered indexes work really well when used for highly selective queries and they may work for short, range type of queries. Suggested Uses Having suffered many table corruption situations (with 150 SQL servers who wouldn't? :-)), I'd say _always_ have a clustered index. With a clustered index you can fish data out around the _bad_ spots on the table thus having minimal data loss. When you cluster, build the cluster to satisfy the largest percentage of range type queries. Don't put the clustered index on your primary key because typically primary keys are increasing linearly. What happens is that you end up inserting all new rows at the end of the table thus creating a hot spot on the last data page. For detail rows, create the clustered index on the commonly accessed foreign key. This will aid joins from the master to it. Use nonclustered index to aid queries where your selection is _very_ selective. For example, primary keys. :-) _________________________________________________________________ Q8.4: OPTIMISTIC VERSUS PESSIMISTIC LOCKING? _________________________________________________________________ This is the same problem another poster had ... basically locking a record to ensure that it hasn't changed underneath ya. fcasas@ix.netcom.com has a pretty nifty solution if you are using ct-lib (I'll include that below -- hope it's okay Francisco ... :-)) ... Basically the problem you are facing is one of being a pessimist or an optimist. I contend that your business really needs to drive this. Most businesses (from my experience) can be optimistic. That is, if you are optimistic that the chances that someone is going to change something from underneath the end-user is _low_, then do _nothing_ about it. On the other hand, if you are pessimistic that someone may change something underneath the end-user, you can solve it at least as follows: Solution #1 Use a timestamp on a header table that would be shared by the common data. This timestamp field is a Sybase datatype and has nothing to do with the current time. Do not attempt to do any operations on this column other than comparisons. What you do is when you grab data to present to the end-user, have the client software also grab the timestamp column value. After some _thing time_, if the end-user wishes to update the database, compare the client timestamp with what's in the database and it it's changed, then you can take appropriate action: again this is dictated by the business. Problem #1 If users are sharing tables but columns are not shared, there's no way to detect this using timestamps because it's not sufficiently granular. Solution #2 (presented by fcasas) ... Also are you coding to ct-lib directly? If so there's something that you could have done, or may still be able to do if you are using cursors. With ct-lib there's a ct_describe function that lets you see _key data_. This allows you to implement optimistic locking with cursors and not need timestamps. Timestamps are nice, but they are changed when any column on a row changes, while the ct_describe mechanism detects changes at the columns level for a greater degree of granularity of the change. In other words, the timestamp granularity is at the row, while ct_describes CS_VERSION_KEY provides you with granularity at the column level. Unfortunately this is not well documented and you will have to look at the training guide and the manuals very closely. Further if you are using cursors _do not_ make use of the [for {read only | update [of column_name_list]}] of the _select_ statement. Omitting this clause will still get you data that can still be updated and still only place a shared lock on the page. If you use the _read only_ clause you are acquiring shared locks, but the cursor is not updatable. However, if you say update [of ... will place updated locks on the page, thus causing contention. So, if you are using cursors _don't use_ the above clause. So, could you answer the following three questions: 1. Are you using optimistic locking? 2. Are you coding to ct-lib? 3. Are you using cursors? Problem #2 You need to be coding with ct-lib ... Solution #3 Do nothing and be optimistic. We do a lot of that in our shop and it's really not that big of a problem. Problem #3 Users may clobber each other's changes ... then they'll come looking for you to clobber you! :-) _________________________________________________________________ Q8.5: HOW DO I FORCE AN INDEX TO BE USED? _________________________________________________________________ Sybase 4.x and Sybase System 10 All indexes have an ordinal value assigned to them. For example, the following query will return the ordinal value of all the indexes on _my_table_: select name, indid from sysindexes where id = object_id("my_table") Assuming that we wanted to force the usuage of index numbered three: select ... from my_table(3) note that using a value of zero is equivalent to a _table scan_. _You should heavily document any indexed that are forced. _ System 11 In System 11, the binding of the internal ordinal value is alleviated so that instead of using the ordinal index value, the index name can be used instead: select ... from my_table (_index_ my_first_index) Note that you should _still_ document that the index is being forced. _________________________________________________________________ Q8.6: WHY PLACE TEMPDB AND LOG ON LOW NUMBERED DEVICES? _System 10 and below._ _________________________________________________________________ In System 10 and Sybase 4.X, the I/O scheduler starts at logical device (_ldev_) zero and works up the _ldev_ list looking for outstanding I/O's to process. Taking this into consideration, the following device fragments (_disk init_) should be added before any others: 1. tempdb 2. log _________________________________________________________________ Q8.7: How much memory to configure? System 10 and below. ---------------------------------------------------------------------------- Overview At some point you'll wonder if your SQL Server 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 SQL Server is that it has to be large enough to accomodate: * concurrent user connections * active procedures * and concurrent open databases. By not setting the SQL Server up correctly it will affect the performance of it. A delicate balance needs to be struck where your SQL Server 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 SQL Servers. * All queries have been tuned and that there are no unnecessary table scans. Preface As the SQL Server 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 SQL Server 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 SQL Server stores the most recently used data pages and index pages in 2K page units. If SQL Server 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 SQL Server 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 SQL Server. 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 SQL Server 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 SQL Server memory needs are met. Available Cache When the SQL Server 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 SQL Server'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 SQL Server 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 SQL Server the longer it'll take. Our experience is that for a SQL Server 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 SQL Server. 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 SQL Server will be either flushing to disk or transmitting to a user connection. If this area is too small, the SQL Server 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 SQL Server. This is good because a user connection may request validation and the SQL Server 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 SQL Server 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 configurd 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 SQL Server 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 SQL Server has 15,964 2K pages configured but 17,185 2K pages are required. This SQL Server 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 ---------------------------------------------------------------------------- Q8.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 procedues will improve throughput because less bytes need to flow down the wire from the client to the SQL server. * 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 parameters change). * Modularity - during application development, the application designer can concentrate on the front-end and the DB designer can concentrate on the SQL Server. _________________________________________________________________ Q8.9: YOU AND _SHOWPLAN_ OUTPUT _________________________________________________________________ Microsoft SQL Server includes a very intelligent cost-based query optimizer which, given an ad-hoc query, can quickly determine the best access method for retrieving the data, including the order in which to join tables and whether or not to use indexes that may be on those tables. By using a cost-based query optimizer, the System Administrator or end user is released from having to determine the most efficient way of structuring the query to get optimal performance -- instead, the optimizer looks at all possible join orders, and the cost of using each index, and picks the plan with the least cost in terms of page I/O's. Detailed information on the final access method that the optimizer chooses can be displayed for the user by executing the Transact-SQL "SET SHOWPLAN ON" command. This command will show each step that the optimizer uses in joining tables and which, if any, indexes it chooses to be the least-cost method of accessing the data. This can be extremely beneficial when analyzing certain queries to determine if the indexes that have been defined on a table are actually being considered by the optimizer as useful in getting to the data. This document will define and explain each of the output messages from SHOWPLAN, and give example queries and the output from SHOWPLAN to illustrate the point. The format will be consistent throughout: a heading which corresponds to the exact text of a SHOWPLAN statement, followed by a description of what it means, a sample query which generates that particular message, and the full output from executing the query with the SHOWPLAN option on. Wherever possible, the queries will use the existing tables and indexes, unaltered, from the SQL Server "Pubs" sample database. STEP n This statement will be included in the SHOWPLAN output for every query, where n is an integer, beginning with "STEP 1". For some queries, SQL Server cannot effectively retrieve the results in a single step, and must break the query plan into several steps. For example, if a query includes a GROUP BY clause, the query will need to be broken into at least two steps: one step to select the qualifying rows from the table, and another step to group them. The following query demonstrates a singlestep query. Query: SELECT au_lname, au_fname FROM Authors WHERE city = "Oakland" SHOWPLAN: STEP 1 The type of query is SELECT FROM TABLE authors Nested iteration Table Scan The type of query is SELECT (into a worktable) This SHOWPLAN statement indicates that SQL Server needs to insert some of the query results into an intermediate worktable, and later in the query processing will then select the values out of that table. This is most often seen with a query which involves a GROUP BY clause, as the results are first put into a work table, and then the qualifying rows in the work table are grouped based on the given column in the GROUP BY clause. The following query returns a list of all cities and indicates the number of authors that live in each city. The query plan is composed of two steps: the first step selects the rows into a worktable, and the second step retrieves the grouped rows from the worktable: Query: SELECT city, total_authors = count(*) FROM Authors GROUP BY city SHOWPLAN: STEP 1 The type of query is SELECT (into a worktable) GROUP BY Vector Aggregate FROM TABLE authors Nested iteration Table Scan TO TABLE Worktable STEP 2 The type of query is SELECT FROM TABLE Worktable Nested iteration Table Scan The type of query is <query type> This statement describes the type of query for each step. For most user queries, the value for <query type> will be SELECT, INSERT, UPDATE, or DELETE. If SHOWPLAN is turned on while other commands are issued, the <query type> will reflect the command that was issued. The following examples show various outputs for different queries/commands: Query 1: CREATE TABLE Mytab (col1 int) SHOWPLAN 1: STEP 1 The type of query is TABCREATE Query 2: INSERT Publishers VALUES ("9904", "NewPubs", "Seattle", "WA") SHOWPLAN 2: STEP 1 The type of query is INSERT The update mode is direct Table Scan TO TABLE publishers The update mode is deferred There are two methods or "modes" that SQL Server can use to perform update operations such as INSERT, DELETE, UPDATE, and SELECT INTO. These methods are called deferred update and direct update. When the deferred method is used, the changes are applied to all rows of the table by making log records in the transaction log to reflect the old and new value of the column(s) being modified (in the case of UPDATE operations), or the values which will be inserted or deleted (in the case of INSERT and DELETE, respectively). When all of the log records have been constructed, the changes are then applied to the data pages. This method generates more log records than a direct update (discussed later), but it has the advantage of allowing the execution of commands which may cascade changes throughout a table. For example, consider a table which has a column "col1" with a unique index on it, and data values numbered consecutively from 1 to 100 in that column. Assume an UPDATE statement is executed to increase the value in each row by 1: Query 1: UPDATE Mytable SET col1 = col1 + 1 SHOWPLAN 1: STEP 1 The type of query is UPDATE The update mode is deferred FROM TABLE Mytable Nested iteration Table Scan TO TABLE Mytable Consider the consequences of starting at the first row in the table, and updating each row, through the end of the table. Updating the first row (which has an initial value of 1) to 2 would cause an error, as the unique index would be violated since there is already a value of 2 in the table; likewise, updating the second row (which has an initial value of 2) to 3 would also cause a unique key violation, as would all rows through the end of the table, except for the last row. By using deferred updates, this problem is easily avoided. The log records are first constructed to show what the new values for each row will be, the existing rows are deleted, and the new values inserted. Just as with UPDATE commands, INSERT commands may also be deferred for very similar reasons. Consider the following query (there is no clustered index or unique index on the "roysched" table): Query 2: INSERT roysched SELECT * FROM roysched SHOWPLAN 2: STEP 1 The type of query is INSERT The update mode is deferred FROM TABLE roysched Nested iteration Table Scan TO TABLE roysched Since there is no clustered index on the table, the new rows will be added to the end of the table. The query processor needs to be able to differentiate between the existing rows that are currently in the table (prior to the INSERT command) and the rows which will be inserted, so as to not get into a continuous loop of selecting a row, inserting it at the end of the table, selecting that row that it just inserted, and re-inserting it again. By using the deferred method of inserting, the log records can be first be constructed to show all of the currently-existing values in the table, then SQL Server will re-read those log records to insert them into the table. The update mode is direct Whenever possible, SQL Server will attempt to use the direct method of applying updates to tables, since it is faster and requires fewer log records to be generated than the deferred method. Depending on the type of command, one or more criteria must be met in order for SQL Server to perform the update using the direct method. Those criteria are: * INSERT: For the direct update method to be used for INSERT operations, the table into which the rows are being inserted cannot be a table which is being read from in the same command. The second query example in the previous section demonstrates this, where the rows are being inserted into the same table in which they are being selected from. In addition, if rows are being inserted into the target table, and one or more of the target table's columns appear in the WHERE clause of the query then the deferred method, rather than the direct method, will be used. * SELECT INTO: When a table is being populated with data by means of a SELECT INTO command, the direct method will always be used to insert the new rows. * DELETE: For the direct update method to be used for DELETE operations, the query optimizer must be able to determine that either 0 or 1 rows qualify for the delete. The only means for it to verify this is to check that there is a unique index on the table, which is qualified in the WHERE clause of the DELETE command, and the target table is not joined with any other table(s). * UPDATE: For the direct update method to be used for UPDATE operations, the same criteria apply as for DELETE: a unique index must exist such that the query optimizer can determine that no more than 1 row qualifies for the update, and the only table in the UPDATE command is the target table to update. In addition, all columns that are being updated must be datatypes that are fixedlength, rather than variable-length. Note that any column that allows NULLs is internally stored by SQL Server as a variable-length datatype column. Query 1: DELETE FROM authors WHERE au_id = "172-32-1176" SHOWPLAN 1: STEP 1 The type of query is DELETE The update mode is direct FROM TABLE authors Nested iteration Using Clustered Index TO TABLE authors Query 2: UPDATE titles SET type = "popular_comp" WHERE title_id = "BU2075" SHOWPLAN 2: STEP 1 The type of query is UPDATE The update mode is direct FROM TABLE titles Nested iteration Using Clustered Index TO TABLE titles Query 3: UPDATE titles SET price = $5.99 WHERE title_id = "BU2075" SHOWPLAN 3: STEP 1 The type of query is UPDATE The update mode is deferred FROM TABLE titles Nested iteration Using Clustered Index TO TABLE titles Note that the only difference between the second and third example queries is the column of the table which is being updated. In the second query, the direct update method is used, whereas in the third query, the deferred method is used. This difference is due to the datatype of the column being updated: the titles.type column is defined as "char(12) NOT NULL", while the titles.price column is defined as "money NULL". Since the titles.price column is not a fixed-length datatype, the direct method cannot be used. GROUP BY This statement appears in the SHOWPLAN output for any query that contains a GROUP BY clause. Queries that contain a GROUP BY clause will always be at least two-step queries: one step to select the qualifying rows into a worktable and group them, and another step to return the rows from the worktable. The following example illustrates this: Query: SELECT type, AVG(advance), SUM(ytd_sales) FROM titles GROUP BY type SHOWPLAN: STEP 1 The type of query is SELECT (into a worktable) GROUP BY Vector Aggregate FROM TABLE titles Nested iteration Table Scan TO TABLE Worktable STEP 2 The type of query is SELECT FROM TABLE Worktable Nested iteration Table Scan Scalar Aggregate Transact-SQL includes the aggregate functions: * AVG() * COUNT() * COUNT(*) * MAX() * MIN() * SUM() Whenever an aggregate function is used in a SELECT statement that does not include a GROUP BY clause, it produces a single value, regardless of whether it is operating on all of the rows in a table or on a subset of the rows defined by a WHERE clause. When an aggregate function produces a single value, the function is called a "scalar aggregate", and is listed as such by SHOWPLAN. The following example shows the use of scalar aggregate functions: Query: SELECT AVG(advance), SUM(ytd_sales) FROM titles WHERE type = "business" SHOWPLAN: STEP 1 The type of query is SELECT Scalar Aggregate FROM TABLE titles Nested iteration Table Scan STEP 2 The type of query is SELECT Table Scan Notice that SHOWPLAN considers this a two-step query, which is very similar to the SHOWPLAN from the GROUP BY query listed earlier. Since the query contains a scalar aggregate, which will return a single value, SQL Server keeps internally a "variable" to store the result of the aggregate function. It can be thought of as a temporary storage space to keep a running total of the aggregate function as the qualifying rows from the table are evaluated. After all rows have been evaluated from the table (Step 1), the final value from the "variable" is then selected (Step 2) to return the scalar aggregate result. Vector Aggregate When a GROUP BY clause is used in a query which also includes an aggregate function, the aggregate function produces a value for each group. These values are called "vector aggregates". The "Vector Aggregate" statement from SHOWPLAN indicates that the query includes a vector aggregate. Below is an example query and SHOWPLAN which includes a vector aggregate: Query: SELECT title_id, AVG(qty) FROM sales GROUP BY title_id SHOWPLAN: STEP 1 The type of query is SELECT (into a worktable) GROUP BY Vector Aggregate FROM TABLE sales Nested iteration Table Scan TO TABLE Worktable STEP 2 The type of query is SELECT FROM TABLE Worktable Nested iteration Table Scan FROM TABLE This SHOWPLAN step indicates the table that the query is reading from. In most queries, the "FROM TABLE" will be followed on the next line by the name of the table which is being selected from. In other cases, it may indicate that it is selecting from a worktable (discussed later). The main importance of examining the table names after the "FROM TABLE" output is to determine the order in which the query optimizer is joining the tables. The order of the tables listed after the "FROM TABLE" statements in the SHOWPLAN output indicate the same order that the tables were joined; this order may be (and often times is) different than the order that they are listed in the FROM clause of the query, or the order that they appear in the WHERE clause of the query. This is because the query optimizer examines all different join orders for the tables involved, and picks the join order that will require the least amount of I/O's. Query: SELECT authors.au_id, au_fname, au_lname FROM authors, titleauthor, titles WHERE authors.au_id = titleauthor.au_id AND titleauthor.title_id = titles.title_id AND titles.type = "psychology" SHOWPLAN: STEP 1 The type of query is SELECT FROM TABLE titles Nested iteration Table Scan FROM TABLE titleauthor Nested iteration Table Scan FROM TABLE authors Nested iteration Table Scan This query illustrates the order in which the SQL Server query optimizer chooses to join the tables, which is not the order that they were listed in the FROM clause or the WHERE clause. By examining the order of the "FROM TABLE" statements, it can be seen that the qualifying rows from the titles table are first located (using the search clause <titles.type = "psychology">). Those rows are then joined with the titleauthor table (using the join clause <titleauthor.title_id = titles.title_id>), and finally the titleauthor table is joined with the authors table to retrieve the desired columns (using the join clause <authors.au_id = titleauthor.au_id>). TO TABLE When a command is issued which makes or attempts to make a modification to one or more rows of a table, such as INSERT, DELETE, UPDATE, or SELECT INTO, the "TO TABLE" statement will show the target table which is being modified. For some operations which require an intermediate step which inserts rows into a worktable (discussed later), the "TO TABLE" will indicate that the results are going to the "Worktable" table, rather than a user table. The following examples illustrate the use of the "TO TABLE" statement: Query 1: INSERT sales VALUES ("8042", "QA973", "7/15/92", 7, "Net 30", "PC1035") SHOWPLAN 1: STEP 1 The type of query is INSERT The update mode is direct Table Scan TO TABLE sales Query 2: UPDATE publishers SET city = "Los Angeles" WHERE pub_id = "1389" SHOWPLAN 2: STEP 1 The type of query is UPDATE The update mode is deferred FROM TABLE publishers Nested iteration Using Clustered Index TO TABLE publishers Notice that the SHOWPLAN for the second query indicates that the publishers table is used both as the "FROM TABLE" as well as the "TO TABLE". In the case of UPDATE operations, the optimizer needs to read the table which contains the row(s) to be updated, resulting in the "FROM TABLE" statement, and then needs to modify the row(s), resulting in the "TO TABLE" statement. Worktable For some types of queries, such as those that require the results to be ordered or displayed in groups, the SQL Server query optimizer may determine that it is necessary to create its own temporary worktable. The worktable is used to hold the intermediate results of the query, at which time the result rows can be ordered or grouped, and then the final results selected from that worktable. When all results have been returned, the worktable is automatically dropped. The worktables are always created in the Tempdb database, so it is possible that the system administrator may have to increase the size of Tempdb to accomodate that queries which require very large worktables. Since the query optimizer creates these worktables for its own internal use, the names of the worktables will not be listed in the tempdb..sysobjects table. Worktables will always need to be used when a query contains a GROUP BY clause. For queries involving ORDER BY, it is possible that the ordering can be done without the use of the worktable. If there is a clustered index on the column(s) in the ORDER BY clause, the optimizer knows that the rows are already stored in sorted order, so a sort in a worktable is not necessary (although there are exceptions to this, depending on the sort order which is installed on the server). Since the data is not stored in sorted order for nonclustered indexes, the worktable will not be necessary if the cheapest access plan is by using the nonclustered index. However, if the optimizer determines that scanning the entire table will require fewer I/Os than using the nonclustered index, then a worktable will need to be created for the ordering of the results. The following examples illustrate the use of worktables: Query 1: SELECT type, AVG(advance), SUM(ytd_sales) FROM titles GROUP BY type SHOWPLAN 1: STEP 1 The type of query is SELECT (into a worktable) GROUP BY Vector Aggregate FROM TABLE titles Nested iteration Table Scan TO TABLE Worktable STEP 2 The type of query is SELECT FROM TABLE Worktable Nested iteration Table Scan Query 2: SELECT * FROM authors ORDER BY au_lname, au_fname SHOWPLAN 2: STEP 1 The type of query is INSERT The update mode is direct Worktable created for ORDER BY FROM TABLE authors Nested iteration Table Scan TO TABLE Worktable STEP 2 The type of query is SELECT This step involves sorting FROM TABLE Worktable Using GETSORTED Table Scan Query 3: SELECT * FROM authors ORDER BY au_id SHOWPLAN 3: STEP 1 The type of query is SELECT FROM TABLE authors Nested iteration Table Scan In the third example above, notice that no worktable was created for the ORDER BY clause. This is because there is a unique clustered index on the authors.au_id column, so the data is already stored in sorted order based on the au_id value, and an additional sort for the ORDER BY is not necessary. In the second example, there is a composite nonclustered index on the columns au_lname and au_fname. However, since the optimizer chose not to use the index, and due to the sort order on the SQL Server, a worktable needed to be created to accomodate the sort. Worktable created for SELECT_INTO SQL Server's SELECT INTO operation performs two functions: it first creates a table with the exact same structure as the table being selected from, and then it insert all rows which meet the WHERE conditions (if a WHERE clause is used) of the table being selected from. The "Worktable created for SELECT_INTO" statement is slightly misleading, in that the "worktable" that it refers to is actually the new physical table that is created. Unlike other worktables, it is not dropped when the query finishes executing. In addition, the worktable is not created in Tempdb, unless the user specifies Tempdb as the target database for the new table. Query: SELECT * INTO seattle_stores FROM stores WHERE city = "seattle" SHOWPLAN: STEP 1 The type of query is TABCREATE STEP 2 The type of query is INSERT The update mode is direct Worktable created for SELECT_INTO FROM TABLE stores Nested iteration Table Scan TO TABLE Worktable Worktable created for DISTINCT When a query is issued which includes the DISTINCT keyword, all duplicate rows are excluded from the results so that only unique rows are returned. To accomplish this, SQL Server first creates a worktable to store all of the results of the query, including duplicates, just as though the DISTINCT keyword was not included. It then sorts the rows in the worktable, and is able to easily discard the duplicate rows. Finally, the rows from the worktable are returned, which insures that no duplicate rows will appear in the output. Query: SELECT DISTINCT city FROM authors SHOWPLAN: STEP 1 The type of query is INSERT The update mode is direct Worktable created for DISTINCT FROM TABLE authors FROM TABLE authors Nested iteration Table Scan TO TABLE Worktable STEP 2 The type of query is SELECT This step involves sorting FROM TABLE Worktable Using GETSORTED Table Scan Worktable created for ORDER BY As discussed previously, queries which include an ORDER BY clause will often require the use of a temporary worktable. When the optimizer cannot use an available index for the ordering, it creates a worktable for use in sorting the result rows prior to returning them. Below is an example which shows the worktable being created for the ORDER BY clause: Query: SELECT * FROM authors ORDER BY city SHOWPLAN: STEP 1 The type of query is INSERT The update mode is direct Worktable created for ORDER BY FROM TABLE authors FROM TABLE authors Nested iteration Table Scan TO TABLE Worktable STEP 2 The type of query is SELECT This step involves sorting FROM TABLE Worktable Using GETSORTED Table Scan Worktable created for REFORMATTING When joining tables, SQL Server may in some cases choose to use a "reformatting strategy" to join the tables and return the qualifying rows. This strategy is only considered as a last resort, when the tables are large and neither table in the join has a useful index to use. The reformatting strategy inserts the rows from the smaller of the two tables into a worktable. Then, a clustered index is created on the worktable, and the clustered index is then used in the join to retrieve the qualifying rows from each table. The main cost in using the reformatting strategy is the time and I/Os necessary to build the clustered index on the worktable; however, that cost is still cheaper than joining the tables with no index. If user queries are using the reformatting strategy, it is generally a good idea to examine the tables involved and create indexes on the columns of the tables which are being joined. The following example illustrates the reformatting strategy. Since none of the tables in the Pubs database are large enough for the optimizer to consider using this strategy, two new tables are used. Each table has 5 columns defined as "char(200)". Tab1 has 500 rows and Tab2 has 250 rows. Query: SELECT Tab1.col1 FROM Tab1, Tab2 WHERE Tab1.col1 = Tab2.col1 SHOWPLAN: STEP 1 The type of query is INSERT The update mode is direct Worktable created for REFORMATTING FROM TABLE Tab2 Nested iteration Table Scan TO TABLE Worktable STEP 2 The type of query is SELECT FROM TABLE Tab1 Nested iteration Table Scan FROM TABLE Worktable Nested iteration Using Clustered Index This step involves sorting This SHOWPLAN statement indicates that the query must sort the intermediate results before returning them to the user. Queries that specify DISTINCT will require an intermediate sort, as well as queries that have an ORDER BY clause which cannot use an available index. As stated earlier, the results are put into a worktable, and the worktable is then sorted. The example on the following page demontrates a query which requires a sort: Query: SELECT DISTINCT state FROM stores SHOWPLAN: STEP 1 The type of query is INSERT The update mode is direct Worktable created for DISTINCT FROM TABLE stores FROM TABLE stores Nested iteration Table Scan TO TABLE Worktable STEP 2 The type of query is SELECT This step involves sorting FROM TABLE Worktable Using GETSORTED Table Scan Using GETSORTED This statement indicates one of the ways in which the result rows can be returned from a table. In the case of "Using GETSORTED", the rows will be returned in sorted order. However, not all queries which return rows in sorted order will have this step. In the case of a query which has an ORDER BY clause, and an index with the proper sort sequence exists on those columns being ordered, an intermediate sort may not be necessary, and the rows can simply be returned in order by using the available index. The "Using GETSORTED" method is used when SQL Server must first create a temporary worktable to sort the result rows, and then return them in the proper sorted order. The following example shows a query which requires a worktable to be created and the rows returned in sorted order: Query: SELECT au_id, au_lname, au_fname, city FROM authors ORDER BY city SHOWPLAN: STEP 1 The type of query is INSERT The update mode is direct Worktable created for ORDER BY FROM TABLE authors FROM TABLE authors Nested iteration Table Scan TO TABLE Worktable STEP 2 The type of query is SELECT This step involves sorting FROM TABLE Worktable Using GETSORTED Table Scan Nested iteration The "Nested iteration" is the default technique used to join tables and/or return rows from a table. It simply indicates that the optimizer is using one or more sets of loops to go through a table and retrieve a row, qualify the row based on the search criteria given in the WHERE clause, return the row to the front-end, and loop again to get the next row. The method in which it gets the rows (such as using an available index) is discussed later. The following example shows the optimizer doing nested iterations through each of the tables in the join: Query: SELECT title_id, title FROM titles, publishers WHERE titles.pub_id = publishers.pub_id AND publishers.pub_id = '1389' SHOWPLAN: STEP 1 The type of query is SELECT FROM TABLE publishers Nested iteration Using Clustered Index FROM TABLE titles Nested iteration Table Scan EXISTS TABLE : nested iteration This SHOWPLAN step is very similar to the previous one of "Nested iteration". The difference, however, is that this step indicates a nested iteration on a table which is part of an existence test in a query. There are several ways an existence test can be written in Transact-SQL, such as "EXISTS", "IN", or "=ANY". Prior to SQL Server version 4.2, queries which contained an IN clause followed by a subquery were treated as table joins. Beginning with version 4.2, these queries are now treated the same as if they were written with an EXISTS clause. The following examples demonstrate the SHOWPLAN output with queries which test for existence of values: Query 1: SELECT au_lname, au_fname FROM authors WHERE EXISTS (SELECT * FROM publishers WHERE authors.city = publishers.city) SHOWPLAN 1: STEP 1 The type of query is SELECT FROM TABLE authors Nested iteration Table Scan FROM TABLE publishers EXISTS TABLE : nested iteration Table Scan Query 2: SELECT title FROM titles WHERE pub_id IN (SELECT pub_id FROM publishers WHERE city LIKE "B%") SHOWPLAN 2: STEP 1 The type of query is SELECT FROM TABLE titles Nested iteration Table Scan FROM TABLE publishers EXISTS TABLE : nested iteration Table Scan Table Scan This SHOWPLAN statement indicates which method was used to retrieve the physical result rows from the given table. When the "table scan" method is used, the execution begins with the first row in the table; each row is then retrieved and compared with the conditions in the WHERE clause, and returned to the front-end if it meets the given criteria. Regardless of how many rows qualify, every row in the table must be looked at, so for very large tables, a table scan can be very costly in terms of page I/Os. If a table has one or more indexes on it, the query optimizer may still choose to do a table scan instead of using one of the available indexes if the optimizer determines that the indexes are too costly or are not useful for the given query. The following query shows a typical table scan: Query: SELECT au_lname, au_fname FROM authors SHOWPLAN: STEP 1 The type of query is SELECT FROM TABLE authors Nested iteration Table Scan Using Clustered Index This SHOWPLAN statement indicates that the query optimizer chose to use the clustered index on a table to retrieve the rows. Unlike a table scan, using an index to retrieve rows does not require the optimizer to examine every row in the table (unless the WHERE clause applies to all rows). For queries which return a small percentage of the rows from a large table, the savings in terms of I/Os of using an index versus doing a table scan can be very significant. The following query shows the clustered index being used to retrieve the rows from the table: Query: SELECT title_id, title FROM titles WHERE title_id LIKE "PS2%" SHOWPLAN: STEP 1 The type of query is SELECT FROM TABLE titles Nested iteration Using Clustered Index Index : <index name> Like the previous statement with the clustered index, this statement indicates that the optimizer chose to use an index to retrieve the rows instead of doing a table scan. The <index namethat follows the "Index :" label will always be the name of a nonclustered index on the table. Remember that each table can have no more than one clustered index, but can have up to 249 nonclustered indexes. The following query illustrates the use of a nonclustered index to find and return rows. This query uses the sysobjects table in the master database as an example, rather than a table in Pubs, since using a nonclustered index on the Pubs tables is generally more costly in terms of I/O than a straight table scan, due to the fact that most of the tables are only 1 page in size. Query: SELECT * FROM master..sysobjects WHERE name = "mytable" AND uid = 5 SHOWPLAN: STEP 1 The type of query is SELECT FROM TABLE master..sysobjects Nested iteration Index : ncsysobjects Using Dynamic Index This SHOWPLAN statement indicates that the query optimizer has chosen to build its own index during the execution of the query, for use in its "OR strategy". Since queries involving OR clauses are generally not very efficient in terms of being able to quickly access the data, the SQL Server optimizer may choose to use the OR strategy. When the OR strategy is used, the optimizer makes several passes through the table -- one pass for each argument to each OR clause. The results of each pass are added to a single worktable, and the worktable is then sorted to remove any duplicate rows. The worktable does not contain the actual data rows from the table, but rather it contains the row IDs for the matching rows. The row IDs are simply a combination of the page number and row number on that page for each of the rows. When the duplicates have been eliminated, the optimizer considers the worktable of row IDs to be, essentially, its own index ("Dynamic Index") pointing to the table's data rows. It can then simply scan through the worktable, get each row ID, and return the data row from the table that has that row ID. The OR strategy is not limited only to queries that contain OR clauses. When an IN clause is used to list a group of possible values, SQL Server interprets that the same way as though the query had a separate equality clause for each of the values in the IN clause. To illustrate the OR strategy and the use of the Dynamic Index, the queries will be based on a table with 10,000 unique data rows, a unique nonclustered index on column "col1", and a unique nonclustered index on column "col2". Query 1: SELECT * FROM Mytable WHERE col1 = 355 OR col2 = 732 SHOWPLAN 1: STEP 1 The type of query is SELECT FROM TABLE Mytable Nested iteration Index : col1_idx FROM TABLE Mytable Nested iteration Index : col2_idx FROM TABLE Mytable Nested iteration Using Dynamic Index Query 2: SELECT * FROM Mytable WHERE col1 IN (700, 1503, 311) SHOWPLAN 2: STEP 1 The type of query is SELECT FROM TABLE Mytable Nested iteration Index : col1_idx FROM TABLE Mytable Nested iteration Index : col1_idx FROM TABLE Mytable Nested iteration Index : col1_idx FROM TABLE Mytable Nested iteration Using Dynamic Index SQL Server does not always resort to using the OR strategy for every query that contains OR clauses. The following conditions must be met before it will choose to use the OR strategy: * All columns in the OR clause must belong to the same table. * If any portion of the OR clause requires a table scan (due to lack of index or poor selectivity of a given index), then a table scan will be used for the entire query, rather than the OR strategy. * The decision to use the OR strategy is made after all indexes and costs are evaluated. If any other access plan is less costly (in terms of page I/Os), SQL Server will choose to use the plan with the least cost. In the examples above, if a straight table scan would result in less page I/Os than using the OR strategy, then the queries would be processed as a table scan instead of using the Dynamic Index. _________________________________________________________________ Q8.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 _________________________________________________________________ Q8.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 dowm 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 Q8.7 regarding procedure cache sizing. _________________________________________________________________ Q8.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. _________________________________________________________________ -- Pablo Sanchez | Ph # (415) 933.3812 Fax # (415) 933.2821 pablo@sgi.com | Pg # (800) 930.5635 -or- pablo_p@corp.sgi.com =============================================================================== I am accountable for my actions. http://reality.sgi.com/pablo [ /Sybase_FAQ ]

Part1 - Part2 - Part3 - Part4 - Part5 - Part6 - Part7 - Part8 - Part9 - Part10 - Part11 - Part12 - Part13 - Part14 - Part15 - Part16

Send corrections/additions to the FAQ Maintainer:
pablo@sgi.com

Last Update August 12 1997 @ 02:37 AM

faq-admin@faqs.org