Transcription
Performance and Tuning Guide:Volume 3 - Tools for Monitoring andAnalyzing PerformanceAdaptive Server Enterprise12.5
DOCUMENT ID: 33619-01-1250-03LAST REVISED: July 2001Copyright 1989-2001 by Sybase, Inc. All rights reserved.This publication pertains to Sybase database management software and to any subsequent release until otherwise indicated in neweditions or technical notes. Information in this document is subject to change without notice. The software described herein is furnishedunder a license agreement, and it may be used or copied only in accordance with the terms of that agreement.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 otherinternational customers should contact their Sybase subsidiary or local distributor. Upgrades are provided only at regularly scheduledsoftware release dates. 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, the Sybase logo, ADA Workbench, Adaptable Windowing Environment, Adaptive Component Architecture, Adaptive Server,Adaptive Server Anywhere, Adaptive Server Enterprise, Adaptive Server Enterprise Monitor, Adaptive Server Enterprise Replication,Adaptive Server Everywhere, Adaptive Server IQ, Adaptive Warehouse, AnswerBase, Anywhere Studio, Application Manager,AppModeler, APT Workbench, APT-Build, APT-Edit, APT-Execute, APT-FORMS, APT-Translator, APT-Library, Backup Server,ClearConnect, Client-Library, Client Services, Data Pipeline, Data Workbench, DataArchitect, Database Analyzer, DataExpress,DataServer, DataWindow, DB-Library, dbQueue, Developers Workbench, Direct Connect Anywhere, DirectConnect, DistributionDirector, E-Anywhere, E-Whatever, Embedded SQL, EMS, Enterprise Application Studio, Enterprise Client/Server, Enterprise Connect,Enterprise Data Studio, Enterprise Manager, Enterprise SQL Server Manager, Enterprise Work Architecture, Enterprise Work Designer,Enterprise Work Modeler, EWA, Financial Fusion, Financial Fusion Server, Gateway Manager, ImpactNow, InfoMaker, InformationAnywhere, Information Everywhere, InformationConnect, InternetBuilder, iScript, Jaguar CTS, jConnect for JDBC, KnowledgeBase,MainframeConnect, Maintenance Express, MAP, MDI Access Server, MDI Database Gateway, media.splash, MetaWorks, MySupport,Net-Gateway, Net-Library, ObjectConnect, ObjectCycle, OmniConnect, OmniSQL Access Module, OmniSQL Toolkit, Open Client,Open ClientConnect, Open Client/Server, Open Client/Server Interfaces, Open Gateway, Open Server, Open ServerConnect, OpenSolutions, Optima , PB-Gen, PC APT Execute, PC DB-Net, PC Net Library, Power , power.stop, PowerAMC, PowerBuilder,PowerBuilder Foundation Class Library, PowerDesigner, PowerDimensions, PowerDynamo, PowerJ, PowerScript, PowerSite,PowerSocket, Powersoft, PowerStage, PowerStudio, PowerTips, Powersoft Portfolio, Powersoft Professional, PowerWare Desktop,PowerWare Enterprise, ProcessAnalyst, Report Workbench, Report-Execute, Replication Agent, Replication Driver, Replication Server,Replication Server Manager, Replication Toolkit, Resource Manager, RW-DisplayLib, RW-Library, S-Designor, SDF, Secure SQLServer, Secure SQL Toolset, Security Guardian, SKILS, smart.partners, smart.parts, smart.script, SQL Advantage, SQL Anywhere, SQLAnywhere Studio, SQL Code Checker, SQL Debug, SQL Edit, SQL Edit/TPU, SQL Everywhere, SQL Modeler, SQL Remote, SQLServer, SQL Server Manager, SQL SMART, SQL Toolset, SQL Server/CFT, SQL Server/DBM, SQL Server SNMP SubAgent, SQLStation, SQLJ, STEP, SupportNow, Sybase Central, Sybase Client/Server Interfaces, Sybase Financial Server, Sybase Gateways, SybaseMPP, Sybase SQL Desktop, Sybase SQL Lifecycle, Sybase SQL Workgroup, Sybase User Workbench, SybaseWare, Syber Financial,SyberAssist, SyBooks, System 10, System 11, System XI (logo), SystemTools, Tabular Data Stream, Transact-SQL, Translation Toolkit,UNIBOM, Unilib, Uninull, Unisep, Unistring, URK Runtime Kit for UniCode, Viewer, Visual Components, VisualSpeller, VisualWriter,VQL, WarehouseArchitect, Warehouse Control Center, Warehouse Studio, Warehouse WORKS, Watcom, Watcom SQL, Watcom SQLServer, Web Deployment Kit, Web.PB, Web.SQL, WebSights, WebViewer, WorkGroup SQL Server, XA-Library, XA-Server and XPServer are trademarks of Sybase, Inc. 3/01Unicode 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.Use, duplication, or disclosure by the government is subject to the restrictions set forth in subparagraph (c)(1)(ii) of DFARS 52.2277013 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.
ContentsCHAPTER 33Using Statistics to Improve Performance. 743Importance of statistics . 743Updating . 744Adding statistics for unindexed columns . 744update statistics commands . 745Column statistics and statistics maintenance. 746Creating and updating column statistics. 747When additional statistics may be useful . 748Adding statistics for a column with update statistics . 748Adding statistics for minor columns with update index statistics .749Adding statistics for all columns with update all statistics . 749Choosing step numbers for histograms. 749Disadvantages of too many steps . 749Choosing a step number . 750Scan types, sort requirements, and locking . 750Sorts for unindexed or non leading columns . 751Locking, scans, and sorts during update index statistics . 751Locking, scans and sorts during update all statistics . 752Using the with consumers clause . 752Reducing update statistics impact on concurrent processes 752Using the delete statistics command. 753When row counts may be inaccurate . 753CHAPTER 34Using the set statistics Commands .Command syntax .Using simulated statistics .Checking subquery cache performance.Checking compile and execute time.Converting ticks to milliseconds .Reporting physical and logical I/O statistics .Total actual I/O cost value .Statistics for writes .Statistics for reads .755755756756756757757758759759iii
ContentsCHAPTER 35ivstatistics io output for cursors.Scan count .Relationship between physical and logical reads.statistics io and merge joins .760761763766Using set showplan .Using .Basic showplan messages.Query plan delimiter message.Step message .Query type message .FROM TABLE message.TO TABLE message .Update mode messages .Optimized using messages .showplan messages for query clauses .GROUP BY message.Selecting into a worktable .Grouped aggregate message.compute by message .Ungrouped aggregate message.messages for order by and distinct .Sorting messages.Messages describing access methods, caching, and I/O cost.Auxiliary scan descriptors message .Nested iteration message.Merge join messages .Table scan message .Clustered index message.Index name message .Scan direction messages .Positioning messages .Scanning messages .Index covering message .Keys message.Matching index scans message .Dynamic index message (OR strategy).Reformatting Message .Trigger Log Scan Message .I/O Size Messages .Cache strategy messages.Total estimated I/O cost message.showplan messages for parallel queries .Executed in parallel 01803805806807807808809767
Contentsshowplan messages for subqueries.Output for flattened or materialized subqueries .Structure of subquery showplan output.Subquery execution message .Nesting level delimiter message.Subquery plan start delimiter.Subquery plan end delimiter.Type of subquery.Subquery predicates .Internal subquery aggregates.Existence join message.813814820820821821821821821822826CHAPTER 36Statistics Tables and Displaying Statistics with optdiag .System tables that store statistics.systabstats table.sysstatistics table .Viewing statistics with the optdiag utility .optdiag syntax .optdiag header information.Table statistics.Index statistics.Column statistics .Histogram displays .Changing statistics with optdiag .Using the optdiag binary mode.Updating selectivities with optdiag input mode.Editing histograms.Using simulated statistics.optdiag syntax for simulated statistics.Simulated statistics output.Requirements for loading and using simulated statistics .Dropping simulated statistics.Running queries with simulated statistics.Character data containing quotation marks .Effects of SQL commands on statistics.How query processing affects systabstats 57857859861861862862864CHAPTER 37Tuning with dbcc traceon .
Adding statistics for a column with update statistics . 748 Adding statistics for minor columns with update index statistics. 749 Adding statistics for all columns with update all statistics . 749 Choosing step numbers for histograms. 749
