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