SQL Performance and Tuning Considerations, concluded - Quiz

(Page 5 of 5 )

Choose the correct responses to each of the multiple-choice questions. Note that there may be more than one correct response to each question.

1. Performance requirements
   
   a. Should be set after the SQL statements are tuned
   
   b. Provide a way to identify statements that need tuning
   
   c. Are best when they contain complex criteria
   
   d. Provide a way to know when to stop tweaking a query
   
   e. Are developed just to make the auditors happy
   
2. Disk reads and writes can be minimized by
   
   a. Allocating buffers of sufficient size
   
   b. Placing all the database files on one disk drive
   
   c. Putting the entire database in memory
   
   d. Adding indexes for all the important table columns
   
   e. Spreading files across all available disk drives
   
3. The computer system can be tuned by
   
   a. Collecting database statistics
   
   b. Applying available security patches
   
   c. Selecting fast and reliable hardware
   
   d. Following the DBMS tuning recommendations
   
   e. Consulting a tuning guide for the operating system
   
4. Efficient table design includes
   
   a. Using VARCHAR for all variable-length character columns
   
   b. Using the smallest possible numeric data type that holds the data values
   
   c. Using triggers whenever possible
   
   d. Using identical data types for primary keys and their matching foreign keys
   
   e. Using identical data types for all primary key columns
   
5. A query execution plan
   
   a. Describes how the DBMS will execute a query
   
   b. Is stored in the SQL cache
   
   c. Is created using the explain plan feature of the RDBMS
   
   d. Requires a plan table to hold the explain results
   
   e. Requires the use of a stored procedure
   
6. The query optimizer
   
   a. Creates a query execution plan in the plan table
   
   b. Determines the best way to execute an SQL statement
   
   c. May use statistics gathered from the database
   
   d. May use rules applied to the way the statement was written
   
   e. Can be cost-based or rule-based
   
7. In order to design proper queries, the developer should
   
   a. Know the characteristics of the data in the database
   
   b. Match data types in predicates
   
   c. Use hints as much as possible
   
   d. Avoid unnecessary tables and columns
   
   e. Maximize the number of rows in each result set
   
8. Table scans can be avoided by
   
   a. Including a WHERE clause that references an indexed column
   
   b. Using GROUP BY instead of DISTINCT
   
   c. Making sure that statistics are up to date
   
   d. Making at least one predicate references the leading column of an index
   
   e. Avoiding unnecessary columns
   
9. An index cannot be used when
   
   a. The WHERE clause references the second column of an index
   
   b. A LIKE clause references a comparison string that contains a wildcard (except in the first position of the string)
   
   c. The NOT operator is used in a predicate
   
   d. An SQL function is included in a column comparison (except when they match a function-based index)
   
   e. The NOT EQUAL operator is used in a predicate
   
10. Considerations for using indexes include
    
    a. Placing indexes on all frequently updated columns
    
    b. Placing indexes on foreign key columns
    
    c. Avoiding overlapping indexes
    
    d. Creating indexes on columns that have only a few possible values
    
    e. Avoiding unique indexes
    
11. Tuning considerations for MySQL include
    
    a. Function-based indexes
    
    b. Hash indexes
    
    c. Clustering indexes
    
    d. Storage engine options
    
    e. Bit-map indexes
    
12. Tuning considerations for Oracle include
    
    a. Function-based indexes
    
    b. Hash indexes
    
    c. Clustering indexes
    
    d. Storage engine options
    
    e. Bit-map indexes
    
13. Tuning considerations for Microsoft SQL Server include
    
    a. Function-based indexes
    
    b. Hash indexes
    
    c. Clustering indexes
    
    d. Storage engine options
    
    e. Bit-map indexes
    
14. An explain plan in Oracle
    
    a. Requires the use of a plan table
    
    b. Contains a PLAN_ID to uniquely identify it
    
    c. Can be viewed using Enterprise Manager
    
    d. Can be viewed with SQL by selecting it from the plan table
    
    e. Is created using the CREATE PLAN statement
    
15. An execution plan in Microsoft SQL Server
    
    a. Requires the use of a plan table
    
    b. Can be displayed using an option in SQL Query Analyzer
    
    c. Displays the execution plan in a text format
    
    d. Displays the execution plan in a graphical format
    
    e. Can be viewed with SQL by selecting it from the plan table
    
16. When tuning INSERT statements, one should consider
    
    a. Index maintenance
    
    b. Row expansion
    
    c. The CASCADE option
    
    d. Adequate free space
    
    e. Query rewrites 
    
17. When tuning UPDATE statements, one should consider
    
    a. Index maintenance
    
    b. Row expansion
    
    c. The CASCADE option
    
    d. Adequate free space
    
    e. Query rewrites 
    
18. When tuning DELETE statements, one should consider
    
    a. Index maintenance
    
    b. Row expansion
    
    c. The CASCADE option
    
    d. Adequate free space
    
    e. Query rewrites
    
19. Data types should match
    
    a. For all primary key columns
    
    b. Between primary key and corresponding secondary key columns
    
    c. Between primary key and corresponding foreign key columns
    
    d. Between column values and literal values compared in predicates
    
    e. For all function-based indexes
    
20. The most likely cause of query performance problems is
    
    a. A poorly tuned operating system
    
    b. A poorly written SQL statement
    
    c. Trigger overhead
    
    d. Index maintenance overhead
    
    e. Row expansion

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

Comments On: SQL Performance and Tuning Considerations, concluded

· This article is an excerpt from the book "SQL DeMYSTified," published by McGraw/Hill...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter 11 of the book SQL DeMYSTiFied, written by Andy Oppel (McGraw-Hill/Osborne, 2005; ISBN: 0072262249). Check it out today at your favorite bookstore. Buy this book now.