Management Data Warehouse - SQL Server Questions and Answers

This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Management Data Warehouse”.

1. _________ introduces the Management Data Warehouse (MDW) to SQL Server Management Studio for streamlined performance troubleshooting.
a) SQL Server 2005
b) SQL Server 2008
c) SQL Server 2012
d) SQL Server 2014
View Answer

Answer: b
Explanation: MDW is a set of components that enable a database developer or administrator to quickly track down problems that could be causing performance degradation.

2. Point out the correct statement.
a) MDW consist of three components
b) SQL Server Express instances can be targets
c) Setting up the MDW is a one-step process
d) All of the mentioned
View Answer

Answer: a
Explanation: MDW consists of three components: Data Collector, MDW database and MDW reports.

3. Which of the following mode allows for the collection and uploading of data to occur on demand?
a) Non-cached mode
b) Cached mode
c) Mixed mode
d) All of the mentioned
View Answer

Answer: a
Explanation: In non-cached mode, collection and upload are on the same schedule.

4. Which of the following scenario favours cached mode?
a) Continuous collection of data
b) Less frequent uploads
c) Data collection and uploading of jobs on different schedules
d) All of the mentioned
View Answer

Answer: d
Explanation: Cached mode uses separate schedules for collection and upload.

5. Point out the wrong statement.
a) The Data Collection is performed primarily through SSIS packages that control the collection frequency on the target
b) You should change the database name after creation
c) Do not change any of the job specifications for the data collection and upload jobs
d) None of the mentioned
View Answer

Answer: b
Explanation: You should not change the database name after creation, because all of the jobs created to manage the database collection refer to the database by the original name and will generate errors if the name is changed.

6. Which of the following is the best Practice and Caveat for Management Data Warehouse?
a) Use a centralized server for the MDW database
b) The XML parameters for a single T-SQL collection item can have multiple <Query> elements
c) Use a distributed server for the MDW database
d) All of the mentioned
View Answer

Answer: a
Explanation: Centralized server allows you to use a single point for viewing reports for multiple instances.

Note: Join free Sanfoundry classes at Telegram or Youtube

7. ____________ stores information about how the management data warehouse reports should group and aggregate performance counters.
a) core.snapshots_internal
b) core.supported_collector_types_internal
c) core.wait_categories
d) core.performance_counter_report_group_items
View Answer

Answer: d
Explanation: core.wait_categories contains the categories used to group wait types according to wait_type characteristic.

8. Which of the following table is used in the management data warehouse schema that is required for the Server Activity?
a) snapshots.query_stat
b) snapshots.os_latch_stats
c) snapshots.active_sessions
d) all of the mentioned
View Answer

Answer: b
Explanation: snapshots.os_latch_stats is a System level resource table.

9. Which of the following is syntax for sp_add_collector_type procedure?
a) core.sp_add_collector [ @collector_type_uid = ] ‘collector_type_uid’
b) core.sp_add_collector_type [ @collector_type_uid = ].
c) core.sp_add_collector_type [ @collector_type_uid = ] ‘collector_type_uid’
d) none of the mentioned
View Answer

Answer: c
Explanation: core.sp_add_collector_type adds a new entry to the core.supported_collector_types view in the management data warehouse database.

10. What does collector_type_id stands for in the following code snippet?
core.sp_remove_collector_type [ @collector_type_uid = ] ‘collector_type_uid’
a) uniqueidentifier
b) membership role
c) directory
d) none of the mentioned
View Answer

Answer: a
Explanation: collector_type_uid is the GUID for the collector type.

Sanfoundry Global Education & Learning Series – SQL Server.

To practice all areas of SQL Server, here is complete set of 1000+ Multiple Choice Questions and Answers.

If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]

« Prev - SQL Server Questions and Answers – SQL Audit

» Next - SQL Server Questions and Answers – Query Execution Plan

Related Posts:

Recommended Articles: