Essential Sybase Certification Interview Questions & Answers:

1. Explain Which ASE commands or utilities can be used to examine partition skew?
(Choose 2)
A. sp_helpdb
B. sp_helpartition
C. sp_help
D. optdiag
E. reorg

B. sp_helpartition
C. sp_help

2. Suppose Given FREDs login is assigned EC2. When he uses a stored procedure
bound to EC3 his scheduling priority is:
A. high
B. medium
C. low
D. unable to be determined

B. medium

3. Explain When you run optdiag command on a allpages-locked table with 1000
pages, what will be value of "Forwarded row count"?
A. 1000
B. 500
C. 0
D. 1

C. 0

4. Do you know What is the maximum number of key columns allowed in an index?
A.16
B. 31
C. 32
D. 63

B. 31

5. Parallel sorting can be used for: (Choose 3)
A. Sort Merge joins
B. slow bcp
C. Reformatting
D. Creating indexes
E. reorg compact

A. Sort Merge joins
C. Reformatting
D. Creating indexes

6. Do you know The reorg command may be used to: (Choose 2)
A. Compact a table that uses datarows locking.
B. Reclaim unused space in a table that uses allpages locking.
C. Reclaim unused space in an index that uses datapages locking.
D. Rebuild a table that uses allpages locking.

A. Compact a table that uses datarows locking.
C. Reclaim unused space in an index that uses datapages locking.

7. Explain Which of the following are true of the Datarows Locking Scheme? (Choose
2)
A. Index pages are locked.
B. No transaction locks are held on index pages.
C. Server uses page locks and row locks, but not table locks.
D. Server uses table locks and row locks, but not page locks.

B. No transaction locks are held on index pages.
D. Server uses table locks and row locks, but not page locks.

8. Explain When does the optimizer consider a hash based table scan on an APL table?
A. if it is a heap table
B. if it is has a clustered index
C. If it is a table with a clustered index and a non clustered index
D. If it is a partitioned table with a clustered index

A. if it is a heap table

9. Explain Which of the following steps is NOT always necessary for performing a
tuning benchmark?
A. record a baseline measurement
B. change one variable, holding all others constant
C. make a new measurement
D. restart Adaptive Server

D. restart Adaptive Server

10. Which of the following can NOT have an assigned execution class
A. database
B. A stored procedure
C. An application
D. A login

A. database

11. Which of the following operations require "quiesce database"?
A. altering the database to a new size
B. performing non-Sybase unmirroring and backup operations.
C. truncating the transaction log
D. dumping the database using Sybase Backup Server.

B. performing non-Sybase unmirroring and backup operations.

12. Where are table/index level statistics stored?
A. sysstatistics
B. systabstats
C. On the leaf level of the index
D. The GAM page
E. The distribution page

B. systabstats

13. Suppose A query and any views in a query can reference how many user tables?
A. 12
B. 16
C. 32
D. 50
E. 192

D. 50

14. Do you know What is the purpose of dsync flag?
A. to synchronize primary and mirrored devices
B. to synchronize primary and secondary databases
C. to control whether writes to operating system files are buffered or not
D. to control Backup Servers access to a dump device

C. to control whether writes to operating system files are buffered or not

15. Explain Which of the following statements describes readpast locking? (Choose 2)
A. It can be specified at the session, transaction, and table level.
B. It allows insert, update, and delete commands to read past any incompatible lock.
C. It allows readers to not block writers.
D. It allows select and readtext queries to silently skip all rows or pages locked with incompatible locks.

B. It allows insert, update, and delete commands to read past any incompatible lock.
D. It allows select and readtext queries to silently skip all rows or pages locked with incompatible locks.

16. Can you explain Which two are advantages of use max_rows_per_page to reduce the row
density? (Choose 2.)
A. It may decrease the disk space used.
B. It may decrease the contention.
C. It may decrease the number of page splits.
D. It may decrease the number of page I/Os.
E. It may help to decrease row movement of frequently updated varchar columns.

B. It may decrease the contention.
E. It may help to decrease row movement of frequently updated varchar columns.

17. Can you explain What is the maximum row size for DOL tables?
A. 1024 bytes of data.
B. 1958 bytes of data.
C. 1962 bytes of data.
D. 2000 bytes of data.

B. 1958 bytes of data.

18. Do you know What type of access method is used for a covered query?
A. parallel index scan
B. serial index scan
C. hash based index scan
D. hash based table scan

B. serial index scan

19. Explain Which of the following can NOT have an assigned execution class
A. database
B. A stored procedure
C. An application
D. A login

A. database

20. Suppose All of the following tactics may reduce contention for cache spinlocks in an
ASE configured to support multiple online engines, except
A. Increasing the spinlock ratio
B. Creating additional named caches and binding frequently used tables to them
C. Creating partitions in heavily used caches
D. Returning to a single engine configuration

A. Increasing the spinlock ratio

21. Suppose The titles table has 50,000 rows and a nonclustered index on the price
column. Which of the following queries use less logical I/Os?
A. select "Min" = min(price), "Max" = max(price) from titles
B. select "Min" = (select min(price) from titles), "Max" = (select max(price) from titles)
C. They will both use the same number of I/Os

B. select "Min" = (select min(price) from titles), "Max" = (select max(price) from titles)

22. Explain What are some ways to reduce last data page lock contention for inserts?
(Choose 3)
A. alter the table to use the Datarows locking scheme
B. alter the table to use the Datapages locking scheme
C. partition the table
D. create a clustered index on a random key
E. increase the server configuration for the number of locks
F. increase the server configuration for the deadlock checking period

A. alter the table to use the Datarows locking scheme
C. partition the table
D. create a clustered index on a random key

23. If sp_sysmon output consistently shows a high number of Voluntary
Yields, which configuration parameter could be modified to improve performance?
A. user log cache size
B. time slice
C. identity grab size
D. stack size
E. cpu accounting flush interval

B. time slice

24. Do you know Transaction Isolation Level 2 is directly supported in the following lock
scheme(s):
A. Allpages
B. Allpages, Datapages
C. Allpages, Datapages, Datarows
D. Datapages, Datarows

D. Datapages, Datarows

25. How to reclaim space from a fragmented allpages heap table?
A. run reorg command
B. dump and load the database
C. create and drop a clustered index
D. truncate the least used pages

C. create and drop a clustered index

26. Explain Which of the following create table can prevent or minimize row
forwarding in DOL tables?
A. fillfactor
B. max_rows_per_page
C. exp_row_size
D. reservepagegap

C. exp_row_size

27. Explain Which of the following cache or IO tuning methods can reduce the spinlock
contention on a single "hot" table that is already bound to a named cache?
A. Create 16K buffer pool in the named cache
B. Change the cache replacement strategy of the named cache to relaxed LRU
C. Partition the named cache
D. Increase the named cache size

C. Partition the named cache

28. Explain What is the maximum number of tables allowed in a join query?
A. 50 User tables and 14 work tables
B. 32 User tables and 14 work tables
C. 30 User tables and 12 work tables
D. 16 User tables and 12 work tables

A. 50 User tables and 14 work tables

29. Explain Which of the following operations require "quiesce database"?
A. altering the database to a new size
B. performing non-Sybase unmirroring and backup operations.
C. truncating the transaction log
D. dumping the database using Sybase Backup Server.

B. performing non-Sybase unmirroring and backup operations.

30. Explain The reorg command may be used to: (Choose 2)
A. Compact a table that uses datarows locking.
B. Reclaim unused space in a table that uses allpages locking.
C. Reclaim unused space in an index that uses datapages locking.
D. Rebuild a table that uses allpages locking.

A. Compact a table that uses datarows locking.
C. Reclaim unused space in an index that uses datapages locking.

31. Explain What is the purpose of dsync flag?
A. to synchronize primary and mirrored devices
B. to synchronize primary and secondary databases
C. to control whether writes to operating system files are buffered or not
D. to control Backup Servers access to a dump device

C. to control whether writes to operating system files are buffered or not

32. Explain Where are table/index level statistics stored?
A. sysstatistics
B. systabstats
C. On the leaf level of the index
D. The GAM page
E. The distribution page

B. systabstats