Deck 7: Performance Monitoring and Optimization

Question 1

What happens to a request for a resource if the resource is busy?

A)it is discarded
B)it is put in a queue
C)an error is returned to try again
D)the resource is stopped

Answer 1

B

Question 2

Which of the following is a guideline to follow that can help you avoid blocking locks and deadlocks?

A)use queries that perform multiple locks
B)use random sequences to perform multiple-table updates
C)use clustered indexes for frequently accessed tables
D)use a single query to modify a large number of rows, if possible

Answer 2

C

Question 3

Indexes prevent the database engine from having to scan every record in the database.

Answer 3

True

Question 4

Which of the following best defines a trace?

A)a graphical interface that enables a database administrator to capture events
B)the result set of database events captured by SQL Server Profiler
C)any activity executed by the SQL Server database engine
D)used to monitor activity of the physical resources that are managed by the operating system

Question 5

Only one user or client application at a time may attempt to query a database.

Question 6

The % Processor Time performance counter displays a count of the number of processes that are waiting for the processor.

Question 7

Which situation occurs when two transactions each have a lock on a resource that the other transaction needs in order to complete?

A)blocking lock
B)multiple lockout
C)exclusive lock
D)deadlock

Question 8

If a request is made to use a resource that is busy, the request is dropped and must be re-issued.

Question 9

Which problem occurs when a concurrent process reads data that was not committed to the database?

A)lost update
B)dirty read
C)incorrect summary
D)resource lock

Question 10

Which of the following is true about query design?

A)the LIKE operator enhances performance
B)it's best to use relative names when referring to database objects
C)sorting should be done by the SQL server, not the client application
D)subqueries can degrade performance

Question 11

Which of the following is a database object that physically stores all columns that are needed to query a single table?

A)foreign key table
B)primary key table
C)covering index
D)composite index

Question 12

Which counter indicates the number of requests that are waiting to perform a disk read or write operation?

A)Logical Disk: Avg. Disk Queue Length
B)Logical Disk: Avg. Disk sec/Read
C)System: Processor Queue Length
D)Memory: Pages/sec

Question 13

Which of the following is NOT a factor that influences the speed and efficiency with which information is retrieved or modified in a database management system?

A)schema design
B)permissions
C)query design
D)indexes

Question 14

Which of the following is a valid consideration when implementing physical database design?

A)normalization diminishes database integrity
B)normalized views of the data improve query performance
C)all relationships should be explicitly defined in the database
D)it's best to use data types that require conversion

Question 15

The logical model is subjected to normalization, which can eliminate data redundancy.

Question 16

Which of the following is NOT a problem that may result from allowing uncontrolled access to the database?

A)dirty reads
B)lost updates
C)concurrency control
D)incorrect summary

Question 17

Which counter measures the data being transferred between the physical disk and memory?

A)Logical Disk: Avg. Disk sec/Read
B)Logical Disk: Avg. Disk Queue Length
C)Memory: Pages/sec
D)System: Processor Queue Length

Question 18

Which of the following is true about indexes and index design?

A)indexes should be created on columns used by WHERE and BY clauses
B)a composite index is an index on a single column
C)an index is created for every foreign key by default
D)indexes usually speed up write operations

Question 19

Which of the following would you use if you wanted to collect performance data on several resources over a period of time for analysis later?

A)performance counter
B)Database Engine Tuning Advisor
C)Task Manager object
D)data collector set

Question 20

Which Windows tool can provide the most detailed analysis of physical resource performance?

A)Task Manager
B)Reliability Monitor
C)Performance Monitor
D)Server Manager

Question 21

_______________ improves the integrity of the database and reduces storage needs, but also splits data across multiple tables.

Question 22

Which command takes corrective action on a fragmented index?

A)ALTER INDEX REORGANIZE
B)UPDATE INDEX REBUILD
C)REINDEX DATABASE
D)UPDATE STATISTICS

Question 23

How does external fragmentation occur?

Question 24

Which of the following is true about indexes and index fragmentation?

A)indexes do not have to be updated once they are created
B)records should be stored on the disk contiguously
C)database records stored in order are said to be fragmented
D)external fragmentation occurs due to accumulation of free space between records

Question 25

A(n)___________ is always created for the primary key on a table.

Question 26

____________ indexes and indexed views should be used sparingly because they must be updated whenever any of the data in the underlying tables changes.

Question 27

Why is the selection of data types an important design consideration?

Question 28

Which of the following is true about the Database Engine Tuning Advisor?

A)the administrator must have detailed knowledge of the database schema to use it
B)performance gains of less than 25% should be discounted
C)it quantifies the expected performance gains for each recommendation
D)it uses a different set of data than the query optimizer

Question 29

What are two major design factors that influence the speed an efficiency in a database management system? Explain.

Question 30

What should you do if logical fragmentation of indexes is above 30 percent?

Question 31

Index __________________ is a routine task that must be performed to ensure the optimal health of the database and query performance.

Question 32

What are the two types of system performance metrics that are useful for troubleshooting SQL Server performance issues?

Question 33

Describe two SQL Server procedures that can be used to identify locking.

Question 34

How are requests to use a particular system resource serviced by the Windows OS?

Question 35

What is a deadlock and how does SQL Server resolve one?

Question 36

Which component of the SQL Server database engine builds a physical execution plan from a SQL query?

A)query optimizer
B)query editor
C)query execution planner
D)query processor

Question 37

List and describe three types of problems that can result from uncontrolled access to the database.

Question 38

Which of the following is a suggested threshold for performing index fragmentation maintenance?

A)delete and recreate if the fragmentation is more than 10 percent
B)update statistics for fragmentation more than 30 percent
C)rebuild if the fragmentation is less than 5 percent
D)reorganize if the logical fragmentation is between 5 and 30 percent

Question 39

SQL Server ___________ is able to correlate time series events saved in a trace file with a data collection set from Performance Monitor.

Question 40

List three query design guidelines that can help reduce demands on the physical resources while providing an improvement in performance.