Session

1
DM205 How to Successfully Take Pass Your
Sybase ASE Certification Exam
Sam Lakkundi Senior Staff Educator Sybase
Certifications lakkundi_at_sybase.com
2
What we will cover
Unit 1 Introduction to the exams Unit
2 ASE-12Associate What do I study ? Unit
3 ASE- 12 Professional What do I study? Unit
4 Sample questions Unit 5 About the questions
3
Class Abstract - Target Audience

• This session is a short discussion on the ASE
  exams.
• Content is from the 1 day session Preparing to
  take the ASE Associate Exams.
• The student should have at least 3 months of
  experience with database administration with ASE
  for the associate exam and 6 months
  administration and tuning experience for the
  professional exam.
• Techniques and suggestions for studying for the
  exam will be covered.
• This session as well as our one day class will
  NOT reveal any actual questions that the student
  will encounter on the exam. However, the types of
  questions will be covered.

4
Unit 1 Introduction to the exams

• 2 Basic ASE Certifications CSP-ASE
• Sybase Certified Associate
• Sybase Certified Professional

5
Unit 1 Introduction to the exams
CSP Associate 12.0 Completion of one multiple
choice exam. Exam contains sixty multiple choice
questions. Sample practice can be downloaded or
exam full practice purchased This exam covers
both fundamental ASE system and database
administration questions.
6
Unit 1 Introduction to the exams

• CSP Professional ASE 12.0
• Pre-requisite CSP Associate ASE 12.0, or CSP
  Professional ASE 11.5. (There is no powering up
  exam for ASE 12.0).
• Completion of one multiple choice exam. This exam
  contains sixty multiple choice questions.
• Sample practice can be downloaded or exam full
  practice purchased
• This exam covers primarily performance and tuning
  questions for ASE, with a few questions on
  advanced administration.

7
Unit 1 Introduction to the exams
Where do I take the exams? Call 1-800-8-SYBASE
choose Education menu or At any Prometric
(800-407-3926) testing center or visit
www.2test.com
8
Unit 1 Introduction to the exams
When do I know if I passed? Immediately. After
taking the test, your score is immediately
printed out and sent to Sybase. CSP Associate ASE
12.0 75 CSP Professional ASE 12.0 75
9
Unit 1 Introduction to the exams
What if I fail? The student is allowed to fail
any of the two tests two times. If the student
fails any of the two tests for the second time,
the exam may not be taken again for the period of
three months.
10
Unit 1 Introduction to the exams
When do I need to be re-certified? Associate
Every version. Just take the new Associate
exam. Professional Every version. If you already
have a professional certification, you only need
to take the professional exam. You do not have to
take the associate exam again. As of version 12.0
there are no more migration exams.
11
Unit 1 Introduction to the exams

• What benefits do I get?
• Industry Recognition
• Use of Logo on business cards
• Official Certificate
• Differentiator for employers and promoters

12
Unit 1 Introduction to the exams

• What Does Sybase Certification Offer You?
• Increases your job advancement opportunities
• Provides a standard, tangible way of measuring
  your technical skills
• Provides you with a goal for self-improvement
  and self-development
• To design and implement real-world solutions,
  which will make you valuable to your clients and
  your team

13
Unit 1 Introduction to the exams

• How does one prepare for an exam?
• Customer Education Schedules available at
  slc.sybase.com
• Experience with products
• Practice Exams Available at this conference and
  through eShop

14
Unit 1 Introduction to the exams

• Course Maps ASE Associate
• We recommend courses
• Introduction to SQL
• Writing Effective Stored Procedures
• Fast Track to Adaptive Server Enterprise
• System Database Administration

15
Unit 1 Introduction to the exams

• Course Maps ASE Professional
• Performance Tuning Configuring ASE 12.0
• Advanced Administration Troubleshooting

16
Unit 2 What do I study? (Associate)
1. ASE Overview 2. ASE 12 New Features 3.
Configuration Installation 4. Resource
Allocation 5. Database Logs 6. Backup
Recovery 7. Parallel Processing 8. Cache
Management 9. Security
17
Unit 2 What do I study? (Associate)

• 1. ASE - Overview
• Purpose of each major system database and system
  table
• Locking scheme of APL and DOL tables
• ASEs operating environment and environment
  variables

18
Unit 2 What do I study? (Associate)

• 2. ASE 12.0 New Features
• General understanding of uses of the premium
  features
• General understanding of how the license manager
  works
• What is SySAM used for
• What are the new values for system limits that
  have changed

19
Unit 2 What do I study? (Associate)

• 3. Configuration Installation
• Primarily
• Know the different uses of the sp_configure
  command

20
Unit 2 What do I study? (Associate)

• 4. Resource Allocation
• System files, raw partitions, and the dsync
  option
• Table partitioning
• Capacity planning device and database
  management object placement and user-defined
  segments
• Including How to recreate DDL from
  storage-related system tables
• Alter table subcommands
• Last chance and freespace thresholds

21
Unit 2 What do I study? (Associate)

• 5. Database Logs
• Know how to plan the size and manage transaction
  logs
• Understand how to perform logical database
  rebuilds

22
Unit 2 What do I study? (Associate)

• 6. Backup Recovery
• Database and transaction log dumps
• How are they processed and what functionality
  supported
• Dumps and loads for standby access
• Enable up to the minute recovery
• Production database backups, protecting the
  master database, mirroring
• Truncating the log, disaster recovery

23
Unit 2 What do I study? (Associate)

• 7. Parallel Processing
• Enabling parallel processing
• Partitioning tables
• Criteria for partitioning
• Steps involved
• Partition skew and how to rebalance a table
• Keeping partition statistics up-to-date

24
Unit 2 What do I study? (Associate)

• 8. Cache Management
• Understand, at a high level, how ASE manages
  memory
• data cache(s) and procedure cache sizing
• when data pages are written to disk

25
Unit 2 What do I study? (Associate)

• 9. Security
• ASEs password security features
• Managing passwords
• Know how to grant and revoke privileges in a
  granular fashion
• Understand the use of system roles, user-defined
  roles and their precedence, difference and
  precedence between groups and user-defined roles
• Understand the use of proxy authorization

26
Unit 2 What do I study? (Associate)

• What NOT to study
• Anything not covered in the previous slides
• Generally, no nitpicky details including system
  numbers and the exact order of arguments to a
  system procedure
• Except, as indicated earlier, the new system
  limits
• No need to memorize exactly each system
  procedure. You should recognize the right one
  among the answers mostly from regular usage
• No answers refer to commands that do not exist

27
Unit 3 What do I study? (Professional)

• 1. Performance and Tuning (General)
• Tuning for a large number of user connections
• Functional limits in ASE
• General benchmarking principles
• Tuning ASEs relinquishing cpu to O/S
• Understand use of all monitoring and
  configuration system procedures

28
Unit 3 What do I study? (Professional)

• 2. Data Structures, OLTP DSS Tuning
• Units of ASE device storage APL and DOL page
  formats
• Understand all of the output of sp_help, and how
  to tune relevant parts
• Defragmentation techniques pros and cons,
  including reorg
• Criteria used in capacity planning
• Preventing hotspots, reducing page splits and
  fragmentation

29
Unit 3 What do I study? (Professional)

• 2. Data Structures, OLTP DSS Tuning (Contd)
• Tuning for OLTP, DSS and mixed workload
• Understand cluster ratios in DOL tables
• When to reconfigure a table to another locking
  scheme

30
Unit 3 What do I study? (Professional)

• 3. Parallel Performance Optimization
• Which type of parallel access plans possible
  when
• Enabling and tuning for parallelism for
  different types of selects, sorts and index
  creation
• Including how to optimizer chooses degree, and
  how to speed up index creation
• Causes of run-time adjustments, and how to
  specify action
• How are joins processed serially and in parallel

31
Unit 3 What do I study? (Professional)

• 4. Locking and Concurrency
• Understand how inserts, updates and deletes are
  processed in APL and DOL tables, including locks
  involved
• When and how to use wait, table and readpast
  locks
• How to tune with the lock promotion parameters
• When to use datapages and datarows locking
• Understand cursor locking and concurrency issues
• Locking issues in tempdb sizing and caching
  tempdb

32
Unit 3 What do I study? (Professional)

• 4. Locking and Concurrency (Contd)
• How ASE manages locks with worker process
  families
• Understand the various ways to reduce logical
  lock contention
• Understand how to optimize concurrency on a
  given table

33
Unit 3 What do I study? (Professional)

• 5. Advanced T-SQL
• The questions from this section of the exam are
  already covered by the topics listed under the
  other sections

34
Unit 3 What do I study? (Professional)

• 6. Performance Assessment
• Understand how to interpret at least the most
  common counters in sp_sysmon
• Understand how to tune the server based on
  context switching measurements
• Understand how to find out which tables and
  stored procedures are most heavily used (hint
  not in sp_sysmon)

35
Unit 3 What do I study? (Professional)

• 7. Cache Management and I/O Tuning
• When to increase or decrease procedure cache
  percentage
• Various reasons to use named caches
• When partitioning a cache may be useful, and how
  far partitioning can be taken
• Various ways to reduce spinlock contention
• Factors used by optimizer to choose I/O size
  Tuning large I/O buffer pools
• Raw partitions vs. system files and the dsync
  option

36
Unit 3 What do I study? (Professional)

• 8. ASE 12.0 New Performance and Tuning and
  advanced System Administration Features
• Understand the use of the quiesce database
  command
• Understand the purpose of abstract query plans
• Using the execute immediate capability

37
Unit 3 What do I study? (Professional)

• 9. Query Optimization
• Criteria used by the optimizer in costing access
  plans
• ASEs min and max optimization
• When will the optimizer used various covered
  access plans
• Column statistics how kept and viewed, when
  updated
• When, how and why to override the optimizers
  decisions
• When does the optimizer use various merge joins

38
Unit 3 What do I study? (Professional)

• 10. Logical Process Manager
• Understand task engine affinity
• When and how to use
• execution classes
• priority queues
• engine affinity
• precedence and scope
• on-the-fly adjustments

39
Unit 3 What do I study? (Professional)

• 11. Other Performance and Tuning Topics
• How to speed up bcp in
• Network tuning for DSS, OLTP and mixed workload
• How to configure for additional network memory
• Network engine afficinity
• When to use cursors
• When and how to make use of denormalization
  techniques
• Where are the various types of statistics stored

40
Unit 3 What do I study? (Professional)

• What NOT to study
• Anything not covered in the previous slides
• Generally, no nitpicky details including system
  numbers and the exact order of arguments to a
  system procedure\
• No answers refer to commands or features that do
  not exist

41
Unit 4 Sample questions

• The questions on all associate exams are
  intuitive.
• Either you know the answer or or dont.
• It draws out the applied real life knowledge of
  the candidate.
• Memorization helps, but will not alone guarantee
  a passing grade.
• There are very few gimmes on this exam, so if a
  question seems too easy, dont try to look too
  deep you know the answer.

42
Unit 4 Sample questions
Type of question NOT found on the exam Question
The reasons to separate the log include A -
Up-to-the minute recovery B - Monitoring how full
the log is C - Mirroring the log without having
to mirror the data D - All of the above
43
Unit 4 Sample questions
Type of question found on the exam
(Associate) Question Why would you place the
transaction log on a separate device? A - allows
you to use dump transaction B - allows for
minimally logged operations C - improves
performance D - helps ensure full recovery in the
event of disk failure E - helps ensure full
recovery in the event of a server crash
44
Unit 4 Sample questions
Type of question NOT found on the exam Question
Select all the set command that can be used in
tuning A - set tablecount B - set optimized C -
set showplan D - set statistics io E - set
recursive
45
Unit 4 Sample questions
Type of question NOT found on the
exam Question Indexes are used strictly for
performance. A, True B. False
46
Unit 4 Sample questions
Type of question found on the exam
(Professional) Question Cache spinlock
contention may occur when A. there is a single
engine B. there is a single process running on
the server C. only selects are performed, and no
modifications D. only data pages are accessed E.
the cache involved has been partitioned
47
Unit 5 How The Question Are Chosen
Stage 1 CSP advisory board
48
Unit 5 How The Question Are Chosen
Stage 1 CSP advisory board
Stage 2 New ASE version
49
Unit 5 How The Question Are Chosen
Stage 1 CSP advisory board
Stage 2 New ASE version
Stage 3 Testing objectives determined
50
Unit 5 How The Question Are Chosen
Stage 1 CSP advisory board
Stage 2 New ASE version
Stage 3 Testing objectives determined
Stage 4 Item pool created
51
Unit 5 How The Question Are Chosen
Stage 1 CSP advisory board
Stage 2 New ASE version
Stage 3 Testing objectives determined
Stage 4 Item pool created
Stage 5 Beta test period
52
Unit 5 How The Question Are Chosen
Stage 1 CSP advisory board
Stage 2 New ASE version
Stage 3 Testing objectives determined
Stage 4 Item pool created
Stage 5 Beta test period
Stage 6 Cut score determined
53
Unit 5 How The Question Are Chosen
Stage 1 CSP advisory board
Stage 2 New ASE version
Stage 3 Testing objectives determined
Stage 4 Item pool created
Stage 5 Beta test period
Stage 6 Cut score determined
Stage 7 Beta is split
54
Next Steps
Visit the Sybase website for more
info http//slc.sybase.com/plan/cintro.html
55
Closing Thought
The CSP test is one of the better certifications
to obtain for a programmer. It is the
responsibility and the duty for all who take it
to keep the exact questions confidential. The CSP
certification should be EARNED by all. Never
reveal the contents to anyone. Should the
questions leak out, and inexperienced programmers
start obtaining this certification, it diminishes
the value of the certification for all who have
obtained it legitimately, through hard work and
studying. Keep the faith and good luck!!!
56
Contact Information

• On the Partner.Net
• http//arachne8887/Partner.net2/education/instruc
  tor/
• On the Internet (External) http//slc.sybase.com/
  certification/index.html
• On the Intranet (External)http//www-sybaseu.syba
  se.com/su_programs/certification/index.htm
• Brant Stevens (Certification Program Manager)
• email bstevens_at_sybase.com (509-328-2571)
• Sam Lakkundi (For All Technical Related
  Questions)
• email lakkundi_at_sybase.com (781-564-6270)
• Maryelizabeth Camilli North America Education
  Partner Contact
• email camilli_at_sybase.com (978-287-1558)