Database Management – Week 6

Average Rating

0 out of 5 stars. 0 votes.

Question 1

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

The administrator must have detailed knowledge of the database schema to use it.
It quantifies the expected performance gains for each recommendation.
Performance gains of less than 25% should be discounted.
Selected: It uses a different set of data than the query optimizer.This answer is incorrect.

Actually, look again. The Database Engine Tuning Advisor quantifies the expected performance gain for each recommendation, and it includes the functionality to automatically add the changes to the database or to generate DDL SQL scripts for manual implementation. Review Module 6 again, and specifically, Page II.

0/1

Question 2

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

It is discarded.
An error is returned to try again.
It is put in a queue.
Selected: The resource is stopped.This answer is incorrect.

Actually, look again. Requests to use a particular system resource—such as the physical disk, memory, or processor—are serviced sequentially by the Windows operating system. If a resource is busy, then the request is added to a queue and serviced once the backlog of requests ahead of it is cleared. Review Module 6 again, and specifically, Page II.

0/1

Question 3

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

Indexes should be created on columns used by the WHERE and BY clauses.
An index is created for every foreign key by default.
A composite index is an index on a single column.
Selected: Indexes usually speed up write operations.This answer is incorrect.

Actually, look again. As a general rule, indexes should be created on columns in the WHERE, ORDER BY, GROUP BY, and DISTINCT clauses. Review Module 6 again, and specifically, Page I.

1/1

Question 4

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

Normalization diminishes database integrity.
Selected: All relationships should be explicitly defined in the database.This answer is correct.
Normalized views of the data improve query performance.
It’s best to use data types that require conversion.

Correct! All relationships (e.g., foreign keys) and constraints (e.g., unique values) should be explicitly defined in the database.

0/1

Question 5

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

Selected: dirty readsThis answer is incorrect.
concurrency control
lost updates
incorrect summary

Actually, look again. Allowing uncontrolled access to the database may result in a number of problems, including dirty reads, lost updates, and incorrect summary.
Review Module 6 again, and specifically, Page II.

1/1

Question 6

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

lost update
incorrect summary
Selected: dirty readThis answer is correct.
resource lock

Correct! Dirty read problems can occur when a transaction that is updating the database fails and the updated values are rolled back to their original state.

0/1

Question 7

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

performance counter
Task Manager object
Selected: Database Engine Tuning AdvisorThis answer is incorrect.
data collector set

Actually, look again. Data collector sets are collections of performance counters that are configured and scheduled to record time series data on a particular group of performance counters. Review Module 6 again, and specifically, Page II.

1/1

Question 8

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?

schema design
query design
Selected: permissionsThis answer is correct.
indexes

Correct! Schema design, query design, and index design are major factors that influence the speed and efficiency with which information is retrieved or modified in a database management system.

0/1

Question 9

Which of the following best defines a trace?

a graphical interface that enables a database administrator to capture events
any activity executed by the SQL Server database engine
the result set of database events captured by SQL Server Profiler
Selected: used to monitor activity of the physical resources that are managed by the operating systemThis answer is incorrect.

Actually, look again. The result set of database events captured by SQL Server Profiler is known as a trace.
Review Module 6 again, and specifically, Page II.

1/1

Question 10

Which situation occurs when two transactions each have a lock on a resource that both transactions need in order to complete?

blocking lock
exclusive lock
multiple lockout
Selected: deadlockThis answer is correct.

Correct! A deadlock is a situation in which two transactions each have a lock on a resource that both transactions need in order to complete.

Bottom of Form

Back to assessments

Top of Form

Question 1

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

Normalization diminishes database integrity.
Selected: All relationships should be explicitly defined in the database.This answer is correct.
Normalized views of the data improve query performance.
It’s best to use data types that require conversion.

Correct! All relationships (e.g., foreign keys) and constraints (e.g., unique values) should be explicitly defined in the database.

1/1

Question 2

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

The administrator must have detailed knowledge of the database schema to use it.
Selected: It quantifies the expected performance gains for each recommendation.This answer is correct.
Performance gains of less than 25% should be discounted.
It uses a different set of data than the query optimizer.

Correct! The Database Engine Tuning Advisor quantifies the expected performance gain for each recommendation, and it includes the functionality to automatically add the changes to the database or to generate DDL SQL scripts for manual implementation.

1/1

Question 3

Which of the following is true about query design?

The LIKE operator enhances performance.
Sorting should be done by the SQL Server, not the client application.
It’s best to use relative names when referring to database objects.
Selected: Subqueries can degrade performance.This answer is correct.

Correct! Subqueries can significantly degrade performance.

1/1

Question 4

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

lost update
incorrect summary
Selected: dirty readThis answer is correct.
resource lock

Correct! Dirty read problems can occur when a transaction that is updating the database fails and the updated values are rolled back to their original state.

0/1

Question 5

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

query optimizer
Selected: query execution plannerThis answer is incorrect.
query editor
query processor

Actually, look again. The SQL Server database engine has a query optimizer that builds a physical execution plan from a SQL query. Review Module 6 again, and specifically, Page II.

1/1

Question 6

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

It is discarded.
An error is returned to try again.
Selected: It is put in a queue.This answer is correct.
The resource is stopped.

Correct! Requests to use a particular system resource—such as the physical disk, memory, or processor—are serviced sequentially by the Windows operating system. If a resource is busy, then the request is added to a queue and serviced once the backlog of requests ahead of it is cleared.

1/1

Question 7

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?

schema design
query design
Selected: permissionsThis answer is correct.
indexes

Correct! Schema design, query design, and index design are major factors that influence the speed and efficiency with which information is retrieved or modified in a database management system.

1/1

Question 8

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

Selected: Logical Disk: Avg. Disk Queue LengthThis answer is correct.
System: Processor Queue Length
Logical Disk: Avg. Disk sec/Read
Memory: Pages/sec

Correct! Logical Disk: Avg. Disk Queue Length provides a count of the number of requests waiting to read or write data from the physical disk.

1/1

Question 9

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

Selected: Indexes should be created on columns used by the WHERE and BY clauses.This answer is correct.
An index is created for every foreign key by default.
A composite index is an index on a single column.
Indexes usually speed up write operations.

Correct! As a general rule, indexes should be created on columns in the WHERE, ORDER BY, GROUP BY, and DISTINCT clauses.

1/1

Question 10

Which command takes corrective action on a fragmented index?

Selected: ALTER INDEX REORGANIZEThis answer is correct.
REINDEX DATABASE
UPDATE INDEX REBUILD
UPDATE STATISTICS

Correct! Reorganizing an index using the ALTER INDEX REORGANIZE is highly recommended for any indexes for which the logical fragmentation is above 5%.

Bottom of Form

Back to assessments

Top of Form

Question 1

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

Indexes do not have to be updated once they are created.
Database records stored in order are said to be fragmented.
Selected: Records should be stored on the disk contiguously.This answer is correct.
External fragmentation occurs due to accumulation of free space between records.

Correct! For an optimal index, records are stored on the disk contiguously, meaning next to each other and in sequence.

1/1

Question 2

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

The administrator must have detailed knowledge of the database schema to use it.
Selected: It quantifies the expected performance gains for each recommendation.This answer is correct.
Performance gains of less than 25% should be discounted.
It uses a different set of data than the query optimizer.

0/1

Question 3

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

Selected: foreign key tableThis answer is incorrect.
covering index
primary key table
composite index

Actually, look again. A covering index is a database object that physically stores all columns needed to query a single table. Unlike a normal index, a covering index is able to return the required data directly to the query, thus eliminating the additional step of retrieving data from the underlying tables. Review Module 6 again, and specifically, Page I.

1/1

Question 4

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

Task Manager
Selected: Performance MonitorThis answer is correct.
Reliability Monitor
Server Manager

Correct! Windows Performance Monitor can be used to monitor activity of the physical resources that are managed by the operating system.

1/1

Question 5

Which of the following best defines a trace?

a graphical interface that enables a database administrator to capture events
any activity executed by the SQL Server database engine
Selected: the result set of database events captured by SQL Server ProfilerThis answer is correct.
used to monitor activity of the physical resources that are managed by the operating system

Correct! The result set of database events captured by SQL Server Profiler is known as a trace.

1/1

Question 6

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

Selected: Logical Disk: Avg. Disk Queue LengthThis answer is correct.
System: Processor Queue Length
Logical Disk: Avg. Disk sec/Read
Memory: Pages/sec

Correct! Logical Disk: Avg. Disk Queue Length provides a count of the number of requests waiting to read or write data from the physical disk.

1/1

Question 7

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?

schema design
query design
Selected: permissionsThis answer is correct.
indexes

Correct! Schema design, query design, and index design are major factors that influence the speed and efficiency with which information is retrieved or modified in a database management system.

1/1

Question 8

Which situation occurs when two transactions each have a lock on a resource that both transactions need in order to complete?

blocking lock
exclusive lock
multiple lockout
Selected: deadlockThis answer is correct.

Correct! A deadlock is a situation in which two transactions each have a lock on a resource that both transactions need in order to complete.

1/1

Question 9

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

Selected: query optimizerThis answer is correct.
query execution planner
query editor
query processor

Correct! The SQL Server database engine has a query optimizer that builds a physical execution plan from a SQL query.

1/1

Question 10

Which command takes corrective action on a fragmented index?

Selected: ALTER INDEX REORGANIZEThis answer is correct.
REINDEX DATABASE
UPDATE INDEX REBUILD
UPDATE STATISTICS

Correct! Reorganizing an index using the ALTER INDEX REORGANIZE is highly recommended for any indexes for which the logical fragmentation is above 5%.

Bottom of Form

Back to assessments

Top of Form

Question 1

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

Logical Disk: Avg. Disk sec/Read
Selected: Memory: Pages/secThis answer is correct.
Logical Disk: Avg. Disk Queue Length
System: Processor Queue Length

Correct! Memory: Pages/sec measures the activity of transferring data between the physical disk and memory.

1/1

Question 2

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

use queries that perform multiple locks
Selected: use clustered indexes for frequently accessed tablesThis answer is correct.
use random sequences to perform multiple-table updates
use a single query to modify a large number of rows, if possible

Correct! Clustered indexes can help you avoid blocking locks and deadlocks.

1/1

Question 3

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

Selected: Logical Disk: Avg. Disk Queue LengthThis answer is correct.
System: Processor Queue Length
Logical Disk: Avg. Disk sec/Read
Memory: Pages/sec

Correct! Logical Disk: Avg. Disk Queue Length provides a count of the number of requests waiting to read or write data from the physical disk.

1/1

Question 4

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

foreign key table
Selected: covering indexThis answer is correct.
primary key table
composite index

Correct! A covering index is a database object that physically stores all columns needed to query a single table. Unlike a normal index, a covering index is able to return the required data directly to the query, thus eliminating the additional step of retrieving data from the underlying tables.

1/1

Question 5

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

Normalization diminishes database integrity.
Selected: All relationships should be explicitly defined in the database.This answer is correct.
Normalized views of the data improve query performance.
It’s best to use data types that require conversion.

Correct! All relationships (e.g., foreign keys) and constraints (e.g., unique values) should be explicitly defined in the database.

1/1

Question 6

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

It is discarded.
An error is returned to try again.
Selected: It is put in a queue.This answer is correct.
The resource is stopped.

1/1

Question 7

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

Indexes do not have to be updated once they are created.
Database records stored in order are said to be fragmented.
Selected: Records should be stored on the disk contiguously.This answer is correct.
External fragmentation occurs due to accumulation of free space between records.

Correct! For an optimal index, records are stored on the disk contiguously, meaning next to each other and in sequence.

1/1

Question 8

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

lost update
incorrect summary
Selected: dirty readThis answer is correct.
resource lock

Correct! Dirty read problems can occur when a transaction that is updating the database fails and the updated values are rolled back to their original state.

1/1

Question 9

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

Selected: query optimizerThis answer is correct.
query execution planner
query editor
query processor

Correct! The SQL Server database engine has a query optimizer that builds a physical execution plan from a SQL query.

1/1

Question 10

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

The administrator must have detailed knowledge of the database schema to use it.
Selected: It quantifies the expected performance gains for each recommendation.This answer is correct.
Performance gains of less than 25% should be discounted.
It uses a different set of data than the query optimizer.

Bottom of Form

Back to assessments

1/1

Question 1

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

a. lost update
b. incorrect summary
Selected: c. dirty readThis answer is correct.
d. resource lock

Correct! Dirty read problems can occur when a transaction that is updating the database fails and the updated values are rolled back to their original state.

1/1

Question 2

Which situation occurs when two transactions each have a lock on a resource that both transactions need in order to complete?

a. blocking lock
b. exclusive lock
c. multiple lockout
Selected: d. deadlockThis answer is correct.

Correct! A deadlock is a situation in which two transactions each have a lock on a resource that both transactions need in order to complete.

1/1

Question 3

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

Selected: a. Logical Disk: Avg. Disk Queue LengthThis answer is correct.
b. System: Processor Queue Length
c. Logical Disk: Avg. Disk sec/Read
d. Memory: Pages/sec

Correct! Logical Disk: Avg. Disk Queue Length provides a count of the number of requests waiting to read or write data from the physical disk.

1/1

Question 4

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

a. use queries that perform multiple locks
Selected: b. use clustered indexes for frequently accessed tablesThis answer is correct.
c. use random sequences to perform multiple-table updates
d. use a single query to modify a large number of rows, if possible

Correct! Clustered indexes can help you avoid blocking locks and deadlocks.

1/1

Question 5

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

Selected: a. query optimizerThis answer is correct.
b. query execution planner
c. query editor
d. query processor

Correct! The SQL Server database engine has a query optimizer that builds a physical execution plan from a SQL query.

1/1

Question 6

Which of the following best defines a trace?

a. a graphical interface that enables a database administrator to capture events
b. any activity executed by the SQL Server database engine
Selected: c. the result set of database events captured by SQL Server ProfilerThis answer is correct.
d. used to monitor activity of the physical resources that are managed by the operating system

Correct! The result set of database events captured by SQL Server Profiler is known as a trace.

1/1

Question 7

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. query design
Selected: c. permissionsThis answer is correct.
d. indexes

Correct! Schema design, query design, and index design are major factors that influence the speed and efficiency with which information is retrieved or modified in a database management system.

1/1

Question 8

Which of the following is true about query design?

a. The LIKE operator enhances performance.
b. Sorting should be done by the SQL Server, not the client application.
c. It’s best to use relative names when referring to database objects.
Selected: d. Subqueries can degrade performance.This answer is correct.

Correct! Subqueries can significantly degrade performance.

1/1

Question 9

Which command takes corrective action on a fragmented index?

Selected: a. ALTER INDEX REORGANIZEThis answer is correct.
b. REINDEX DATABASE
c. UPDATE INDEX REBUILD
d. UPDATE STATISTICS

Correct! Reorganizing an index using the ALTER INDEX REORGANIZE is highly recommended for any indexes for which the logical fragmentation is above 5%.

1/1

Question 10

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

a. Normalization diminishes database integrity.
Selected: b. All relationships should be explicitly defined in the database.This answer is correct.
c. Normalized views of the data improve query performance.
d. It’s best to use data types that require conversion.

Correct! All relationships (e.g., foreign keys) and constraints (e.g., unique values) should be explicitly defined in the database.

Leave a Reply Cancel reply