Fooling, Cheating, Lying and Understanding the Optimizer

Sybase, Inc.
Atlanta, Chicago, Concord, Bethesda, Dallas, Irvine, New York, Washington etc., U.S.

Course Format: Classroom | E-learning | Virtual Class | Online | On-site | Blended | Self-paced

Course Description:

'' This lecture-only course presents ways you can affect the optimizer’s behavior by fooling it, cheating it, and lying to it. We’ll discuss the well-known, understood, and documented force index and forceplan options, and examine how you can affect the optimizer’s behavior by adding, modifying and deleting statistics. Have your queries get to the data with less I/O. Understand documented force indexes and forceplan options, and examine how the optimizer’s behavior can be influenced by adding, modifying and deleting statistics. Discover how to force the use of an index, help the optimizer get better statistics, and have the optimizer not use an index. Discussion will include force index commands, forceplan commands, column statistics, changing the optimizer’s mind, coverage of deck trace flag 310 and in-depth coverage of optdiag output and dbcc traceflag 302.

DBA’s using Adaptive Server Enterprise and experienced SQL developers will want to get in on this - no fooling!


Objectives
Upon completion of this course, you will be able to:

Take a non-optimal running query and determine what can be done, if anything

Interpete showplan output

Interpete statistics IO output

Understand optdiag output

Use traceflag 302 output in understanding why an index was chosen or rejected



Who Should Attend
System and Database Administrators, and experienced SQL developers.



Topics
Day 1: The Optimizer and Optdiag

Optimizer Overview

Force index and force plan options
review of set showplan on, and set statistics io on commands

Adding, modifying, deleting statistics overview

Optdiag - Default statistics in detail

Update, adding and deleting statistics - how , why, and what they do

Day 2: The where clause and dbcc traceflags:

Pick a different index please, make it non-SARGable
optimizer trace flags - overview.

Before the prep phase - details of the dbcc traceflag 302 - why this index, why not that index?.

After the prep-phase - single table details of dbcc traceflag 310

Overview of traceflag 311, and the losers - traceflag 317

Different where clause design, different index design




Prerequisites
Experience as a Sybase DBA in the following areas:

Running update statistics
Creating indexes
Reviewing optdiag output
Reviewing showplan output
Reviewing dbcc 302, 310 command output

Or

Previous attendence of the following courses for either v11.9.2, v12.0 or v12.5

Fast Track to Adaptive Server Enterprise
System and Database Administration: Adaptive Server Enterprise



...''

Phone:1-800-8-SYBASE (800-879-2273)

School Address:

14800 Quorum
Suite 505
Dallas, Texas 75240
United States

8755 West Higgins Road
Suite 1100
Chicago, IL 60631

The Grace Building
1114 Avenue of the Americas, 32nd Floor
New York, NY 10036

Rock Spring Plaza
6550 Rock Spring Drive, Suite 700
Bethesda, Maryland

561 Virginia Rd.
Concord, MA 01742
(978) 287-1500

3820 Mansell Road
Suite 100
Alpharetta , GA 30022

19900 MacArthur Boulevard
Suite 510
Irvine, CA 92612

Jobs & Resumes: Atlanta, Chicago, Concord, Bethesda, Dallas, Irvine, New York, Washington etc.
Houses & Roommates: Atlanta, Chicago, Concord, Bethesda, Dallas, Irvine, New York, Washington etc.

Powering Up with ASE 12.5.2
Managing and Tuning tempdb
Using Abstract Query Plans
Dynamic Reconfiguration for Managing Memory Nonstop Optimal Performance
Interpreting the Major ASE System Tables
Implementing ASE Replicator
Unix to Linux Migration for Adaptive Server Enterprise
Administering Sybase IQ for the Data Warehouse
Interpreting SP_SYSMON
Advanced Performance: Monitoring Tables Facility
Open Client Programming Using Client-Library
Open Server Programming
Understanding Indexes
Harness the Power with Configurable Parameters
Using SybMigrate
Powering Up to ASE Interim Releases
Introduction to SQL Expert
Behind the Scenes with DBCC Commands
Backup Server Unleashed

Notice: The course description on this page was captured from the Internet as historical reference or submitted by visitors. It was archived statically and not updated from day to day.
 

Tips: Try the search form on top of each page to find the latest course information, using course topic and location as keywords. Keep the course topic keyword short to get more results.