Database Management – Week 8

Database Management, Information Technology
Average Rating
0 out of 5 stars. 0 votes.

Question 1

Which tool do you use to record a SQL Server trace?

  • Task Manager
  • Selected: SQL Server ProfilerThis answer is correct.
  • Performance Monitor
  • Management Data Warehouse

Correct! You can capture SQL Server performance data by using the SQL Server Profiler to record a trace, or by capturing performance counter data using the performance monitor utility.

1/1

Question 2

Ideally, database administrators should monitor SQL Server reactively and solve problems as they occur.

  • True
  • Selected:FalseThis answer is correct.

Correct! It is necessary for a database administrator to proactively monitor each SQL Server instance.

1/1

Question 3

Which of the following is NOT true with respect to dynamic management views and functions?

  • They belong to the sys schema.
  • They are given the prefix dm_ in their name.
  • Server-scoped dynamic management views and functions are stored in the master system database.
  • Selected: Database-scoped dynamic management views and functions are stored in the msdb system database.This answer is correct.

Correct! Database-scoped dynamic management views and functions are stored in the msdb system database.

1/1

Question 4

Why is establishing a baseline important for monitoring performance?

  • Baselines are not important because there is no substitute for experience.
  • Selected: A baseline can help a database administrator understand how the system is expected to behave under normal operating conditions.This answer is correct.
  • Baselines are not relevant to performance monitoring.
  • both options a and b

Correct! A baseline can help a database administrator understand how the system is expected to behave under normal operating conditions.

0/1

Question 5

How must the recipient of a notification be configured in SQL Server Agent?

  • Selected: as a mail recipientThis answer is incorrect.
  • as an operator
  • as an alert administrator
  • as an Agent Manager

Actually, look again. The recipient of a notification should be configured in SQL Server Agent as an operator. Review Module 8 again, and specifically, page 369 in Chapter 10 of the textbook.

1/1

Question 6

Which of the following is true about the Management Data Warehouse?

  • There are no predefined data collection sets.
  • Management reports must be custom made.
  • It is installed automatically with SQL Server.
  • Selected: Data may be queried using SQL.This answer is correct.

Correct! The data that is stored in the Management Data Warehouse may be queried using SQL, and it can also be viewed using one of several predefined management reports.

1/1

Question 7

Which of the following is not a required step in configuring a database audit?

  • Create a new audit object.
  • Map the users and logins to the database roles.
  • Create a database audit specification.
  • Enable the audit.
  • Selected: Enable HTML.This answer is correct.

Correct! There are four steps to configuring a database audit: define an audit object; create the server audit specification; create the database audit specification; and enable the audit to begin capturing events.

1/1

Question 8

To get an accurate view of SQL Server performance using SQL Server Activity Monitor without affecting overall system performance, how often should the server be polled?

  • 10 times/second
  • Selected: 1 time/10 secondsThis answer is correct.
  • 1 time/second
  • 10 times/minute

Correct! To get an accurate view of SQL Server performance using SQL Server Activity Monitor without affecting overall system performance, the server should be polled 1 time/10 seconds.

1/1

Question 9

Where are server-scoped dynamic management views stored?

  • Selected: master system databaseThis answer is correct.
  • resource system database
  • model system database
  • msdb system database

Correct!
Server-scoped dynamic management views are stored in a master system database.

1/1

Question 10

For which type of event are notifications and alerts generally NOT sent?

  • SQL Server Agent job failure
  • deadlocks
  • high CPU or memory utilization
  • Selected: SQL Server errors with severity 1–10This answer is correct.

Correct! In general, for a production system, SQL Server Agent should be used to send notifications and alerts for the following events:
• SQL Server Agent job failure notifications
• SQL Server errors with a severity level of 17 and above
• Deadlocks
• Resource alerts that include high CPU or memory utilization and disk space shortage

Bottom of Form

Back to assessments

Question 1

To get an accurate view of SQL Server performance using SQL Server Activity Monitor without affecting overall system performance, how often should the server be polled?

  • 10 times/second
  • Selected: 1 time/10 secondsThis answer is correct.
  • 1 time/second
  • 10 times/minute

Correct! To get an accurate view of SQL Server performance using SQL Server Activity Monitor without affecting overall system performance, the server should be polled 1 time/10 seconds.

1/1

Question 2

Which of the following is true about the Management Data Warehouse?

  • There are no predefined data collection sets.
  • Management reports must be custom made.
  • It is installed automatically with SQL Server.
  • Selected: Data may be queried using SQL.This answer is correct.

Correct! The data that is stored in the Management Data Warehouse may be queried using SQL, and it can also be viewed using one of several predefined management reports.

1/1

Question 3

Which tool do you use to record a SQL Server trace?

  • Task Manager
  • Selected: SQL Server ProfilerThis answer is correct.
  • Performance Monitor
  • Management Data Warehouse

Correct! You can capture SQL Server performance data by using the SQL Server Profiler to record a trace, or by capturing performance counter data using the performance monitor utility.

1/1

Question 4

Ideally, database administrators should monitor SQL Server reactively and solve problems as they occur.

  • True
  • Selected:FalseThis answer is correct.

Correct! It is necessary for a database administrator to proactively monitor each SQL Server instance.

1/1

Question 5

Where are server-scoped dynamic management views stored?

  • Selected: master system databaseThis answer is correct.
  • resource system database
  • model system database
  • msdb system database

Correct!
Server-scoped dynamic management views are stored in a master system database.

0/1

Question 6

What is the difference between a Dynamic Management View (DMV) and a Dynamic Management Function (DMF)?

No answer provided

  • Dynamic management views may be queried using SQL.
  • For complex queries, a set of input parameters may be supplied to a dynamic management function at the time of execution.
  • Dynamic management functions may be queried using SQL.
  • There is no difference; the term function and view are used interchangeably.

Actually, look again. For complex queries, a set of input parameters may be supplied to a dynamic management function at the time of execution. Review Module 8 again, and specifically, page 382 in Chapter 10 of the textbook.

1/1

Question 7

Which of the following is NOT a standard collector type installed on SQL Server?

  • Transact-SQL Query
  • SQL Trace
  • Selected: SQL AlertThis answer is correct.
  • Query Activity

Correct! Four standard collector types are installed with SQL Server: Transact-SQL Query, SQL Trace, Performance Counters, and Query Activity.

1/1

Question 8

How must the recipient of a notification be configured in SQL Server Agent?

  • as a mail recipient
  • Selected: as an operatorThis answer is correct.
  • as an alert administrator
  • as an Agent Manager

Correct! The recipient of a notification should be configured in SQL Server Agent as an operator.

1/1

Question 9

Why is establishing a baseline important for monitoring performance?

  • Baselines are not important because there is no substitute for experience.
  • Selected: A baseline can help a database administrator understand how the system is expected to behave under normal operating conditions.This answer is correct.
  • Baselines are not relevant to performance monitoring.
  • both options a and b

Correct! A baseline can help a database administrator understand how the system is expected to behave under normal operating conditions.

1/1

Question 10

What permission must a user have to be able to view data in Activity Monitor?

  • VIEW ANY DATABASE
  • ALTER SETTINGS
  • Selected: VIEW SERVER STATEThis answer is correct.
  • CONTROL SERVER

Correct!  A user must have the VIEW SERVER STATE permission in order to view data in Activity Monitor.

Top of Form

Question 1

Where are server-scoped dynamic management views stored?

  • Selected: master system databaseThis answer is correct.
  • resource system database
  • model system database
  • msdb system database

Correct!
Server-scoped dynamic management views are stored in a master system database.

1/1

Question 2

For which type of event are notifications and alerts generally NOT sent?

  • SQL Server Agent job failure
  • deadlocks
  • high CPU or memory utilization
  • Selected: SQL Server errors with severity 1–10This answer is correct.

Correct! In general, for a production system, SQL Server Agent should be used to send notifications and alerts for the following events:
• SQL Server Agent job failure notifications
• SQL Server errors with a severity level of 17 and above
• Deadlocks
• Resource alerts that include high CPU or memory utilization and disk space shortage

1/1

Question 3

How must the recipient of a notification be configured in SQL Server Agent?

  • as a mail recipient
  • Selected: as an operatorThis answer is correct.
  • as an alert administrator
  • as an Agent Manager

Correct! The recipient of a notification should be configured in SQL Server Agent as an operator.

1/1

Question 4

Which type of message is generated automatically when a performance condition is met?

  • Selected: alertThis answer is correct.
  • counter
  • WMI event
  • performance alarm

Correct! Alerts are generated automatically when a performance condition is met.

1/1

Question 5

What permission must a user have to be able to view data in Activity Monitor?

  • VIEW ANY DATABASE
  • ALTER SETTINGS
  • Selected: VIEW SERVER STATEThis answer is correct.
  • CONTROL SERVER

Correct!  A user must have the VIEW SERVER STATE permission in order to view data in Activity Monitor.

1/1

Question 6

What are the advantages of using the Management Data Warehouse and data collector versus other tools for collecting statistics?

  • The Management Data Warehouse provides a standardized repository for storing and managing the data.
  • The data collector can capture different types of data; the SQL Profiler and Windows
    Performance Monitor can capture only a single type of data.
  • The data collector does not require further configuration after installation of a SQL Server instance.
  • Selected: both options a and bThis answer is correct.

Correct! Each data collector type stores the results of the data collection in the Management Data Warehouse (MDW), which is a centralized data repository installed on each SQL Server instance. The data that is stored in the Management Data Warehouse may be queried using SQL, and it can also be viewed using one of several predefined management reports.

1/1

Question 7

Which of the following is true about the Management Data Warehouse?

  • There are no predefined data collection sets.
  • Management reports must be custom made.
  • It is installed automatically with SQL Server.
  • Selected: Data may be queried using SQL.This answer is correct.

Correct! The data that is stored in the Management Data Warehouse may be queried using SQL, and it can also be viewed using one of several predefined management reports.

1/1

Question 8

Which of the following is NOT true with respect to dynamic management views and functions?

  • They belong to the sys schema.
  • They are given the prefix dm_ in their name.
  • Server-scoped dynamic management views and functions are stored in the master system database.
  • Selected: Database-scoped dynamic management views and functions are stored in the msdb system database.This answer is correct.

Correct! Database-scoped dynamic management views and functions are stored in the msdb system database.

1/1

Question 9

What is the first priority in checking the status of a SQL Server instance?

  • check database status using Object Explorer
  • Selected: ensure that all services are running normallyThis answer is correct.
  • verify remote client/server connectivity
  • check for recent errors in scheduled jobs

Correct! The first priority for a database administrator is to ensure that all services are running normally.

0/1

Question 10

Which range of SQL Server error message severity levels are user-generated messages that must be fixed by the user?

  • 1–10
  • Selected: 17–10This answer is incorrect.
  • 11–16
  • 20–25

Actually, look again. The severity range of 11–16 includes user-generated error messages that must be fixed by the user; for example, a syntax error in a SQL query would result in a user error being generated. Review Module 8 again, and specifically, page 369 in Chapter 10 of the textbook.

Bottom of Form

Back to assessments

Top of Form

Question 1

Which range of SQL Server error message severity levels are user-generated messages that must be fixed by the user?

  • 1–10
  • 17–10
  • Selected: 11–16This answer is correct.
  • 20–25

Correct! The severity range of 11–16 includes user-generated error messages that must be fixed by the user; for example, a syntax error in a SQL query would result in a user error being generated.

1/1

Question 2

Which of the following is true about the Management Data Warehouse?

  • There are no predefined data collection sets.
  • Management reports must be custom made.
  • It is installed automatically with SQL Server.
  • Selected: Data may be queried using SQL.This answer is correct.

Correct! The data that is stored in the Management Data Warehouse may be queried using SQL, and it can also be viewed using one of several predefined management reports.

1/1

Question 3

What is the first priority in checking the status of a SQL Server instance?

  • check database status using Object Explorer
  • Selected: ensure that all services are running normallyThis answer is correct.
  • verify remote client/server connectivity
  • check for recent errors in scheduled jobs

Correct! The first priority for a database administrator is to ensure that all services are running normally.

1/1

Question 4

Ideally, database administrators should monitor SQL Server reactively and solve problems as they occur.

  • True
  • Selected:FalseThis answer is correct.

Correct! It is necessary for a database administrator to proactively monitor each SQL Server instance.

1/1

Question 5

For which type of event are notifications and alerts generally NOT sent?

  • SQL Server Agent job failure
  • deadlocks
  • high CPU or memory utilization
  • Selected: SQL Server errors with severity 1–10This answer is correct.

Correct! In general, for a production system, SQL Server Agent should be used to send notifications and alerts for the following events:
• SQL Server Agent job failure notifications
• SQL Server errors with a severity level of 17 and above
• Deadlocks
• Resource alerts that include high CPU or memory utilization and disk space shortage

1/1

Question 6

What permission must a user have to be able to view data in Activity Monitor?

  • VIEW ANY DATABASE
  • ALTER SETTINGS
  • Selected: VIEW SERVER STATEThis answer is correct.
  • CONTROL SERVER

Correct!  A user must have the VIEW SERVER STATE permission in order to view data in Activity Monitor.

1/1

Question 7

Which of the following is NOT true with respect to dynamic management views and functions?

  • They belong to the sys schema.
  • They are given the prefix dm_ in their name.
  • Server-scoped dynamic management views and functions are stored in the master system database.
  • Selected: Database-scoped dynamic management views and functions are stored in the msdb system database.This answer is correct.

Correct! Database-scoped dynamic management views and functions are stored in the msdb system database.

1/1

Question 8

What is the difference between a Dynamic Management View (DMV) and a Dynamic Management Function (DMF)?

  • Dynamic management views may be queried using SQL.
  • Selected: For complex queries, a set of input parameters may be supplied to a dynamic management function at the time of execution.This answer is correct.
  • Dynamic management functions may be queried using SQL.
  • There is no difference; the term function and view are used interchangeably.

Correct! For complex queries, a set of input parameters may be supplied to a dynamic management function at the time of execution.

1/1

Question 9

Why is establishing a baseline important for monitoring performance?

  • Baselines are not important because there is no substitute for experience.
  • Selected: A baseline can help a database administrator understand how the system is expected to behave under normal operating conditions.This answer is correct.
  • Baselines are not relevant to performance monitoring.
  • both options a and b

Correct! A baseline can help a database administrator understand how the system is expected to behave under normal operating conditions.

1/1

Question 10

Which section of Activity Monitor should you inspect if you want to see the average speeds for read and write transfers of data to the physical disk?

  • overview
  • resource waits
  • processes
  • Selected: data file I/OThis answer is correct.

Correct! You should inspect Activity Monitor data file I/O if you want to see the average speeds for read and write transfers of data to the physical disk.

Bottom of Form

Back to assessments

 

1/1

Question 1

What is the first priority in checking the status of a SQL Server instance?

  • check database status using Object Explorer
  • Selected: ensure that all services are running normallyThis answer is correct.
  • verify remote client/server connectivity
  • check for recent errors in scheduled jobs

Correct! The first priority for a database administrator is to ensure that all services are running normally.

1/1

Question 2

Which of the following is true about the Management Data Warehouse?

  • There are no predefined data collection sets.
  • Management reports must be custom made.
  • It is installed automatically with SQL Server.
  • Selected: Data may be queried using SQL.This answer is correct.

Correct! The data that is stored in the Management Data Warehouse may be queried using SQL, and it can also be viewed using one of several predefined management reports.

1/1

Question 3

Why is establishing a baseline important for monitoring performance?

  • Baselines are not important because there is no substitute for experience.
  • Selected: A baseline can help a database administrator understand how the system is expected to behave under normal operating conditions.This answer is correct.
  • Baselines are not relevant to performance monitoring.
  • both options a and b

Correct! A baseline can help a database administrator understand how the system is expected to behave under normal operating conditions.

1/1

Question 4

Which of the following is NOT true with respect to dynamic management views and functions?

  • They belong to the sys schema.
  • They are given the prefix dm_ in their name.
  • Server-scoped dynamic management views and functions are stored in the master system database.
  • Selected: Database-scoped dynamic management views and functions are stored in the msdb system database.This answer is correct.

Correct! Database-scoped dynamic management views and functions are stored in the msdb system database.

1/1

Question 5

Ideally, database administrators should monitor SQL Server reactively and solve problems as they occur.

  • True
  • Selected:FalseThis answer is correct.

Correct! It is necessary for a database administrator to proactively monitor each SQL Server instance.

1/1

Question 6

What is the difference between a Dynamic Management View (DMV) and a Dynamic Management Function (DMF)?

  • Dynamic management views may be queried using SQL.
  • Selected: For complex queries, a set of input parameters may be supplied to a dynamic management function at the time of execution.This answer is correct.
  • Dynamic management functions may be queried using SQL.
  • There is no difference; the term function and view are used interchangeably.

Correct! For complex queries, a set of input parameters may be supplied to a dynamic management function at the time of execution.

1/1

Question 7

To get an accurate view of SQL Server performance using SQL Server Activity Monitor without affecting overall system performance, how often should the server be polled?

  • 10 times/second
  • Selected: 1 time/10 secondsThis answer is correct.
  • 1 time/second
  • 10 times/minute

Correct! To get an accurate view of SQL Server performance using SQL Server Activity Monitor without affecting overall system performance, the server should be polled 1 time/10 seconds.

1/1

Question 8

Which type of message is generated automatically when a performance condition is met?

  • Selected: alertThis answer is correct.
  • counter
  • WMI event
  • performance alarm

Correct! Alerts are generated automatically when a performance condition is met.

1/1

Question 9

What are the advantages of using the Management Data Warehouse and data collector versus other tools for collecting statistics?

  • The Management Data Warehouse provides a standardized repository for storing and managing the data.
  • The data collector can capture different types of data; the SQL Profiler and Windows
    Performance Monitor can capture only a single type of data.
  • The data collector does not require further configuration after installation of a SQL Server instance.
  • Selected: both options a and bThis answer is correct.

Correct! Each data collector type stores the results of the data collection in the Management Data Warehouse (MDW), which is a centralized data repository installed on each SQL Server instance. The data that is stored in the Management Data Warehouse may be queried using SQL, and it can also be viewed using one of several predefined management reports.

1/1

Question 10

When is the ideal time to measure system performance to create a baseline?

  • before the system is put into production
  • on weekends, when there is little use
  • Selected: under normal operating loadsThis answer is correct.
  • after a series of complaints that the system is performing poorly

Correct! The ideal time to measure system performance to create a baseline is under normal operating loads.

Leave a Reply

Your email address will not be published. Required fields are marked *