Sybase Certification Interview Questions

 
1.
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
2.
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
3.
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.
4.
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.
5.
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
6.
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
7.
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
8.
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
9.
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
10.
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
11.
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
12.
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
13.
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
14.
Explain Which of the following can NOT have an assigned execution class
A. database
B. A stored procedure
C. An application
D. A login
15.
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
16.
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.
17.
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.
18.
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.
19.
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
20.
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
21.
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
22.
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.
23.
Which of the following can NOT have an assigned execution class
A. database
B. A stored procedure
C. An application
D. A login
24.
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
25.
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
26.
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.
27.
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.
28.
Parallel sorting can be used for: (Choose 3)
A. Sort Merge joins
B. slow bcp
C. Reformatting
D. Creating indexes
E. reorg compact
29.
Do you know What is the maximum number of key columns allowed in an index?
A.16
B. 31
C. 32
D. 63
30.
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
31.
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
32.
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