Question 1

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

Accepted Answer

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 will be added to a queue and serviced once the backlog of requests ahead of it is cleared.

Question 2

Which of the following best defines a trace?

Accepted Answer

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 
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 B

Question 3

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

Accepted Answer

Design of the database schema and queries are major factors that influence the speed and efficiency with which information is retrieved or modified in a database management system. Many databases grow in a haphazard manner as tables are added incrementally without much attention paid to the overall design. This can lead to unanticipated performance problems. In addition, making changes once a database is in production is likely to impact many dependent processes, resulting in significant expense. Poorly designed queries do not make efficient use of the resources available.

Question 4

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

Accepted Answer

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

Question 5

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

Accepted Answer

Avoid operators, such as LIKE, that add

Question 6

Which command takes corrective action on a fragmented index?

Accepted Answer

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

Question 7

How does external fragmentation occur?

Accepted Answer

External fragmentation occurs when the l

Question 8

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

Accepted Answer

A deadlock is a situation in which two t

Question 9

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

Accepted Answer

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 
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 10

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

Accepted Answer

The answer of A(n)___________ is always created for the primary...

Question 11

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

Accepted Answer

A) True 
 B)False

Question 12

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?

Accepted Answer

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

Question 13

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

Accepted Answer

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

Question 14

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

Accepted Answer

The answer of ____________ indexes and indexed views should be...

Question 15

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

Accepted Answer

In general, the data type that requires

Question 16

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

Accepted Answer

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

Question 17

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

Accepted Answer

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 
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 18

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

Accepted Answer

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

Question 19

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?

Accepted Answer

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

Question 20

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

Accepted Answer

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

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

Which of the following best defines a trace?

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

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

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

Which command takes corrective action on a fragmented index?

How does external fragmentation occur?

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

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

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

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

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?

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

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

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

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

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

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

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?

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

Introduction to SQL Server 2012

Deploying SQL Server 2012

Architecture and Configuration

Creating SQL Server 2012 Databases

Transact:SQL Primer

SQL Server Security

Backup and Recovery

Data Integration

Monitoring SQL Server 2012

Filters

Exam 7: Performance Monitoring and Optimization

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

Which of the following best defines a trace?

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

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

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

Which command takes corrective action on a fragmented index?

How does external fragmentation occur?

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

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

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

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

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?

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

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

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

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

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

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

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?

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

Introduction to SQL Server 2012

Deploying SQL Server 2012

Architecture and Configuration

Creating SQL Server 2012 Databases

Transact:SQL Primer

SQL Server Security

Backup and Recovery

Data Integration

Monitoring SQL Server 2012

Filters