Query Tuning
Document ID: 32614-01-1200-02
Document ID: 32614-01-1200-02
This publication pertains to Adaptive Server Enterprise Version 12 of the Sybase database management software and to any subsequent version until otherwise indicated in new editions or technical notes. Information in this document is subject to change without notice. The software described herein is furnished under a license agreement, and it may be used or copied only in accordance with the terms of that agreement.
Document Orders
To order additional documents, U.S. and Canadian customers should call Customer Fulfillment at (800) 685-8225, fax (617) 229-9845.
Customers in other countries with a U.S. license agreement may contact Customer Fulfillment via the above fax number. All other international customers should contact their Sybase subsidiary or local distributor.
Upgrades are provided only at regularly scheduled software release dates.
Copyright © 1989–1999 by Sybase, Inc. All rights reserved.
No part of this publication may be reproduced, transmitted, or translated in any form or by any means, electronic, mechanical, manual, optical, or otherwise, without the prior written permission of Sybase, Inc.
Sybase Trademarks
Adaptable Windowing Environment, Adaptive Component Architecture, Adaptive Server Enterprise Monitor, Adaptive Warehouse, ADA Workbench, AnswerBase, Application Manager, AppModeler, APT-Build, APT-Edit, APT- Execute, APT-Library, APT-Translator, APT Workbench, Backup Server, BayCam, Bit-Wise, ClearConnect, Client-Library, Client Services, CodeBank, Connection Manager, DataArchitect, Database Analyzer, DataExpress, Data Pipeline, DataServer, DataWindow, DB-Library, dbQueue, Developers Workbench, DirectConnect, Distribution Agent, Distribution Director, Embedded SQL, EMS, Enterprise Application Server, Enterprise Application Studio, Enterprise Client/Server, EnterpriseConnect, Enterprise Data Studio, Enterprise Manager, Enterprise SQL Server Manager, Enterprise Work Architecture, Enterprise Work Designer, Enterprise Work Modeler, EWA, Formula One, Gateway Manager, GeoPoint, ImpactNow, InformationConnect, InstaHelp, InternetBuilder, iScript,
 
 Jaguar CTS, jConnect for JDBC, KnowledgeBase, Logical Memory Manager, MainframeConnect, Maintenance Express, MAP, MDI Access Server, MDI Database Gateway, media.splash, MetaBridge, MetaWorks, MethodSet, MySupport, Net-Gateway, NetImpact, Net-Library, Next Generation Learning, ObjectConnect, OmniConnect, OmniSQL Access Module, OmniSQL Toolkit, Open Client, Open ClientConnect, Open Client/Server, Open Client/Server Interfaces, Open Gateway, Open Server, Open ServerConnect, Open Solutions, Optima++, PB-Gen, PC APT-Execute, PC DB-Net, PC Net Library, Power++, Power AMC, PowerBuilt, PowerBuilt with PowerBuilder, PowerDynamo, PowerJ, PowerScript, PowerSite, PowerSocket, Powersoft Portfolio, PowerStudio, Power Through Knowledge, PowerWare Desktop, PowerWare Enterprise, ProcessAnalyst, Replication Agent, Replication Driver, Replication Server Manager, Report- Execute, Report Workbench, Resource Manager, RW-DisplayLib, RW-Library, SAFE, SDF, Secure SQL Server, Secure SQL Toolset, Security Guardian, SKILS, smart.partners, smart.parts, smart.script, SQL Code Checker, SQL Edit, SQL Edit/TPU, SQL Modeler, SQL Remote, SQL Server, SQL Server/CFT, SQL Server/DBM, SQL Server Manager, SQL Server SNMP SubAgent, SQL Station, SQL Toolset, Sybase Central, Sybase Client/Server Interfaces, Sybase Development Framework, Sybase Financial Server, Sybase Gateways, Sybase Learning Connection, Sybase MPP, Sybase SQL Desktop, Sybase SQL Lifecycle, Sybase SQL Workgroup, Sybase Synergy Program, Sybase Virtual Server Architecture, Sybase User Workbench, SybaseWare, SyberAssist, SyBooks, System 10, System 11, the System XI logo, SystemTools, Tabular Data Stream, The Enterprise Client/Server Company, The Extensible Software Platform, The Future Is Wide Open, The Learning Connection, The Model for Client/Server Solutions, The Online Information Center, Translation Toolkit, Turning Imagination Into Reality, UltraLite, UNIBOM, Unilib, Uninull, Unisep, Unistring, URK Runtime Kit for UniCode, Viewer, VisualSpeller, VisualWriter, WarehouseArchitect, Warehouse Studio, Warehouse WORKS, Watcom, Watcom SQL, Watcom SQL Server, Web.PB, Web.SQL, WebSights, WebViewer, WorkGroup SQL Server, XA-Library, XA- Server, and XP Server are trademarks of Sybase, Inc. 2/99
Unicode and the Unicode Logo are registered trademarks of Unicode, Inc.
All other company and product names used herein may be trademarks or registered trademarks of their respective companies.
Restricted Rights
Use, duplication, or disclosure by the government is subject to the restrictions set forth in subparagraph (c)(1)(ii) of DFARS 52.227-7013 for the DOD and as set forth in FAR 52.227-19(a)-(d) for civilian agencies.
Sybase, Inc., 6475 Christie Avenue, Emeryville, CA 94608.
 
Table of Contents
About This Book
Adaptive Server Enterprise Documents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxviii
Other Sources of Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xl
Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xli Formatting SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xli
Font and Syntax Conventions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xli
Case . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xlii
Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xliii
Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xliii
Basic Concepts
What Is “Good Performance”? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-1 Response Time . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-1
Throughput . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-1
 
2. Database Design and Denormalizing for Performance
Database Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-1 Physical Database Design for Adaptive Server . . . . . . . . . . . . . . . . . . . . . 2-2
Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-3
Denormalization Input . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-10
Denormalization Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-10
Using Application Logic to Manage Denormalized Data. . . . . . . . . . . . 2-17
Batch Reconciliation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-18
3. Data Storage
Major Performance Gains Through Query Optimization . . . . . . . . . . . . . . . . . . . . . . . 3-1 Query Processing and Page Reads. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-2
Adaptive Server Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-3
Data and Index Pages. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-4
Large Object (LOB) Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-4
Extents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-5
Allocation Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-7
 
Page Allocation Keeps an Object’s Pages Together . . . . . . . . . . . . . . . . . . 3-9
The sysindexes Table and Data Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-10
Heaps of Data: Tables Without Clustered Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . 3-11
Lock Schemes and Differences Between Heaps . . . . . . . . . . . . . . . . . . . . 3-11
Select Operations on Heaps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-12
Allpages-Locked Heap Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-13
Inserting Data into an Allpages-Locked Heap Table. . . . . . . . . . . . . . . . 3-13
Contention During Heap Inserts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-14
Inserting Data into a Data-Only-Locked Heap Table . . . . . . . . . . . . . . . 3-15
If Contention Occurs During Heap Inserts . . . . . . . . . . . . . . . . . . . . 3-15
Deleting Data from a Heap Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-16
Deleting from an Allpages-Locked Heap Table. . . . . . . . . . . . . . . . . 3-16
Deleting from a Data-Only Locked Heap Table. . . . . . . . . . . . . . . . . 3-16
Deleting the Last Row on a Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-17
Update Operations on Heaps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-17
Updates on Allpages-Locked Heap Tables. . . . . . . . . . . . . . . . . . . . . 3-18
Updates on Data-Only-Locked Heap Tables . . . . . . . . . . . . . . . . . . . 3-18
How Adaptive Server Performs I/O for Heap Operations . . . . . . . . . . . . . . . . . . . . . 3-19
Sequential Prefetch, or Large I/O . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-20
Caches and Object Bindings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-20 Heaps, I/O, and Cache Strategies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-21
Overview of Cache Strategies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-21
LRU Replacement Strategy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-21
MRU Replacement Strategy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-22
Caching and Inserts on Heaps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-23
Caching and Update and Delete Operations on Heaps . . . . . . . . . . 3-24
Asynchronous Prefetch and I/O on Heap Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-25
Heaps: Pros and Cons . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-25
Maintaining Heaps. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-26
Using reorg rebuild to Reclaim Space . . . . . . . . . . . . . . . . . . . . . . . . . . 3-27
Reclaiming Space by Creating a Clustered Index . . . . . . . . . . . . . . . 3-27
Reclaiming Space Using bcp . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-27
The Transaction Log: A Special Heap Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-27
 
4. How Indexes Work
From Heaps of Pages to Fast Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-1 What Are Indexes? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-2
Types of Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-2
Page Splitting on Full Data Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-8
Exceptions to Page Splitting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-9
Page Splitting on Index Pages. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-10
Performance Impacts of Page Splitting . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-10
Overflow Pages. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-10
Deleting the Last Row on a Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-12
Index Page Merges. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-14
Nonclustered Index Performance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-17
Index Covering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-21
Indexes and Caching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-24
Index Trips Through the Cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-26
Tuning Query Performance
 
Performance and Tuning Guide ix
Adaptive Server Enterprise Version 12
Adaptive Server’s Cost-Based Optimizer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-2 Steps in Query Processing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-3
Working with the Optimizer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-4
How Is “Fast” Determined? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-4
Factors Examined During Query Optimization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-5
How Preprocessing Adds Optimizable Clauses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-6
Converting Clauses to Search Argument Equivalents . . . . . . . . . . . . . . . 5-7
Conversion of Expressions into Search Arguments. . . . . . . . . . . . . . . . . . 5-7
Search Argument Transitive Closure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-8
 Join Transitive Closure. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-8
Predicate Transformation Example . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-10
Predicate Transformation Steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-10
Search Arguments and Useful Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-11 Search Argument Syntax. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-12
Nonequality Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-12
Histogram Cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-14
Density Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-15
How the Optimizer Uses Densities and Histograms . . . . . . . . . . . . 5-16
Using Statistics on Multiple Search Arguments. . . . . . . . . . . . . . . . . . . . 5-16
Default Values for Search Arguments . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-17
SARGs Using Variables and Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . 5-17
Guidelines for Creating Search Arguments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-18
Join Syntax and Join Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-19
How Joins Are Processed. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-19
When Statistics Are Not Available for Joins . . . . . . . . . . . . . . . . . . . . . . . 5-20
Density Values and Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-20
Multicolumn Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-21
Datatype Mismatches and Query Optimization . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-21
Overview of the Datatype Hierarchy and Index Issues . . . . . . . . . . . . . 5-22
General Rules for Index Use. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-23
Exceptions to the Rule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-23
Comparison of Numeric and Decimal Datatypes . . . . . . . . . . . . . . . 5-24
Comparing Numeric Types to Other Datatypes . . . . . . . . . . . . . . . . 5-24
Datatypes for Parameters and Variables Used as SARGs. . . . . . . . . . . . 5-25
Troubleshooting Datatype Mismatch Problems for SARGs. . . . . . . 5-26
 
Compatible Datatypes for Join Columns . . . . . . . . . . . . . . . . . . . . . . . . . 5-27
Troubleshooting Datatype Mismatch Problems for Joins. . . . . . . . . 5-27
Suggestions on Datatypes and Comparisons . . . . . . . . . . . . . . . . . . . . . . 5-28
Forcing a Conversion to the Other Side of a Join. . . . . . . . . . . . . . . . . . . 5-28
Splitting Stored Procedures to Improve Costing . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-30
6. Access Methods and Query Costing for Single Tables
How To Use This Chapter While Analyzing Queries . . . . . . . . . . . . . . . . . . . . . . . . . . 6-1
Basic Units of Costing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-3
Table Scan Cost . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-3
Cost of a Table Scan on an Allpages-Locked Table . . . . . . . . . . . . . . . . . . 6-3
Cost of a Table Scan on a Data-Only-Locked Tables . . . . . . . . . . . . . . . . . 6-4
Added Cost for Forwarded Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-6
From Rows to Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-6
How Cluster Ratios Affect Large I/O Estimates . . . . . . . . . . . . . . . . . . . . 6-7
The Data Page Cluster Ratio. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-7
Index Page Cluster Ratio. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-8
Evaluating the Cost of Index Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-9
Evaluating the Cost of a Query That Returns a Single Row. . . . . . . . . . . 6-9
Evaluating the Cost of a Query That Returns Many Rows. . . . . . . . . . . . 6-9
Range Queries Using Clustered Indexes (Allpages Locking) . . . . . 6-10
Range Queries with Covering Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-11
Range Queries with Noncovering Indexes . . . . . . . . . . . . . . . . . . . . . . . . 6-13
How Data Row Cluster Ratio Refines Page Estimates . . . . . . . . . . . 6-13
Result Set Size and Index Use . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-14
Costing for Noncovering Index Scans. . . . . . . . . . . . . . . . . . . . . . . . . 6-16
Added Cost for Forwarded Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-16
Query Costing for Queries Using order by . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-16 Prefix Subset and Sorts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-17
Key Ordering and Sorts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-18
Specifying Ascending or Descending Order for Index Keys . . . . . . 6-18
How the Optimizer Costs Sort Operations . . . . . . . . . . . . . . . . . . . . . . . . 6-20
Allpages-Locked Tables with Clustered Indexes. . . . . . . . . . . . . . . . . . . 6-20
Sorts When the Index Covers the Query. . . . . . . . . . . . . . . . . . . . . . . . . . 6-22
Sorts and Noncovering Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-23
Backward Scans and Joins. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-23
Deadlocks and Descending Scans . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-23
Access Methods and Costing for or and in Clauses. . . . . . . . . . . . . . . . . . . . . . . . . 6-23
or syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-24
 
Methods for Processing or Clauses. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-24
When Table Scans Are Used for or Queries . . . . . . . . . . . . . . . . . . . . 6-26
Dynamic Index (OR Strategy) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-26
Multiple Matching Index Scans (Special OR Strategy). . . . . . . . . . . 6-28
How Aggregates Are Optimized . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-28
Combining max and min Aggregates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-29
Queries That Use Both min and max . . . . . . . . . . . . . . . . . . . . . . . . . . 6-30
How Update Operations Are Performed . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-30
Direct Updates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-30
In-Place Updates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-31
Deferred Index Inserts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-34
 Joins and Subqueries in Update and Delete Statements. . . . . . . . . . 6-36
Deletes and Updates in Triggers vs. Referential Integrity . . . . . . . . 6-37
Optimizing Updates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-37
Effects of Update Types and Indexes on Update Modes . . . . . . . . . 6-38
Choosing Fixed-Length Datatypes for Direct Updates . . . . . . . . . . 6-39
Using max_rows_per_page to Increase Direct Updates. . . . . . . . . . . . 6-39
Using sp_sysmon While Tuning Updates. . . . . . . . . . . . . . . . . . . . . . . . . . 6-40
7. Access Methods and Query Costing for Joins and Subqueries
Costing and Optimizing Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-1
 Join Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-2
Multicolumn Densities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-2
 Join Permutations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-3
Nested-Loop Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-6 Cost Formula for Nested-Loop Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-8
Choice of Inner and Outer Tables for Nested-Loop Joins. . . . . . . . . . . . . 7-8
Access Methods and Costing for Sort-Merge Joins . . . . . . . . . . . . . . . . . . . . . . . . . . 7-8 How a Full-Merge Join Is Performed. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-11
How a Right-Merge or Left-Merge Join Is Performed. . . . . . . . . . . . . . . 7-12
How a Sort-Merge Join Is Performed. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-12
Example of Mixed Joins. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-13
 
showplan Messages for Sort-Merge Joins. . . . . . . . . . . . . . . . . . . . . . . 7-14
Costing for Merge Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-15
Costing for a Full-Merge Join with Unique Values . . . . . . . . . . . . . . . . . 7-15
Example: Allpages-Locked Tables with Clustered Indexes . . . . . . . . . . 7-16
Costing for a Full-Merge Join with Duplicate Values . . . . . . . . . . . . . . . 7-16
Costing for Sorts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-17
When Merge Joins Cannot Be Used . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-18
Use of Worker Processes for Merge Joins . . . . . . . . . . . . . . . . . . . . . . . . . 7-19
Recommendations for Improved Merge Performance . . . . . . . . . . . . . . 7-19
Enabling and Disabling Merge Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-21
Enabling at the Server Level . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-21
Enabling Merge Joins for a Session . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-21
The Reformatting Strategy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-21
When Flattening Can Be Done. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-23
Exceptions to Flattening . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-23
Flattened Subqueries Executed as Regular Joins. . . . . . . . . . . . . . . . 7-25
Flattened Subqueries Executed as Existence Joins . . . . . . . . . . . . . . 7-25
Flattened Subqueries Executed Using Unique Reformatting . . . . . 7-26
Flattened Subqueries Using Duplicate Elimination d. . . . . . . . . . . . 7-27
Flattening Expression Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-28
Materializing Subquery Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-28
Noncorrelated Expression Subqueries . . . . . . . . . . . . . . . . . . . . . . . . 7-28
Short Circuiting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-30
Subquery Results Caching. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-31
Optimizing Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-32
8. Cursors and Performance
 
Performance and Tuning Guide xiii
Adaptive Server Enterprise Version 12
Resources Required at Each Stage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-4 Memory Use and Execute Cursors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-5
Cursor Modes: Read-Only and Update . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-6
Read-Only vs. Update . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-6
Allpages-Locked Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-6
Data-Only-Locked Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-7
Comparing Performance with and Without Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . 8-8
Sample Stored Procedure Without a Cursor. . . . . . . . . . . . . . . . . . . . . . . . 8-8
Sample Stored Procedure With a Cursor. . . . . . . . . . . . . . . . . . . . . . . . . . . 8-9
Cursor vs. Non-Cursor Performance Comparison . . . . . . . . . . . . . . . . . 8-11
Cursor vs. Non-Cursor Performance Explanation . . . . . . . . . . . . . . 8-11
Locking with Read-Only Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-11
Isolation Levels and Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-13
Partitioned Heap Tables and Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-13
Optimizing Tips for Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-14
Optimizing for Cursor Selects Using a Cursor. . . . . . . . . . . . . . . . . . . . . 8-14
Using union Instead of or Clauses or in Lists . . . . . . . . . . . . . . . . . . . . . . 8-14
Declaring the Cursor’s Intent . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-15
Specifying Column Names in the for update Clause . . . . . . . . . . . . . . . . 8-15
Using set cursor rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-16
Keeping Cursors Open Across Commits and Rollbacks. . . . . . . . . . . . . 8-16
Opening Multiple Cursors on a Single Connection. . . . . . . . . . . . . . . . . 8-17
9. Indexing for Performance
Symptoms of Poor Indexing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-2
Detecting Indexing Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-2
Index Is Not Selective Enough . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-3
Index Does Not Support Range Queries. . . . . . . . . . . . . . . . . . . . . . . . 9-3
Too Many Indexes Slow Data Modification . . . . . . . . . . . . . . . . . . . . . 9-4
Index Entries Are Too Large. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-4
Exception for Wide Data Rows and Wide Index Rows. . . . . . . . . . . . 9-5
Index Limits and Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-5
Choosing Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-6 Index Keys and Logical Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-7
Guidelines for Clustered Indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-7
Choosing Clustered Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-8
Other Indexing Guidelines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-9
Choosing Nonclustered Indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-10
Choosing Composite Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-11
Advantages of Composite Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-13
Disadvantages of Composite Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-13
Techniques for Choosing Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-13
Choosing an Index for a Range Query . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-14
Adding a Point Query with Different Indexing Requirements . . . . . . . 9-15
Index and Statistics Maintenance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-16
Monitoring Applications and Indexes over Time . . . . . . . . . . . . . . . . . . 9-16
Dropping Indexes That Hurt Performance. . . . . . . . . . . . . . . . . . . . . . . . 9-17
Maintaining Index and Column Statistics. . . . . . . . . . . . . . . . . . . . . . . . . 9-17
Rebuilding Indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-18
Choosing Space Management Properties for Indexes. . . . . . . . . . . . . . . 9-19
Additional Indexing Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-19
Creating Artificial Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-19
Dropping and Rebuilding Indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-20
10. Managing Statistics to Improve Performance
The Importance of Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10-1 When Do Statistics Need to Be Updated? . . . . . . . . . . . . . . . . . . . . . . . . . 10-2
Adding Statistics for Unindexed Columns . . . . . . . . . . . . . . . . . . . . . . . . 10-2
The update statistics Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10-2
Column Statistics and Statistics Maintenance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10-3
Creating and Updating Column Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10-4
Identifying When Additional Statistics May Be Useful . . . . . . . . . . . . . 10-5
Adding Statistics for a Column with update statistics . . . . . . . . . . . . . . . 10-5
Adding Statistics for Minor Columns with update index statistics . . . . . 10-6
Adding Statistics for All Columns with update all statistics . . . . . . . . . . 10-6
Choosing Step Numbers for Histograms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10-6
Disadvantages of Too Many Steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10-7
Choosing a Step Number. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10-7
Scan Types, Sort Requirements, and Locking During update statistics . . . . . . . . . . 10-8
Sorts for Unindexed or Nonleading Columns . . . . . . . . . . . . . . . . . . . . . 10-8
Locking, Scans, and Sorts During update index statistics . . . . . . . . . . . . . 10-9
 
Locking, Scans and Sorts During update all statistics . . . . . . . . . . . . . . . . 10-9
Using the with consumers Clause. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10-9
Reducing update statistics Impact on Concurrent Processes . . . . . . . . . . 10-9
Using the delete statistics Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10-10
When Row Counts May Be Inaccurate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10-11
Parallel Query Concepts and Tuning
11. Introduction to Parallel Query Processing
Types of Queries That Can Benefit from Parallel Processing . . . . . . . . . . . . . . . . . . 11-2
Adaptive Server’s Worker Process Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11-3
Parallel Query Execution. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11-4
Hash-Based Table Scans. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11-7
showplan Messages for Parallel Queries. . . . . . . . . . . . . . . . . . . . . . . . . . 11-10
Controlling the Degree of Parallelism . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11-11
Configuration Parameters for Controlling Parallelism. . . . . . . . . . . . . 11-12
How Limits Apply to Query Plans . . . . . . . . . . . . . . . . . . . . . . . . . . 11-12
How the Limits Work In Combination . . . . . . . . . . . . . . . . . . . . . . . 11-13
Examples of Setting Parallel Configuration Parameters . . . . . . . . 11-14
Using set Options to Control Parallelism for a Session . . . . . . . . . . . . . 11-14
set Command Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11-15
Worker Process Availability and Query Execution . . . . . . . . . . . . . . . . 11-16
Other Configuration Parameters for Parallel Processing . . . . . . . . . . . 11-17
Commands for Working with Partitioned Tables . . . . . . . . . . . . . . . . . . . . . . . . . . 11-17
Balancing Resources and Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11-19
CPU Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11-19
Tuning Example: CPU and I/O Saturation. . . . . . . . . . . . . . . . . . . . . . . 11-20
Guidelines for Parallel Query Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11-20
Hardware Guidelines. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11-20
Examples of Parallel Query Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11-22
Improving the Performance of a Table Scan. . . . . . . . . . . . . . . . . . . 11-22
 
Improving the Performance of a Nonclustered Index Scan. . . . . . 11-23
Guidelines for Partitioning and Parallel Degree . . . . . . . . . . . . . . . . . . 11-23
Experimenting with Data Subsets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11-24
System Level Impacts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11-25 Locking Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11-25
Device Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11-26
Queries That Use set rowcount. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11-27
Queries That Set Local Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11-27
Achieving Consistent Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11-28
12. Parallel Query Optimization
What Is Parallel Query Optimization? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12-1 Optimizing for Response Time vs. Total Work. . . . . . . . . . . . . . . . . . . . . 12-2
When Is Parallel Query Optimization Performed? . . . . . . . . . . . . . . . . . . . . . . . . . . 12-2
Overhead Cost of Parallel Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12-3 Factors That Are Not Considered. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12-3
Parallel Access Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12-3
Parallel Partition Scan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12-4
Requirements for Consideration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12-5
Requirements for Consideration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12-7
Hash-Based Table Scans on Allpages-Locked Tables . . . . . . . . . . . . 12-8
Hash-Based Table Scans on Data-Only-Locked Tables. . . . . . . . . . . 12-9
Requirements for Consideration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12-9
Parallel Range-Based Scans. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12-12
Requirements for Consideration . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12-12
Additional Parallel Strategies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12-14
Selecting Parallel Access Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12-15
Degree of Parallelism for Parallel Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12-16
Upper Limit to Degree of Parallelism . . . . . . . . . . . . . . . . . . . . . . . . . . . 12-17
 
Worker Processes for Partition-Based Scans. . . . . . . . . . . . . . . . . . . 12-17
Worker Processes for Hash-Based Scans. . . . . . . . . . . . . . . . . . . . . . 12-18
Worker Processes for Range-Based Scans. . . . . . . . . . . . . . . . . . . . . 12-18
Degree of Parallelism for Nested-Loop Joins . . . . . . . . . . . . . . . . . . . . . 12-19
Alternative Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12-21
Parallel Queries and Existence Joins . . . . . . . . . . . . . . . . . . . . . . . . . 12-22
Degree of Parallelism Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12-22
Partitioned Heap Table Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12-22
Nonpartitioned Heap Table Example . . . . . . . . . . . . . . . . . . . . . . . . 12-23
Table with Clustered Index Example . . . . . . . . . . . . . . . . . . . . . . . . 12-23
Runtime Adjustments to Worker Processes . . . . . . . . . . . . . . . . . . . . . . 12-24
Parallel Query Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12-24
Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12-26
union Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12-27
How Adaptive Server Adjusts a Query Plan . . . . . . . . . . . . . . . . . . . . . 12-29
Evaluating the Effect of Runtime Adjustments . . . . . . . . . . . . . . . . . . . 12-30
Recognizing and Managing Runtime Adjustments . . . . . . . . . . . . . . . 12-30
Using set process_limit_action . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12-30
Checking Runtime Adjustments with sp_sysmon. . . . . . . . . . . . . . . . . . 12-31
Diagnosing Parallel Performance Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12-32
Query Does Not Run in Parallel (When You Think It Should) . . . . . . 12-32
Parallel Performance Is Not As Good As Expected. . . . . . . . . . . . . . . . 12-33
Calling Technical Support for Diagnosis. . . . . . . . . . . . . . . . . . . . . . . . . 12-34
Resource Limits for Parallel Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12-34
13. Parallel Sorting
Creating a Distribution Map. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13-4
 
Dynamic Range Partitioning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13-5
Worker Process Requirements for Creating Indexes. . . . . . . . . . . . . 13-7
Using with consumers While Creating Indexes . . . . . . . . . . . . . . . . . . 13-9
Worker Process Requirements for select Query Sorts . . . . . . . . . . . . . . 13-10
Worker Processes for Merge-Join Sorts . . . . . . . . . . . . . . . . . . . . . . . 13-10
For Other Worktable Sorts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13-10
Caches, Sort Buffers, and Parallel Sorts. . . . . . . . . . . . . . . . . . . . . . . . . . 13-11
Cache Bindings. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13-11
How the Number of Sort Buffers Affects Sort Performance . . . . . 13-12
Sort Buffer Configuration Guidelines . . . . . . . . . . . . . . . . . . . . . . . . 13-12
Using Less Than the Configured Number of Sort Buffers . . . . . . . 13-13
Configuring the number of sort buffers Parameter. . . . . . . . . . . . . . . 13-14
Procedure for Estimating Merge Levels and I/O . . . . . . . . . . . . . . 13-15
Configuring Caches for Large I/O During Parallel Sorting . . . . . 13-16
Balancing Sort Buffers and Large I/O Configuration. . . . . . . . . . . 13-17
Disk Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13-17
Number of Devices in the Target Segment. . . . . . . . . . . . . . . . . . . . 13-18
Recovery Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13-19
Using set sort_resources on . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13-20
Sort Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13-21
Query Tuning Tools
Query Tuning Tools Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14-1
How Tools May Interact . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14-2
Using showplan and noexec Together . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14-2
noexec and statistics io . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14-3
How Tools Relate to Query Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14-3
 
15. Determining or Estimating the Sizes of Tables and Indexes
Why Object Sizes Are Important to Query Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . 15-1
Tools for Determining the Sizes of Tables and Indexes . . . . . . . . . . . . . . . . . . . . . . 15-2
Effects of Data Modifications on Object Sizes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15-2
Using optdiag to Display Object Sizes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15-3 Advantages of optdiag . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15-3
Disadvantages of optdiag . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15-4
Using sp_spaceused to Display Object Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15-4 Advantages of sp_spaceused. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15-5
Disadvantages of sp_spaceused . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15-5
Using sp_estspace to Estimate Object Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15-6 Advantages of sp_estspace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15-7
Disadvantages of sp_estspace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15-7
Using Formulas to Estimate Object Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15-8 Factors That Can Affect Storage Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15-8
Storage Sizes for Datatypes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15-9
Tables and Indexes Used in the Formulas. . . . . . . . . . . . . . . . . . . . . . . . 15-10
Calculating Table and Clustered Index Sizes for Allpages-Locked Tables. . 15-11
Step 1: Calculate the Data Row Size . . . . . . . . . . . . . . . . . . . . . . . . . 15-11
Step 2: Compute the Number of Data Pages . . . . . . . . . . . . . . . . . . 15-12
Step 3: Compute the Size of Clustered Index Rows . . . . . . . . . . . . 15-12
Step 4: Compute the Number of Clustered Index Pages . . . . . . . . 15-13
Step 5: Compute the Total Number of Index Pages . . . . . . . . . . . . 15-14
Step 6: Calculate Allocation Overhead and Total Pages. . . . . . . . . 15-14
Step 7: Calculate the Size of the Leaf Index Row. . . . . . . . . . . . . . . 15-15
Step 8: Calculate the Number of Leaf Pages in the Index. . . . . . . . 15-17
Step 9: Calculate the Size of the Non-Leaf Rows. . . . . . . . . . . . . . . 15-17
Step 10: Calculate the Number of Non-Leaf Pages . . . . . . . . . . . . . 15-17
Step 11: Calculate the Total Number of Non-Leaf Index Pages. . . 15-18
Step 12: Calculate Allocation Overhead and Total Pages. . . . . . . . 15-18
Calculating the Sizes of Data-Only-Locked Tables . . . . . . . . . . . . . . . . 15-18
Step 1: Calculate the Data Row Size . . . . . . . . . . . . . . . . . . . . . . . . . 15-19
Step 2: Compute the Number of Data Pages . . . . . . . . . . . . . . . . . . 15-20
Step 3: Calculate Allocation Overhead and Total Pages. . . . . . . . . 15-20
Step 4: Calculate the Size of the Index Row . . . . . . . . . . . . . . . . . . . 15-21
Step 5: Calculate the Number of Leaf Pages in the Index. . . . . . . . 15-22
Step 6: Calculate the Number of Non-Leaf Pages in the Index . . . 15-22
Step 7: Calculate the Total Number of Non-Leaf Index Pages. . . . 15-23
Step 8: Calculate Allocation Overhead and Total Pages. . . . . . . . . 15-24
Other Factors Affecting Object Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15-24
 
Effects of Space Management Properties . . . . . . . . . . . . . . . . . . . . . 15-24
Using Average Sizes for Variable Fields . . . . . . . . . . . . . . . . . . . . . . 15-26
Very Small Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15-26
16. Using the set statistics Commands
set statistics Command Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16-1
Using Simulated Statistics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16-1
Checking Subquery Cache Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16-2
Checking Compile and Execute Time . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16-2 Converting Ticks to Milliseconds. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16-3
Reporting Physical and Logical I/O Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16-3
Total Actual I/O Cost Value . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16-4
Statistics for Writes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16-4
Statistics for Reads . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16-4
statistics io Output for Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16-6
Scan Count. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16-7
Queries Reporting a Scan Count of More Than 1 . . . . . . . . . . . . . . . 16-8
Queries Reporting Scan Count of 0 . . . . . . . . . . . . . . . . . . . . . . . . . . . 16-9
Relationship Between Physical and Logical Reads . . . . . . . . . . . . . . . . . 16-9
Logical Reads, Physical Reads, and 2K I/O. . . . . . . . . . . . . . . . . . . . 16-9
Physical Reads and Large I/O . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16-9
Reads and Writes on Worktables . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16-10
Effects of Caching on Reads . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16-10
statistics io and Merge Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16-11
17. Using set showplan 
Step Message . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-3
TO TABLE Message . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-6
Update Mode Messages. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-7
Direct Update Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-8
Optimized Using Messages. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-10
Simulated Statistics Message . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-10
Abstract Plan Messages. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-10
GROUP BY Message . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-12
Grouped Aggregate Message . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-13
compute by Message . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-14
Ungrouped Aggregate Message. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-16
Worktable Message for distinct. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-18
Worktable Message for order by . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-19
Sorting Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-20
GETSORTED Message. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-21
showplan Messages Describing Access Methods, Caching, and I/O Cost . . . . . . . 17-22 Auxiliary Scan Descriptors Message . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-23
Nested Iteration Message . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-25
Merge Join Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-25
Dynamic Index Message (OR Strategy). . . . . . . . . . . . . . . . . . . . . . . . . . 17-36
Reformatting Message . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-37
I/O Size Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-41
Cache Strategy Messages. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-41
Executed in Parallel Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-43
Coordinating Process Message. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-43
Worker Processes Message . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-44
Scan Type Message. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-44
Output for Flattened or Materialized Subqueries . . . . . . . . . . . . . . . . . 17-49
Flattened Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-49
Materialized Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-53
Subquery Execution Message . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-57
Type of Subquery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-57
Expression Subqueries and the ONCE Aggregate . . . . . . . . . . . . . 17-60
Subqueries with distinct and the ONCE-UNIQUE Aggregate . . . 17-61
Existence Join Message . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17-62
18. Tuning with dbcc traceon 
Tuning with dbcc traceon(302) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18-1 dbcc traceon(310) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18-1
Invoking the dbcc Trace Facility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18-2
General Tips for Tuning with dbcc traceon(302) . . . . . . . . . . . . . . . . . . . . 18-2
Checking for Join Columns and Search Arguments . . . . . . . . . . . . . . . . 18-3
Determining How the Optimizer Estimates I/O Costs. . . . . . . . . . . . . . 18-3
Structure of dbcc traceon(302) Output. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18-4
Additional Blocks and Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18-5
The Table Information Block . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18-6
Identifying the Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18-6
Basic Table Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18-6
Partition Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18-7
The Clause Block . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18-8 Search Clause Identification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18-8
When Search Clauses Are Not Optimizable . . . . . . . . . . . . . . . . . . . 18-8
Values Unknown At Optimize Time . . . . . . . . . . . . . . . . . . . . . . . . . . 18-9
 Join Clause Identification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18-9
Sort Avert Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18-9
The Column Block . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18-10 Selectivities When Statistics Exist and Values Are Known. . . . . . . . . . 18-10
When the Optimizer Uses Default Values. . . . . . . . . . . . . . . . . . . . . . . . 18-11
Unknown Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18-11
Out-of-Range Messages. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18-12
The Index Selection Block . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18-14
Scan and Filter Selectivity Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18-15
How Scan and Filter Selectivity Can Differ . . . . . . . . . . . . . . . . . . . 18-15
Other Information in the Index Selection Block. . . . . . . . . . . . . . . . . . . 18-16
The Best Access Block . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18-17
dbcc traceon(310 ) and Final Query Plan Costs . . . . . . . . . . . . . . . . . . . . . . . . . . . 18-18
Flattened Subquery Join Order Message. . . . . . . . . . . . . . . . . . . . . . . . . 18-19
Worker Process Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18-20
Final Plan Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18-20
System Tables That Store Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19-1
systabstats Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19-1
sysstatistics Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19-2
optdiag Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19-3
Data Page CR Count . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19-6
Table-Level Derived Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19-6
Space Utilization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19-7
Index-Level Derived Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19-9
Space Utilization for an Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19-10
Large I/O Efficiency for an Index . . . . . . . . . . . . . . . . . . . . . . . . . . . 19-11
Column Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19-11
Range Cell and Total Density Values. . . . . . . . . . . . . . . . . . . . . . . . . 19-13
Range and In-Between Selectivity Values. . . . . . . . . . . . . . . . . . . . . 19-15
Histogram Displays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19-15
Understanding Histogram Output . . . . . . . . . . . . . . . . . . . . . . . . . . 19-17
Choosing the Number of Steps for Highly Duplicated Values . . . 19-21
Changing Statistics with optdiag . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19-21 Using the optdiag Binary Mode. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19-22
When You Must Use Binary Mode . . . . . . . . . . . . . . . . . . . . . . . . . . 19-23
Updating Selectivities with optdiag Input Mode . . . . . . . . . . . . . . . . . . 19-23
Editing Histograms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19-24
Rules Checked During Histogram Loading. . . . . . . . . . . . . . . . . . . 19-26
Re-Creating Indexes Without Losing Statistics Updates . . . . . . . . 19-26
Using Simulated Statistics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19-26
Simulated Statistics Output. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19-27
Using Simulated Statistics in Another Database . . . . . . . . . . . . . . . 19-30
Dropping Simulated Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19-30
Running Queries with Simulated Statistics . . . . . . . . . . . . . . . . . . . . . . 19-31
showplan Messages for Simulated Statistics . . . . . . . . . . . . . . . . . . . 19-31
Character Data Containing Quotation Marks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19-31
 
20. Advanced Optimizing Tools
Specifying Optimizer Choices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20-2
Risks of Using forceplan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20-3
Things to Try Before Using forceplan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20-4
Specifying the Number of Tables Considered by the Optimizer . . . . . . . . . . . . . . . . 20-4
Specifying an Index for a Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20-5 Risks of Specifying Indexes in Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . 20-6
Things to Try Before Specifying Indexes. . . . . . . . . . . . . . . . . . . . . . . . . . 20-7
Specifying I/O Size in a Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20-7 Index Type and Large I/O. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20-9
When prefetch Specification Is Not Followed . . . . . . . . . . . . . . . . . . . . . . 20-9
set prefetch on . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20-10
Specifying Cache Strategy in select, delete, and update Statements. . . . 20-11
Controlling Large I/O and Cache Strategies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20-12 Getting Information on Cache Strategies . . . . . . . . . . . . . . . . . . . . . . . . 20-12
Enabling and Disabling Merge Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20-12
Enabling and Disabling Join Transitive Closure . . . . . . . . . . . . . . . . . . . . . . . . . . . 20-13
Suggesting a Degree of Parallelism for a Query . . . . . . . . . . . . . . . . . . . . . . . . . . . 20-14
Query Level parallel Clause Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . 20-15
Concurrency Optimization for Small Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20-16
Changing the Locking Scheme. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20-17
Query Tuning With Abstract Plans
21. Introduction to Abstract Plans
What Are Abstract Plans? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21-1
The Relationship Between Query Text and Query Plans . . . . . . . . . . . . . . . . . . . . . 21-2
The Limits of Options for Influencing Query Plans . . . . . . . . . . . . . . . . 21-2
Full vs. Partial Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21-3
Creating a Partial Plan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21-4
Abstract Plan Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21-5
Managing Abstract Plans. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21-6
22. Creating and Using Abstract Plans
Using set Commands to Capture and Associate Plans . . . . . . . . . . . . . . . . . . . . . . 22-1 Enabling Plan Capture Mode with set plan dump . . . . . . . . . . . . . . . . . . . 22-2
Associating Queries with Stored Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . 22-2
Using Replace Mode During Plan Capture. . . . . . . . . . . . . . . . . . . . . . . . 22-3
When To Use Replace Mode. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22-3
Using Dump, Load, and Replace Modes Simultaneously . . . . . . . . . . . 22-4
Using dump and load to the Same Group . . . . . . . . . . . . . . . . . . . . . . 22-4
Using dump and load to Different Groups. . . . . . . . . . . . . . . . . . . . . . 22-5
set plan exists check Option . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22-6
Using Other set Options with Abstract Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22-6 Using showplan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22-6
Using noexec. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22-7
Using forceplan. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22-7
Creating Plans Using SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22-8 Using create plan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22-8
Using the plan Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22-9
23. Managing Abstract Plans with System Procedures
System Procedures for Managing Abstract Plans . . . . . . . . . . . . . . . . . . . . . . . . . . 23-1
Managing an Abstract Plan Group . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23-1
Creating an Abstract Plan Group. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23-2
Dropping an Abstract Plan Group . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23-2
Getting Information about an Abstract Plan Group . . . . . . . . . . . . . . . . 23-2
Renaming an Abstract Plan Group . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23-5
Finding Abstract Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23-5
Viewing a Plan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23-6
Dropping an Individual Abstract Plan . . . . . . . . . . . . . . . . . . . . . . . . . . . 23-7
Comparing Two Abstract Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23-7
Changing an Existing Plan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23-9
Managing All Plans in a Group . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23-9
Copying All Plans in a Group. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23-9
Comparing All Plans in a Group . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23-10
Dropping All Abstract Plans in a Group. . . . . . . . . . . . . . . . . . . . . . . . . 23-13
Importing and Exporting Groups of Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23-13 Exporting Plans to a User Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23-13
Importing Plans from a User Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23-14
 
Keywords . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24-1
Operands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24-2
Introduction to Writing Abstract Plans. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25-1
The Abstract Plan Language. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25-2
Queries, Access Methods, and Abstract Plans . . . . . . . . . . . . . . . . . . 25-3
Identifying Tables in Abstract Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25-3
Identifying Indexes in Abstract Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25-5
Specifying Join Order in Abstract Plans . . . . . . . . . . . . . . . . . . . . . . . . . . 25-5
A Shorthand Notation for Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25-6
 Join Order Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25-6
Specifying Partial Plans and Hints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25-10
Grouping Multiple Hints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25-10
Creating Abstract Plans for Subqueries. . . . . . . . . . . . . . . . . . . . . . . . . . 25-12
Materialized Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25-12
Flattened Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25-13
Example: Changing the Join Order in a Flattened Subquery. . . . . 25-14
Nested Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25-15
More Subquery Examples: Reading Ordering and Attachment . . 25-16
Modifying Subquery Nesting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25-17
Abstract Plans for Queries Containing Aggregates. . . . . . . . . . . . . . . . 25-19
Specifying the Reformatting Strategy . . . . . . . . . . . . . . . . . . . . . . . . . . . 25-21
OR Strategy Limitation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25-22
Tips on Writing Abstract Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25-22
Comparing Plans “Before” and “After” . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25-23
Effects of Enabling Server-Wide Capture Mode. . . . . . . . . . . . . . . . . . . 25-24
The Time and Space to Copy Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25-24
Abstract Plans for Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25-25
Procedures and Plan Ownership . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25-25
 
Procedures with Variable Execution Paths and Optimization . . . . . . . 25-25
Ad Hoc Queries and Abstract Plans. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25-26
 
List of Figures
Figure 2-1: Database design.............................................................................................................2-2
Figure 2-3: A table that violates first normal form .......................................................................2-5
Figure 2-4: Correcting First Normal Form violations by creating two tables...........................2-5
Figure 2-5: A table that violates Second Normal Form................................................................2-6
Figure 2-6: Correcting Second Normal Form violations by creating two tables......................2-6
Figure 2-7: A table that violates Third Normal Form...................................................................2-7
Figure 2-8: Correcting Third Normal Form violations by creating two tables.........................2-7
Figure 2-9: Balancing denormalization issues...............................................................................2-9
Figure 2-12: Denormalizing by collapsing tables..........................................................................2-13
Figure 2-13: Denormalizing by duplicating tables .......................................................................2-13
Figure 2-14: Horizontal and vertical partitioning of tables.........................................................2-14
Figure 2-15: Horizontal partitioning of active and inactive data ...............................................2-15
Figure 2-16: Vertically partitioning a table.....................................................................................2-16
Figure 2-17: Using triggers to maintain normalized data............................................................2-17
Figure 2-18: Maintaining denormalized data via application logic...........................................2-17
Figure 2-19: Using batch reconciliation to maintain data ............................................................2-18
Figure 3-1: An Adaptive Server data page ....................................................................................3-4
Figure 3-2: Large object storage.......................................................................................................3-5
Figure 3-5: Page linkage in allpages-locked tables .....................................................................3-12
Figure 3-6: Selecting from an allpages-locked heap...................................................................3-13
Figure 3-7: Selecting rows from a data-only-locked table .........................................................3-13
Figure 3-8: Inserting a row into an allpages-locked heap table ................................................3-14
Figure 3-9: Hints direct inserts to pages with space...................................................................3-15
Figure 3-10: Deleting rows from an allpages-locked heap table.................................................3-16
Figure 3-11: Deleting rows from a data-only-locked heap table ................................................3-17
Figure 3-12: Original row stores pointer to the forwarded row’s location ...............................3-19
Figure 3-13: Alignment when performing large I/O...................................................................3-20
Figure 3-14: LRU strategy takes a clean page from the LRU end of the cache.........................3-21
Figure 3-15: MRU strategy places pages just before the wash marker......................................3-22
Figure 3-16: Finding a needed page in cache ................................................................................3-22
Figure 3-17: Inserts to a heap page in the data cache...................................................................3-24
Figure 3-18: Data vs. log I/O ...........................................................................................................3-28
 
Figure 4-2: Data-only-locked tables always contain root and leaf levels ..................................4-4
Figure 4-3: Clustered index on last name, on an allpages-locked table.....................................4-6
Figure 4-4: Selecting a row using a clustered index, allpages-locked table ..............................4-7
Figure 4-5: Inserting a row into an allpages-locked table with a clustered index....................4-8
Figure 4-6: Page splitting in an allpages-locked table with a clustered index..........................4-9
Figure 4-7: Adding an overflow page to a clustered index, allpages-locked table................4-11
Figure 4-8: Deleting a row from a table with a clustered index................................................4-12
Figure 4-9: Deleting the last row on a page (before the delete) ................................................4-13
Figure 4-10: Deleting the last row on a page (after the delete) ...................................................4-14
Figure 4-11: Nonclustered index structure ....................................................................................4-16
Figure 4-12: Selecting rows using a nonclustered index..............................................................4-17
Figure 4-13: An insert into a heap table with a nonclustered index ..........................................4-18
Figure 4-14: Deleting a row from a table with a nonclustered index.........................................4-19
Figure 4-15: Insert using a clustered index on a data-only-locked table...................................4-21
Figure 4-16: Matching index access does not have to read the data row..................................4-23
Figure 4-17: A nonmatching index scan.........................................................................................4-24
Figure 4-18: Caching used for a point query via a nonclustered index.....................................4-25
Figure 4-19: Finding the root index page in cache........................................................................4-25
Figure 4-20: Caching with separate caches for data and index pages .......................................4-26
Figure 4-21: Index page recycling in the cache..............................................................................4-27
Figure 5-1: Query execution steps...................................................................................................5-3
Figure 6-2: Page chain crossing extents in an allpages-locked table..........................................6-8
Figure 6-3: Range query on the clustered index of an allpages-locked table .........................6-11
Figure 6-4: Range query with a covering index ..........................................................................6-12
Figure 6-5: Forward and backward scans on an index ..............................................................6-19
Figure 6-6: An order by query using a clustered index, allpages locking...............................6-21
Figure 6-7: An order by desc query using a clustered index.....................................................6-22
Figure 6-8: Resolving or queries using the OR strategy ............................................................6-27
Figure 6-9: Deferred index update ................................................................................................6-35
Figure 7-1: Nesting of tables during a nested-loop join...............................................................7-7
Figure 7-2: Merge join types...........................................................................................................7-10
Figure 7-3: A serial merge scan on two tables with clustered indexes ....................................7-11
Figure 7-4: Full merge scan using a nonclustered index on the inner table............................7-12
Figure 7-5: Multiple steps in processing a merge join................................................................7-14
Figure 8-1: Cursor example..............................................................................................................8-1
Figure 8-2: Cursor flowchart............................................................................................................8-3
Figure 8-4: Read-only cursors and locking experiment input ..................................................8-12
Figure 11-1: Worker process model.................................................................................................11-4
Figure 11-2: Relative execution times for serial and parallel query execution.........................11-5
Figure 11-3: A serial task scans data pages....................................................................................11-7
Figure 11-4: Worker processes scan an unpartitioned table........................................................11-7
Figure 11-5: Multiple worker processes access multiple partitions ...........................................11-8
Figure 11-6: Hash-based, nonclustered index scan ......................................................................11-9
Figure 11-7: Join query using different parallel access methods on each table......................11-10
Figure 11-8: Steps for creating and loading a new partitioned table.......................................11-18
Figure 12-1: Parallel partition scan..................................................................................................12-5
Figure 12-3: Parallel hash-based table scan on an allpages-locked table ..................................12-9
Figure 12-4: Nonclustered index hash-based scan .....................................................................12-11
Figure 12-5: A parallel right-merge join.......................................................................................12-13
Figure 12-6: Worker process usage for a nested-loop join .........................................................12-20
Figure 13-1: Parallel sort strategy....................................................................................................13-4
Figure 14-1: Query processing analysis tools and query processing.........................................14-4
Figure 17-1: Subquery showplan output structure.....................................................................17-56
Figure 18-1: Structure of blocks of output in dbcc traceon(302).................................................18-5
 
 
List of Tables
Table 1: Font and syntax conventions in this manual............................................................. xli
Table 2: Types of expressions used in syntax statements.....................................................xliii
Table 3-1: Overhead and user data space on data and index pages.........................................3-3
Table 3-2: Use of sysindexes pointers in data access ................................................................3-10
Table 5-1: Search argument equivalents.......................................................................................5-7
Table 5-3: Precision and scale of integer and money types .....................................................5-25
Table 5-4: Indexes considered for mismatched column datatypes.........................................5-27
Table 6-1: Special access methods for aggregates .....................................................................6-29
Table 6-2: Effects of indexing on update mode.........................................................................6-39
Table 7-1: Tables considered at a time during a join...................................................................7-3
Table 8-1: Locks and memory use for isql and Client-Library client cursors .........................8-5
Table 8-2: Sample execution times against a 5000-row table...................................................8-11
Table 8-3: Locks held on data and index pages by cursors .....................................................8-12
Table 8-4: Effects of for update clause and shared on cursor locking....................................8-16
Table 9-1: Effects of key size on index size and levels................................................................9-4
Table 9-2: Composite nonclustered index ordering and performance ..................................9-12
Table 9-3: Comparing index strategies for two queries ...........................................................9-15
Table 10-1: Scans, sorts, and locking during update statistics ..................................................10-8
Table 11-1: Configuration parameters for parallel execution..................................................11-12
Table 11-2: set options for parallel execution tuning................................................................11-15
Table 11-3: Scaling of engines and worker processes...............................................................11-20
Table 12-1: Parallel access method summary ............................................................................12-15
Table 12-2: Determining applicable partition or hash-based access methods......................12-16
Table 13-1: Number of producers and consumers used for create index................................13-7
Table 13-2: Basic sort resource messages....................................................................................13-20
Table 15-1: sp_spaceused output...................................................................................................15-4
Table 15-2: Storage sizes for Adaptive Server datatypes ...........................................................15-9
Table 15-3: Estimated additional pages for pointer information in LOB columns ..............15-27
Table 16-1: statistics io output for reads .......................................................................................16-5
Table 17-1: Basic showplan messages...........................................................................................17-1
Table 17-3: showplan messages describing access methods ...................................................17-22
Table 17-4: showplan messages for parallel queries.................................................................17-42
Table 17-5: showplan messages for subqueries.........................................................................17-48
Table 17-6: Internal subquery aggregates ..................................................................................17-58
Table 18-1: dbcc traceon(310) output ..........................................................................................18-21
Table 18-2: pathtypes in dbcc traceon(310) output...................................................................18-24
 
Table 19-1: Table and column information ..................................................................................19-4
Table 19-2: Table statistics...............................................................................................................19-5
Table 19-4: Index statistics..............................................................................................................19-8
Table 19-6: Column statistics........................................................................................................19-12
Table 19-8: Histogram summary statistics.................................................................................19-16
Table 19-10: Effects of DDL on systabstats and sysstatistics .....................................................19-32
Table 20-1: Access methods and prefetching...............................................................................20-9
Table 20-2: Optimizer hints for serial and parallel execution .................................................20-14
Table 20-3: Effects of alter table on concurrency optimization settings.................................20-17
Table 23-1: Return status values for sp_cmp_qplans .................................................................23-8
Table 23-2: Report modes for sp_cmp_all_qplans ....................................................................23-11
Table 24-1: Identifiers used.............................................................................................................24-2
About This Book
This book discusses performance and tuning issues for Sybase® Adaptive Server™ Enterprise.
Audience
• Sybase System Administrators
The Performance and Tuning Guide is divided into these volumes:
• Performance and Tuning: Query Tuning
• Performance and Tuning: System Tuning
Performance and Tuning: Query Tuning
This book contains the following parts and chapters:
• Part 1, Basic Concepts
- Chapter 1, “Introduction to Performance Analysis,” describes the major components to be analyzed when addressing performance.
- Chapter 2, “Database Design and Denormalizing for Performance,” provides a brief description of relational databases and good database design.
- Chapter 3, “Data Storage,” describes Adaptive Server page types, how data is stored on pages, and how queries on heap tables are executed.
 
How to Use This Book Adaptive Server Enterprise Version 12
• Part 2, Tuning Query Performance
- Chapter 5, “Understanding the Query Optimizer,” explains the process of query optimization, and how statistics are applied to search arguments and joins for queries.
- Chapter 6, “Access Methods and Query Costing for Single Tables,” describes how Adaptive Server accesses tables in queries that only involve a single table, and how the costs are estimated for various access methods.
- Chapter 7, “Access Methods and Query Costing for Joins and Subqueries,”describes how Adaptive Server accesses tables during joins and subqueries and how the costs are determined.
- Chapter 8, “Cursors and Performance,” describes performance issues with cursors.
- Chapter 9, “Indexing for Performance,” provides guidelines and examples for choosing indexes.
- Chapter 10, “Managing Statistics to Improve Performance,” describes how to use the update statistics command to create and update statistics.
• Part 3