Sybase System XI

1. Introduction. The Problem. Understanding the Components of the Performance Problem. Business Rules and Models. Performance Metrics. Troubleshooting. Elements of the Performance Problem. Summary: Dos and Don'ts. 2. Design. The Phases of Design. The Conceptual Data Model. The Logical Model. Summary: Dos and Don'ts. 3. Understanding the Optimizer. The Optimizer. Cost-Based vs. Rule Based Optimizers. SARG, A Key Optimizer Ingredient. OAM page: A Key Component for Optimizer Statistics. Understanding and Using Showplan for Optimization. Using DBCC TRACEON (3604, 302). Techniques to Use When You Know Better. Clauses. 4. Execution Thread Analysis. Concurrency Anomalies. Execution Thread Analysis. Transaction Maps and Concurrency. Business Rules. Summary. 5. Indexes. Index and Concurrency Issues. Using Nonclustered Indexes for Coverage. Why Different Index Types? Index Characteristics. Index Statistics are Important. Index Tuning Strategy. The Dichotomy of Relational Design and Sybase Indexing. 6. Table Locking and Concurrency Controls. Consistency vs. Concurrency. Sybase Lock Types. Deadlocks. Checking For Deadlocks. Lock Escalation. Table Locks. Lock Isolation Levels. Chained Transactions. Viewing Locks. Blocked Processes. Heap Tables and Lock Contention. Concurrency Analysis. Optimistic Locking. Locking Strategies. 7. Query Strategies. Use Showplan when Testing Code to Make Sure that the Optimizer Uses Appropriate Indexes. Use "Set statistics time on" to Benchmark Performance. Know the Essential Nature of your Data. Pay Attention to Complex Queries and Subqueries. Use Stored Procedures and Triggers When It Makes Sense To Do So. Always start with a Normalized Design. Consider Denormalization ONLY After Normalized. Design Has Been Proven Unsuccessful. Consider A User's High-Level Business Rules With Regard To I/O Performance Tuning Issues. Perform Lock Analysis. Use Transaction Chopping Techniques. Review Your Memory Layout With the DBA and System Administrator. Review the Hardware and Network Architecture. Use Segmentation. Separate Client/Server OTLP Systems and Ad-Hoc DSS (Decision Support System) Queries. "Select into" is Fast. Use it as Often as Possible. Perform Periodic Re-creation of Clustered Indexes with Fillfactor. Bring Down the Server and Bring It Back Up Again Periodically to Clear Memory. Re-analyze SQL Causes per Execution Thread with the Most Experienced Staff Available. 8. Query Tactics and Techniques. Use >= instead of >. Use EXISTS and IN instead of NOT EXISTS and NOT IN. Use Parameters Vs. Local Variables in WHERE Clauses. When Using Multiple Expressions, the Most Limiting Expression Should Go First. Optimize ORDER BY. Use Caution With the LIKE Operator. UNION vs. OR. Avoid NOT EQUALS, !=, !>, !< and . Convert NOT EXISTS to EXISTS. USE NOT EQUALS When There is Not an Index. Put the Least Restricting Expression First in OR Expressions. Avoid the Danger of a Calculation in the WHERE Clause. Try to Use the Whole Index, or as Many Index Columns as Possible. Always Include the Leading Portion of the Index. Avoid the Use of Incompatible Datatypes Between Columns. Beware When Using Temp Tables. Avoid Datatype Inconsistencies or Modifications When Using Temporary Tables Accessed by Multiple Stored Procedures. Use "EXISTS" Instead of "COUNT(*)" When Possible. 9. Memory. Total Memory Configuration Variable. Data and Procedure Caches. Named Caches. I/O Size. Tempdb and Memory. Controlling Aging of Objects. Stored Procedures and the Procedure Cache. Clearing Cache. Testing and Cache Contents. Bibliography. Index.