Sybase ASE P & T Course

1. DBCC for Checking Consistency

  • List and explain the use of dbcc tools at your disposal
  • Understand how to analyze database integrity using dbcc
  • Be able to troubleshoot object consistency problems with dbcc commands
  • Be able to describe strategies for checking database consistency without running a full suite of dbcc
  • commands on a live database
  • Understand the following dbcc commands:
  • checkstorage/checkverify
  • checkalloc/tablealloc
  • checkdb/checktable
  • checkcatalog
  • Understand how to fix allocation errors and inconsistent index and data pages with available dbcc
  • Commands

2. Data Cache Configuration and Tuning

  • Know how to properly configure memory and choose appropriate cache strategies
  • Understand the benefits of named caches, large I/Os, and metadata caches
  • Know how to create, delete, and modify named caches
  • Know how to bind objects to named caches
  • Know how to configure metadata caches
  • Know how to create buffer pools to enable large I/Os
  • Understand how buffer pools are used by queries
  • Understand how to monitor and tune data cache structures
  • Know how to Set optimal I/O size
  • Know how to determine and set cache strategy
  • Understand how to configure cache partitioning
  • Understand how to monitor and tune asynchronous prefetch

3. Data Page Management and Allocation

  • Describe roles played by allocation page, OAM page, and GAM page in managing allocation
  • Describe how tables can grow and become fragmented
  • Understand Structure of the following type of pages: Data, Log, Allocation, OAM and GAM
  • Know which system tables are used in object allocation
  • Understand how objects are allocated
  • Understand what happens when tables grow in relationship to allocation
  • Understand what occurs in relationship to system tables and internally when the 'disk init' command
  • is invoked
  • Understand what happens in relationship to system tables and on the allocation tables when the
  • 'create database' command is invoked
  • Describe how various commands use or modify management pages
  • Explain fragments of a given database and database fragments on a given device
  • Know which management pages contain inconsistencies, and discuss options and trade-offs in fixing
  • these
  • Be able to interpret data page header and row layouts

4. Optimizer Statistics

  • Understand how optimizer statistics are stored in the server
  • Be able to describe optimizer statistics and their purpose
  • Understand the use of optdiag to view optimizer statistics
  • Know the commands to manage optimizer statistics
  • Know the different tools to confirm statistics are being kept up to date
  • Identify upgrade issues with optimizer statistics

5. Index Management

  • Understand Index page layout and structure
  • Understand allocation of non-clustered and clustered indexes
  • Be able to describe index structure
  • be able to interpret index page header and row data
  • Understand index Page Search Algorithm
  • Understand Allpages Clustered and Nonclustered Index Structures
  • Understand DOL placement indexes
  • Understand garbage collection in indexes
  • Troubleshoot index error conditions
  • Understand what sp_fixindex does

6. Non-master and master device failures

  • Understand how to troubleshoot a device offline error
  • Understand how to restore a database with a fragment on a device that has failed
  • Explain how devices and databases are restored
  • Understand how to restore a master device with and without backups of the master database
  • Understand how to recover from several distinct master device failure scenarios
  • Understand the disk reinit and disk refit commands

7. Locking and Concurrency Control

  • Introduction to Locking
  • Locking Configuration and Tuning
  • Locking Reports
  • Using Locking Commands
  • Indexes
  • Indexing for Concurrency Control

8. Engine Configuration

  • Using Engines and CPUs
  • Background concepts
  • Single-CPU process model
  • Adaptive Server SMP process model
  • Asynchronous log service
  • Housekeeper wash task improves CPU utilization
  • Measuring CPU usage
  • Enabling engine-to-CPU affinity
  • Multiprocessor application design guidelines
  • Distributing Engine Resources
  • Successfully distributing resources
  • Managing preferred access to resources
  • Types of execution classes
  • Execution class attributes
  • Setting execution class attributes
  • Determining precedence and scope
  • Example scenario using precedence rules
  • Considerations for engine resource distribution

9. Understanding Sysmon

  • Introduction to sp_sysmon
  • Monitoring Performance with sp_sysmon

10. Multiple temporary Databases, Partitioning in ASE 15, tempdb performance issue.

  • Creating multiple temporary Databases
  • Partitioning in ASE 15
  • tempdb performance issue
  • Resource Governor

11. Maintenance Activities & troubleshooting: reorg, update stats, index stats, optdiag

  • Maintenance Jobs
  • reorg and requirement
  • Understanding stats with update stats
  • optiag : DDDPCL, IPCL
  • Performance Troubleshooting