Question 1.
Given
The emplyees table has an index created as follows:
create index emp_index1
on employees(last_name,first_name desc)

Which of the following queries would not require a sort? (Choose 2)

A. Select * from employees order by last_name, first_name
B. Select * from employees order by last_name ASC, first_name DESC
C. Select * from employees order by first_name DESC, last_name ASC
D. Select * from employees order by last_name DESC, first_name ASC

Answer: B, D

Question 2.
Given
number of worker proccesses = 10
parallel degree =5
max scan parallel degree = 4.
Table A has the following charateristics:
Datarows locking, No indexes, a column named price,5000 rows, 4 partitions.
A user executes the following query. Select* from A where price <$10.

Which of the following conditions would cause the query to run in serial instead of parallel? (choose 2)

A. there are not enough worker processes available at run time
B. the database option select into/bulkcopy/pllsort is not set to true
C. the partition skew is 2.3
D. the database is set for single user mode
E. max parallel degrees does not match the number of partitions

Answer: A, C

Question 3.
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 pageslocked with incompatible 
    locks.

Answer: B, D

Question 4.
The ACME company has an 8 CPU SMP system running Adaptive Enterprise (ASE). The ASE server has been allocated 1.5 GB of 2.0 GB of memory available. The ASE server has been allocated 2 enginess. There are 1,000 concurrent users of the ASE server. The sp_sysmon report shoes the spinlock contention rate averages 70-80% for the default data cache.

Which steps can be taken to correct this performance problem?

A. Add a 16KB buffer pool to the server.
B. Increase the size of the tempdb database.
C. Add two engines to the ASE server.
D. Add named caches to and bind the appropriate objects to the named caches.
E. Add more locks to the server.

Answer: D

Question 5.
What is the purpose of dsync flag?

A. to synchronize primary and mirroed 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 Server's access to a dump device.

Answer: C

Question 6.
All of the following execution class associations exist in the ASE when user FRED, running application ctisql, logs in. 

Which one will be applied to FREDs session?

A. sp_bindexeclass ctisql,AP,NULL,EC1
B. sp_bindexeclass ctisql,AP,FRED,EC2
C. sp_bindexeclass FRED,LG,NULL,EC1
D. sp_bindexeclass FRED,LG,ctisql,EC2
E. It depends on the order of the creations of the bindings

Answer: B

Question 7.
The reorg command may be used to:(Choose 2)

A. Compact a table that users 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

Answer: A, C

Question 8.
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 peroid

Answer: A, C, D

Question 9.
The "abstract plan language" is:

A. A language used to tell the optimizer how to process a query
B. The internal command language used by sp_sysmon
C. A new type of command language for creating very complicated queries
D. The language used internally by the server for planning the most efficient use of memory

Answer: A

Question 10.
Which two actions can cause locking on system tables in tempdb?(Choose 2)

A. create table in tempbd
B. select into a #tempbd
C. worktables created for reformatting
D. selecting from a large table in tempdb

Answer: A, B

	Web www.certsbraindumps.com