Question 1

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

Accepted Answer

Dirty read problems can occur when a tra

Question 2

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

Accepted Answer

A) True 
 B)False

Question 3

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

Accepted Answer

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

Question 4

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

Accepted Answer

The answer of SQL Server ___________ is able to correlate...

Question 5

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

Accepted Answer

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

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

Accepted Answer

A) True 
 B)False

Question 7

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

Accepted Answer

Rebuilding the index using the ALTER IND

Question 8

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

Accepted Answer

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

Question 9

Match between columns
                        Premises: an important feature of a multiuser relational database management system that protects the integrity of data by controlling access to logical resources
occurs if a page has insufficient space to store new data in sequential order, and as a result, the page files become split
a previously saved trace file that was generated using SQL Server Profiler used for analysis by the Database Engine Tuning Advisor.
an optimization process to lower the duration of a query and overall costs by improving the design of the SQL code or by adding indexes
a situation that is caused when a process must wait for locks to be released by other active processes before it is able to proceed
a database integrity problem that can occur if two processes attempt to update the same record concurrently and the updates of one process are overwritten by the second process
a database integrity problem that occurs if a transaction attempts to read and summarize values while the data is in an inconsistent state due to a concurrent update process
any activity executed by the SQL Server database engine
a term used to describe records that are stored next to each other in sequence
the process of improving the design of code

Accepted Answer

The Answer of lost update and concurrency control and contiguous is...

Question 10

Which of the following is true about query design?

Accepted Answer

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

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

Accepted Answer

The answer of _______________ improves the integrity of the database...

Question 12

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

Accepted Answer

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

Question 13

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

Accepted Answer

A) True 
 B)False

Question 14

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

Accepted Answer

A) True 
 B)False

Question 15

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

Accepted Answer

The sp_who2 procedure lists all sessions

Question 16

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

Accepted Answer

The answer of Index __________________ is a routine task that...

Question 17

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

Accepted Answer

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

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

Accepted Answer

The first are basic measures of resource

Question 19

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

Accepted Answer

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

Question 20

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

Accepted Answer

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

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

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

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

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

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

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

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

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

Which of the following is true about query design?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Which of the following is true about query design?

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

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

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

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

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

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

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

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

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

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

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