Deck 10: Monitoring SQL Server 2012

Question 1

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

A)check database status using Object Explorer
B)verify remote client/server connectivity
C)ensure that all services are running normally
D)check for recent errors of scheduled jobs

Answer 1

C

Question 2

A data collector set is a collection of statistics captured and viewed in real time.

Answer 2

False

Question 3

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

A)there are no predefined data collection sets
B)it is installed automatically with SQL Server
C)management reports must be custom made
D)data may be queried using SQL

Answer 3

D

Question 4

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

A)Alerts
B)WMI events
C)Counters
D)Performance alarm

Question 5

Where are server-scoped dynamic management views stored?

A)master system database
B)model system database
C)resource system database
D)msdb system database

Question 6

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

A)VIEW ANY DATABASE
B)VIEW SERVER STATE
C)ALTER SETTINGS
D)CONTROL SERVER

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?

A)10 times/second
B)1 time/second
C)1 time/10 seconds
D)10 times/minute

Question 8

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

A)before the system is put into production
B)under normal operating loads
C)on weekends, when there is little use
D)after a series of complaints that the system is performing poorly

Question 9

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

A)SQL Server Agent job failure
B)High CPU or memory utilization
C)Deadlocks
D)SQL Server errors with severity 1-10

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?

A)Overview
B)Processes
C)Resource Waits
D)Data File I/O

Question 11

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

A)as a mail recipient
B)as an alert administrator
C)as an operator
D)as an Agent manager

Question 12

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

Question 13

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

A)1-10
B)11-16
C)17-10
D)20-25

Question 14

It is always preferable to quantitatively measure performance rather than relying on the experience of an individual database administrator.

Question 15

Which of the following is NOT part of the process of creating a database audit?

A)create an audit user and assign the AUDIT DB permission
B)define an audit object
C)create the server audit specification
D)enable the audit to begin capturing events

Question 16

To check whether system or resource errors have occurred, you should check the events in the Windows application log and SQL Server log.

Question 17

Activity Monitor can help you assess CPU and memory utilization.

Question 18

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

A)Transact-SQL Query
B)SQL Alert
C)SQL Trace
D)Query Activity

Question 19

Where should you look for events related to a maintenance and backup plan scheduled using SQL Server Agent?

A)Windows application log
B)SQL Server Agent error log
C)SQL Server log
D)Windows system and security logs

Question 20

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

A)Task Manager
B)Performance Monitor
C)SQL Server Profiler
D)Management Data Warehouse

Question 21

A data collector set is a collection of ____________ captured at predetermined time intervals.

Question 22

Each SQL Server error message is assigned a numerical _________ level that describes the type of error.

Question 23

Describe how a database administrator can distinguish between normal and abnormal SQL Server activity.

Question 24

What permissions must a user have in order to view data in Activity Monitor? What happens if the user runs the tool but lacks sufficient permissions?

Question 25

What are performance conditions and how do they work? Provide an example.

Question 26

What is the first priority of a database administrator in determining the proper functioning of a SQL Server instance?

Question 28

Performance ___________ are rules defined by placing a threshold on the value of a specific performance counter monitored by SQL Server Agent.

Question 29

Describe the steps involved in capturing a set of baseline values.

Question 30

What are data collectors and data collections?

Question 31

After establishing the current state of a SQL Server instance, databases, and network connections, what should a database administrator check next?

Question 32

Each data collector type stores the results of the data collection in the Management Data ___________.

Question 33

Activity Monitor uses _________ management views to retrieve several of its result sets.

Question 34

SQL Server ____________ Monitor is a dashboard that allows you to monitor system behavior through a simple graphical user interface.

Question 35

List two events in which the SQL Server Agent should be used to send notifications and alerts?

Question 36

A database administrator must ____________ monitor each SQL Server instance to minimize the risk of service disruption.

Question 37

A ______________ is a set of recorded performance results that can be used as future comparison points.

Question 38

What are dynamic management views and dynamic management functions?

Question 39

List the main ranges of severity levels and their function.